creating a variable based on the values of two another variables

[Guest]

Dear Stata users,

I have data from IMF on exchange rate (local currency in terms of US dollar). Here is the sample of my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 reporter int year double EXCH
"AUS" 1990 1.28105666666667
"AUS" 1991 1.28375583333333
"AUS" 1992 1.36164833333333
"AUS" 1993          1.47056
"AUS" 1994 1.36775083333333
"AUS" 1995        1.3490325
"AUS" 1996 1.27786333333333
"AUS" 1997          1.34738
"AUS" 1998 1.59182833333333
"AUS" 1999          1.54995
"AUS" 2000 1.72482666666667
"AUS" 2001        1.9334425
"AUS" 2002        1.8405625
"AUS" 2003 1.54191416666667
"AUS" 2004        1.3597525
"AUS" 2005 1.30947333333333
"AUS" 2006     1.3279734405
"AUS" 2007        1.1950725
"AUS" 2008 1.19217833333333
"AUS" 2009 1.28218881008452
"AUS" 2010 1.09015948638677
"AUS" 2011 .969463201496735
"AUS" 2012 .965801030658708
"AUS" 2013 1.03584309652054
"AUS" 2014 1.10936329281692
"AUS" 2015 1.33109026245502
"AUS" 2016 1.34521397601947
"AUS" 2017 1.30475807671592
"AUS" 2018 1.33841214646451
"AUT" 1990 11.3698333333333
"AUT" 1991 11.6759166666667
"AUT" 1992 10.9893333333333
"AUT" 1993       11.6321825
"AUT" 1994 11.4218249166667
"AUT" 1995 10.0814958333333
"AUT" 1996       10.5865575
"AUT" 1997 12.2042441666667
"AUT" 1998        12.379065
"AUT" 1999                .
"AUT" 2000                .
"AUT" 2001                .
"AUT" 2002                .
"AUT" 2003                .
"AUT" 2004                .
"AUT" 2005                .
"AUT" 2006                .
"AUT" 2007                .
"AUT" 2008                .
"AUT" 2009                .
"AUT" 2010                .
"AUT" 2011                .
"AUT" 2012                .
"AUT" 2013                .
"AUT" 2014                .
"AUT" 2015                .
"AUT" 2016                .
"AUT" 2017                .
"AUT" 2018                .
"BEL" 1990 33.4179166666667
"BEL" 1991         34.14825
"BEL" 1992          32.1495
"BEL" 1993 34.5965208333333
"BEL" 1994       33.4564975
"BEL" 1995 29.4800166666667
"BEL" 1996 30.9615133333333
"BEL" 1997 35.7738908333333
"BEL" 1998 36.2986408333333
"BEL" 1999                .
"BEL" 2000                .
"BEL" 2001                .
"BEL" 2002                .
"BEL" 2003                .
"BEL" 2004                .
"BEL" 2005                .
"BEL" 2006                .
"BEL" 2007                .
"BEL" 2008                .
"BEL" 2009                .
"BEL" 2010                .
"BEL" 2011                .
"BEL" 2012                .
"BEL" 2013                .
"BEL" 2014                .
"BEL" 2015                .
"BEL" 2016                .
"BEL" 2017                .
"BEL" 2018                .
"CAN" 1990 1.16677362666667
"CAN" 1991 1.14572584416667
"CAN" 1992       1.20872292
"CAN" 1993 1.29008788166667
"CAN" 1994     1.3656734475
"CAN" 1995 1.37244541833333
"CAN" 1996 1.36352163583333
"CAN" 1997 1.38459802833333
"CAN" 1998 1.48350530166667
"CAN" 1999 1.48570481916667
"CAN" 2000      1.485394095
"CAN" 2001      1.548839955
"CAN" 2002 1.57034283416667
end

For the Eurozone countries such as Austria, Belgium etc. there are empty spaces beginning from the year a particular country joined Eurozone. I wanted to tell stata to take for Eurozone countries exchange rate from Euro_Area. I generated dummy for eurozone:

gen eurozone=0
replace eurozone=1 if reporter=="AUT" & year>=1999
replace eurozone=1 if reporter=="BEL" & year>=1999
replace eurozone=1 if reporter=="DEU" & year>=1999
replace eurozone=1 if reporter=="ESP" & year>=1999
replace eurozone=1 if reporter=="FIN" & year>=1999
replace eurozone=1 if reporter=="FRA" & year>=1999
replace eurozone=1 if reporter=="GRC" & year>=2001
replace eurozone=1 if reporter=="ITA" & year>=1999
replace eurozone=1 if reporter=="NLD" & year>=1999
replace eurozone=1 if reporter=="PRT" & year>=1999

I come up with an idea to generate a variable EuroArea and take exchange rate values when partner=="Euro_Area" and then replace EXCH with EuroArea values when EXCH==. for eurozone countries. I tried a code:

gen EuroArea=.
 replace EuroArea=cond(reporter=="Euro_Area",EXCH, .) 
 bysort year (EuroArea): replace EuroArea=EuroArea[_N]

I wasn't successful though. Stata generated missing values.


The aim of my study is to investigate the influence of exchange rate on export. Previously I prepared the exchange rates in Excel. However, the magnitude of the coefficient on exchange rate was very small. I decided to cross-check it and do everything in Stata from scratch. However, I got stuck with it. Please help me

[William Lisowski]

bysort year (EuroArea): replace EuroArea=EuroArea[_N]

All the missing values of EuroArea will sort to the end within each year - the non-missing values will be the first observations within each year - so perhaps what you want is

bysort year (EuroArea): replace EuroArea=EuroArea[1]

[Guest]

Thank you William for your kind help! I got my results