Generate a new spread variable from existing data

[Bruce Peer]

Need some help please.

I want to create a new variable called spread10 = yield10( for each country) - yield10 for USA.

The outcome I am looking for is a spread value relative to the USA for each country for each year.
So the yield data for USA data must repeatedly populate the rows of the new variable for each country for each year. Thank you.

* Example generated by -dataex-. For more info, type help dataex
clear
input str29 country float year double(cab forex yield10)
"USA"                  1995              -1.487        49095.7                  .
"USA"                  1996              -1.546        38293.8                  .
"USA"                  1997              -1.641          30809                  .
"USA"                  1998              -2.373        36000.9                  .
"USA"                  1999              -3.068        32182.1                  .
"USA"                  2000              -3.921        31238.3              6.029
"USA"                  2001              -3.724          28981              5.018
"USA"                  2002              -4.173          33818              4.611
"USA"                  2003              -4.559        39721.8              4.015
"USA"                  2004              -5.205        42718.3              4.274
"USA"                  2005              -5.746        37838.1               4.29
"USA"                  2006              -5.911        40943.5              4.792
"USA"                  2007              -5.089        45803.8              4.629
"USA"                  2008              -4.716        49583.6              3.667
"USA"                  2009              -2.623          50520              3.257
"USA"                  2010              -2.871          52075              3.214
"USA"                  2011              -2.919          51878              2.786
"USA"                  2012              -2.573          49922              1.803
"USA"                  2013              -2.011          47599              2.351
"USA"                  2014              -2.102          41944              2.541
"USA"                  2015              -2.233          39242              2.136
"USA"                  2016              -2.107          39023              1.842
"USA"                  2017              -1.874          42775               2.33
"USA"                  2018              -2.129          41938               2.91
"USA"                  2019  -2.052999973297119          41536 2.1440000534057617
"USA"                  2020                -2.8          44537               .894
"USA"                  2021              -3.524          40736              1.443
"USA"                  2022              -3.774          37209              2.952
"USA"                  2023              -3.031          35924              3.956
"United Arab Emirates" 1995                .162         7111.6                  .
"United Arab Emirates" 1996               5.838         7678.8                  .
"United Arab Emirates" 1997              10.879         8027.3                  .
"United Arab Emirates" 1998               3.874         8664.4                  .
"United Arab Emirates" 1999               6.364        10377.1                  .
"United Arab Emirates" 2000              15.888        13303.9                  .
"United Arab Emirates" 2001               8.956        13918.2                  .
"United Arab Emirates" 2002               3.274        14897.2                  .
"United Arab Emirates" 2003               5.708        14731.5                  .
"United Arab Emirates" 2004               6.347          18209                  .
"United Arab Emirates" 2005              12.995        20867.7                  .
"United Arab Emirates" 2006              17.091        27511.9                  .
"United Arab Emirates" 2007               7.532        77161.9                  .
"United Arab Emirates" 2008               6.967        31556.6                  .
"United Arab Emirates" 2009               3.055        25070.4                  .
"United Arab Emirates" 2010               4.209        31750.9                  .
"United Arab Emirates" 2011               12.41        36090.5                  .
"United Arab Emirates" 2012              19.007        45826.1                  .
"United Arab Emirates" 2013              18.293        66950.9                  .
"United Arab Emirates" 2014              13.159        77242.1                  .
"United Arab Emirates" 2015                4.74        92571.8                  .
"United Arab Emirates" 2016               3.575        84383.5                  .
"United Arab Emirates" 2017               7.042        94353.6                  .
"United Arab Emirates" 2018               9.657        98411.7                  .
"United Arab Emirates" 2019   8.928999900817871 106381.3984375                  .
"United Arab Emirates" 2020               6.039         102164                  .
"United Arab Emirates" 2021              11.549       123766.5                  .
"United Arab Emirates" 2022              11.743       130110.8                  .
"United Arab Emirates" 2023               8.153         155951                  .
"United Kingdom"       1995               -.668        42857.1                  .
"United Kingdom"       1996                -.57        40608.1                  .
"United Kingdom"       1997               -.189          31589                  .
"United Kingdom"       1998               -.419        29931.8                  .
"United Kingdom"       1999              -2.361        30086.8                  .
"United Kingdom"       2000              -1.835        38025.1              5.327
"United Kingdom"       2001              -1.775          30610              5.009
"United Kingdom"       2002              -2.015        31897.4              4.914
"United Kingdom"       2003              -1.872        33170.1              4.579
"United Kingdom"       2004              -2.304        35770.7              4.927
"United Kingdom"       2005              -1.959        37348.8              4.459
"United Kingdom"       2006              -3.138        41092.5              4.374
"United Kingdom"       2007              -3.727        44315.1               5.06
"United Kingdom"       2008              -3.781        40486.7              4.502
"United Kingdom"       2009              -3.075        35618.5               3.36
"United Kingdom"       2010              -2.757        43488.8              3.365
"United Kingdom"       2011              -1.596        49691.3              2.869
"United Kingdom"       2012              -3.167        59837.2              1.744
"United Kingdom"       2013              -4.699          66187              2.026
"United Kingdom"       2014              -4.955        72092.2              2.139
"United Kingdom"       2015              -4.869        95253.2              1.794
"United Kingdom"       2016              -5.384       100643.8              1.221
"United Kingdom"       2017              -3.547       114355.4              1.181
"United Kingdom"       2018              -3.893       137305.8              1.409
"United Kingdom"       2019 -2.7019999027252197   133598.59375  .8820000290870667
"United Kingdom"       2020              -2.903       139832.2               .316
"United Kingdom"       2021               -.459       123615.3               .736
"United Kingdom"       2022              -3.137       109744.5              2.382
"United Kingdom"       2023              -2.932       105912.5              4.056
end

------------------ copy up to and including the previous line ------------------

Listed 87 out of 87 observations

[Clyde Schechter]

Well, yes, one approach would be to create a new variable that, for each year, contains the USA value. But you don't actually have to do that.

gen byte is_usa = country == "USA"
by year (is_usa country), sort: gen spread = yield10-yield10[_N]
sort country year

[Nick Cox]

Another way to do it is to get first

bysort year : egen yield_usa = total((country == "USA") * yield10) 

or

bysort year : egen yield_usa = mean(cond(country == "USA", yield10, .) )

For more explanation, see https://journals.sagepub.com/doi/pdf...867X1101100210