generating a value based on another value from a given point in time

[Guest]

Hi all,

I have data on 26 countries (reporter) and their exchange rates EXCH_USD_rep (local currency per US dollar). For every country I have another country-partner. I want to calculate exchange rate for each country-pair. I wanted to generate a variable EXCH_USD_par. E.g. I have year 1990. Reporter country is Australia. It's EXCH_USD_rep is 1.281056666667. I have partner country Austria. I want to create EXCH_USD_par for it. I know that in 1990 EXCH_USD_rep for Austria was .8262755899217. I don't know how to tell Stata to take EXCH_USD_rep for Austria in 1990 as EXCH_USD_par. I tried:

gen EXCH_USD_par=cond(reporter==partner, EXCH_USD_rep,.)

However, I wasn't successful. Stata generated missing values

The solution which comes to my mind is to generate additional dta file and rename reporter partner and merge it. I would prefer to find more elegant way though

[Clyde Schechter]

Your post has gone unanswered for nearly 6 hours. I suspect people are reluctant to try to help without having example data to work with: this kind of problem is difficult to do in one's head with imaginary data. Please post back showing example data, and use the -dataex- command to do that. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

More generally, always show example data when asking for help with code. It's usually necessary, and it doesn't hurt when it isn't.

[George Ford]

I'm sure there are better ways to do it and hope someone posts one. What I've been doing lately is to create a new variable with missing values for all countries I don't want, then use egen.

g dropit = EXCH_USA_rep if country=="USA" //gives missing values for other countries
bys year: egen baseEXCH = mean(dropit) //could use max/min or such since you are only getting one value by year
drop dropit
correl EXCH_USA_rep baseEXCH if country=="Australia"

note that the baseEXCH is there for all countries, so can run through them all for the USA if that's the goal.

[George Ford]

Here's a code that lets you do it on the fly.
Makes sure data is xtset.
idnum equals the id of the country you want as the base.

