Moving variables up

[Carlotta Jarach]

Dear all,

I have a simple question regarding moving variables. My dataset looks like that:

ID NVis Var1 Var2 Var3 Var 4

1 0 F 95 0 14oct2002
1 1 F 96 0 04mar2004
1 2 F 98 0 13jan2006
1 3 F 99 0 25jul2007
2 0 F 96 0 25feb2002
2 1 F 96 1 13dec2002
3 0 F 94 0 02mar2002
3 1 F 95 0 11nov2003
3 2 F 99 0 11may2007
3 3 F 99 1 19dec2007

But I want to move all the variables (except ID NVis and Var4) one row above, and obtaining this:

ID NVis Var1 Var2 Var3 Var 4

1 0 . . . 14oct2002
1 1 F 95 0 04mar2004
1 2 F 96 0 13jan2006
1 3 F 98 0 25jul2007
2 0 . . 25feb2002
2 1 F 96 0 13dec2002
3 0 . . . 02mar2002
3 1 F 94 0 11nov2003
3 2 F 95 0 11may2007
3 3 F 99 0 19dec2007

Basically, all the covariates will be moved one row below, resulting in the row at Visit 0 to have all blank and in losing information regarding last NVis for each ID.

Thanks to anyone who will help!

[William Lisowski]

Perhaps this example will start you in a useful direction.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id nvis) str1 var1 byte(var2 var3) str9 var4
1 0 "F" 95 0 "14oct2002"
1 1 "F" 96 0 "04mar2004"
1 2 "F" 98 0 "13jan2006"
1 3 "F" 99 0 "25jul2007"
2 0 "F" 96 0 "25feb2002"
2 1 "F" 96 1 "13dec2002"
3 0 "F" 94 0 "02mar2002"
3 1 "F" 95 0 "11nov2003"
3 2 "F" 99 0 "11may2007"
3 3 "F" 99 1 "19dec2007"
end
foreach v of varlist var1 var2 var3 {
    bysort id (nvis): generate L`v' = `v'[_n-1]
}
list, sepby(id)

. list, sepby(id)

     +--------------------------------------------------------------------+
     | id   nvis   var1   var2   var3        var4   Lvar1   Lvar2   Lvar3 |
     |--------------------------------------------------------------------|
  1. |  1      0      F     95      0   14oct2002               .       . |
  2. |  1      1      F     96      0   04mar2004       F      95       0 |
  3. |  1      2      F     98      0   13jan2006       F      96       0 |
  4. |  1      3      F     99      0   25jul2007       F      98       0 |
     |--------------------------------------------------------------------|
  5. |  2      0      F     96      0   25feb2002               .       . |
  6. |  2      1      F     96      1   13dec2002       F      96       0 |
     |--------------------------------------------------------------------|
  7. |  3      0      F     94      0   02mar2002               .       . |
  8. |  3      1      F     95      0   11nov2003       F      94       0 |
  9. |  3      2      F     99      0   11may2007       F      95       0 |
 10. |  3      3      F     99      1   19dec2007       F      99       0 |
     +--------------------------------------------------------------------+

Added in edit: I did not bother converting your date variable var4 back to a Stata SIF daily date variable. Copying and pasting your example data into Stata resulted in it being treated as a string.

It is better to use the dataex command to provide sample data. If you are running version 15.1 or later, or a fully updated version 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 and 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.

When asking for help with code, always show example data. When showing example data, always use dataex.

[Carlotta Jarach]

Thank you William! You literally saved my day. And for my future posts I will use dataex, thank you for the suggestion.