How to capture last observations of a column into a new variable

[Giacomo Porcellotti]

Hello Statalists,


I am dealing with a real time data set with different data vinatges and I need to create a new variable that contains the last observation of each variable in the list (so I need the diagonal made up of the last ons of each var). Here the code:

g t = tq(1947q1) + _n -1
format t %tq
tsset t

local listaHPvintages " L65Q4 L66Q1 L66Q2 L66Q3 L66Q4 L67Q1 L67Q2 L67Q3 L67Q4 L68Q1 L68Q2 L68Q3 L68Q4 L69Q1 L69Q2 L69Q3 L69Q4 L70Q1 L70Q2 L70Q3 L70Q4 L71Q1 L71Q2 L71Q3 L71Q4 L72Q1 L72Q2 L72Q3 L72Q4 L73Q1 L73Q2 L73Q3 L73Q4 L74Q1 L74Q2 L74Q3 L74Q4 L75Q1 L75Q2 L75Q3 L75Q4 L76Q1 L76Q2 L76Q3 L76Q4 L77Q1 L77Q2 L77Q3 L77Q4 L78Q1 L78Q2 L78Q3 L78Q4 L79Q1 L79Q2 L79Q3 L79Q4 L80Q1 L80Q2 L80Q3 L80Q4 "

local start=tq(1965q4)
local finish=tq(2020q3)

g HPcycleGDP_realtime= .

forvalues i=`start'/`finish' {
foreach var in `listaHPvinatges' {
qui replace HPcycleGDP_realtime= HPcycle`var'[`i'] if t==`i'
}
}

Btw, each var contanis observations up to a certain t and then there are missing values.
Unfortunately the code doesn't work and HPcycleGDP_realtime remains an empty variable. Can does anyone help?

[Andrew Musau]

clear
set seed 11232022
set obs 20
local length 5
foreach var in L65Q4 L66Q1 L66Q2{
    g `var'= rnormal() in 1/`length'
    local length= `length'+5
}
g qdate= tq(1998q4)+_n
format qdate %tq

*START HERE
g tag = .
g pos=_n
g wanted=.
local i 1
foreach var in L65Q4 L66Q1 L66Q2{
    replace tag=!missing(`var')
    bys tag (qdate): replace wanted= `var'[_N] if pos==`i'
    local ++i
}
sort qdate
drop tag pos

Res.:

. l, sep(0)

     +--------------------------------------------------------+
     |     L65Q4       L66Q1       L66Q2    qdate      wanted |
     |--------------------------------------------------------|
  1. |  .1841236    .7077148   -.8692091   1999q1    .8516155 |
  2. | -.4919222    .7088638   -.3965603   1999q2   -1.133561 |
  3. | -2.775931   -.0311551   -.9049748   1999q3   -.2479252 |
  4. |  1.103625   -.9214947    1.935245   1999q4           . |
  5. |  .8516155   -1.391761     .218238   2000q1           . |
  6. |         .   -.4107559   -.4264529   2000q2           . |
  7. |         .   -.3056706    .2756058   2000q3           . |
  8. |         .    1.625134   -.9931005   2000q4           . |
  9. |         .    1.022664    1.293707   2001q1           . |
 10. |         .   -1.133561   -.0449982   2001q2           . |
 11. |         .           .   -.0705652   2001q3           . |
 12. |         .           .   -1.443918   2001q4           . |
 13. |         .           .   -.3476707   2002q1           . |
 14. |         .           .    .3533595   2002q2           . |
 15. |         .           .   -.2479252   2002q3           . |
 16. |         .           .           .   2002q4           . |
 17. |         .           .           .   2003q1           . |
 18. |         .           .           .   2003q2           . |
 19. |         .           .           .   2003q3           . |
 20. |         .           .           .   2003q4           . |
     +--------------------------------------------------------+

.

[Giacomo Porcellotti]

Thanks a lot, it works perfectly! You helped me so much!

Best of luck!