capture program drop makebase
program makebase
    syntax varlist(min=1 max=1 numeric) [, GENerate(string) idnum(real 0) ]
    capture drop `generate'    
    local identif = r(panelvar)
    local timevar = r(timevar)
    di "`identif'"
    di "`timevar'"
    di `idnum'
    tokenize `varlist'
    tempvar xxxxx
    g `xxxxx' = `1' if `identif' == `idnum'
    bys `timevar': egen `generate' = max(`xxxxx')
end
xtset id t
makebase variable_name , generate(base) idnum(1)

[George Ford]

Here's a code that lets you do it on the fly.
Makes sure data is xtset.
idnum equals the id of the country you want as the base.

capture program drop makebase
program makebase
    syntax varlist(min=1 max=1 numeric) [, GENerate(string) idnum(real 0) ]
    capture drop `generate'    
    local identif = r(panelvar)
    local timevar = r(timevar)
    di "`identif'"
    di "`timevar'"
    di `idnum'
    tokenize `varlist'
    tempvar xxxxx
    g `xxxxx' = `1' if `identif' == `idnum'
    bys `timevar': egen `generate' = max(`xxxxx')
end
xtset id t
makebase variable_name , generate(base) idnum(1)

[Guest]

Thank you George and Clyde for your answers. Here is my data example:

* Example generated by -dataex-. For more info, type help dataex
clear
input int year str17 reporter str3 partner double EXCH_USD_rep
1990 "AUS" "CAN" 1.28105666666667
1990 "AUS" "ISR" 1.28105666666667
1990 "AUS" "TUR" 1.28105666666667
1990 "AUS" "FRA" 1.28105666666667
1990 "AUS" "NOR" 1.28105666666667
1990 "AUS" "DEU" 1.28105666666667
1990 "AUS" "JPN" 1.28105666666667
1990 "AUS" "SWE" 1.28105666666667
1990 "AUS" "PRT" 1.28105666666667
1990 "AUS" "ITA" 1.28105666666667
1990 "AUS" "GRC" 1.28105666666667
1990 "AUS" "FIN" 1.28105666666667
1990 "AUS" "CZE" 1.28105666666667
1990 "AUS" "CHE" 1.28105666666667
1990 "AUS" "DNK" 1.28105666666667
1990 "AUS" "BEL" 1.28105666666667
1990 "AUS" "NLD" 1.28105666666667
1990 "AUS" "GBR" 1.28105666666667
1990 "AUS" "NZL" 1.28105666666667
1990 "AUS" "CHN" 1.28105666666667
1990 "AUS" "USA" 1.28105666666667
1990 "AUS" "RUS" 1.28105666666667
1990 "AUS" "AUT" 1.28105666666667
1990 "AUS" "ESP" 1.28105666666667
1990 "AUS" "KOR" 1.28105666666667
1990 "AUT" "RUS" .826275589921731
1990 "AUT" "DNK" .826275589921731
1990 "AUT" "GRC" .826275589921731
1990 "AUT" "USA" .826275589921731
1990 "AUT" "CAN" .826275589921731
1990 "AUT" "KOR" .826275589921731
1990 "AUT" "ITA" .826275589921731
1990 "AUT" "SWE" .826275589921731
1990 "AUT" "NLD" .826275589921731
1990 "AUT" "FRA" .826275589921731
1990 "AUT" "ISR" .826275589921731
1990 "AUT" "TUR" .826275589921731
1990 "AUT" "CZE" .826275589921731
1990 "AUT" "DEU" .826275589921731
1990 "AUT" "GBR" .826275589921731
1990 "AUT" "CHN" .826275589921731
1990 "AUT" "BEL" .826275589921731
1990 "AUT" "JPN" .826275589921731
1990 "AUT" "ESP" .826275589921731
1990 "AUT" "FIN" .826275589921731
1990 "AUT" "CHE" .826275589921731
1990 "AUT" "PRT" .826275589921731
1990 "AUT" "AUS" .826275589921731
1990 "AUT" "NZL" .826275589921731
1990 "AUT" "NOR" .826275589921731
1990 "BEL" "ISR" .828408102151964
1990 "BEL" "AUS" .828408102151964
1990 "BEL" "TUR" .828408102151964
1990 "BEL" "CHN" .828408102151964
1990 "BEL" "DEU" .828408102151964
1990 "BEL" "GBR" .828408102151964
1990 "BEL" "SWE" .828408102151964
1990 "BEL" "ESP" .828408102151964
1990 "BEL" "DNK" .828408102151964
1990 "BEL" "AUT" .828408102151964
1990 "BEL" "NZL" .828408102151964
1990 "BEL" "CAN" .828408102151964
1990 "BEL" "ITA" .828408102151964
1990 "BEL" "CHE" .828408102151964
1990 "BEL" "JPN" .828408102151964
1990 "BEL" "KOR" .828408102151964
1990 "BEL" "NLD" .828408102151964
1990 "BEL" "NOR" .828408102151964
1990 "BEL" "GRC" .828408102151964
1990 "BEL" "USA" .828408102151964
1990 "BEL" "CZE" .828408102151964
1990 "BEL" "PRT" .828408102151964
1990 "BEL" "FIN" .828408102151964
1990 "BEL" "RUS" .828408102151964
1990 "BEL" "FRA" .828408102151964
1990 "CAN" "DNK" 1.16677362666667
1990 "CAN" "FIN" 1.16677362666667
1990 "CAN" "AUT" 1.16677362666667
1990 "CAN" "NLD" 1.16677362666667
1990 "CAN" "DEU" 1.16677362666667
1990 "CAN" "ITA" 1.16677362666667
1990 "CAN" "SWE" 1.16677362666667
1990 "CAN" "KOR" 1.16677362666667
1990 "CAN" "CHE" 1.16677362666667
1990 "CAN" "ISR" 1.16677362666667
1990 "CAN" "ESP" 1.16677362666667
1990 "CAN" "RUS" 1.16677362666667
1990 "CAN" "NOR" 1.16677362666667
1990 "CAN" "CZE" 1.16677362666667
1990 "CAN" "PRT" 1.16677362666667
1990 "CAN" "TUR" 1.16677362666667
1990 "CAN" "NZL" 1.16677362666667
1990 "CAN" "GBR" 1.16677362666667
1990 "CAN" "AUS" 1.16677362666667
1990 "CAN" "FRA" 1.16677362666667
1990 "CAN" "USA" 1.16677362666667
1990 "CAN" "BEL" 1.16677362666667
1990 "CAN" "JPN" 1.16677362666667
1990 "CAN" "CHN" 1.16677362666667
1990 "CAN" "GRC" 1.16677362666667
end

I wanted to generate EXCH_USD_par so that I could calculate ratio between EXCH_USD_par and EXCH_USD_rep. I did it by code:

preserve
drop partner
duplicates drop
rename reporter partner
rename EXCH_USD_rep EXCH_USD_par 
sort partner year
save EXCH_USD_par.dta
restore

merge partner year using EXCH_USD_par.dta
drop _merge

However, I was wondering if there is a better way to get it without saving additional file.

[Hemanshu Kumar]

Guest,

your code in #6 already does a great job. If you have objections to saving a file, you could use a tempfile instead, which is a temporary file that will automatically be erased when your code ends. Also, you are using the syntax for the older version of merge. I incorporate these minor changes below:

preserve
    drop partner
    duplicates drop
    rename reporter partner
    rename EXCH_USD_rep EXCH_USD_par
    sort partner year
    tempfile rates
    save `rates'
restore

merge m:1 partner year using `rates', nogen

[George Ford]

g EXCH_USD_par = .
levelsof reporter, local(levels)
  foreach r of local levels {
    summ EXCH_USD_rep if reporter=="`r'"
    replace EXCH_USD_par = r(mean) if partner=="`r'"
}