reshape long with a large number of variables

[Maye Ehab]

I have a panel data set with a large number of variables.
the variables are named hhid_06 age_06 age_12 etc
the identifier for individuals is Findid and the years are 06 and 12
I tried the below ( which was provided by @Clyde Schechter in another post) but smthg went wrong as indicated below. I think because of the zero in 06

ds Findid, not
local stubs `r(varlist)'
forvalues i = 06/12 {
local stubs: subinstr local stubs "`i'" "", all
}
local stubs: list uniq stubs
reshape long `stubs', i(Findid) j(year)

The message I got is:
(note: j = 6 12)
(note: hhid_6 not found)
(note: indid_6 not found)
(note: pn_6 not found)
(note: in_206 not found)

I think it is messed up because of the zero. how can I fix this?

Many thanks,
Maye

[Daniel Bela]

Your forvalues-loop is a loop over integer numbers 1 through 12; you probably need to manually add the leading 0 to your string to remove from the variable names inside the loop; something like this:

ds Findid, not
local stubs `r(varlist)'
forvalues i = 6/12 {
    if (inrange(`i',0,9)) local replacestring 0`i'
    else local replacestring `i'
    local stubs: subinstr local stubs "`replacestring'" "", all
}
local stubs: list uniq stubs
display `"`stubs'"' // this may be helpful to actually see what you put into -reshape-
reshape long `stubs', i(Findid) j(year)

Regards
Bela

PS: Please consider using CODE-delimiters when posting syntax examples -- it makes everything more legible.

[Nick Cox]

You're right. With your syntax forvalues ignores the leading zeros as inconsequential, not what you want here.

A solution to this problem is documented at http://www.stata-journal.com/sjpdf.h...iclenum=pr0051 and no doubt elsewhere.

To get a leading zero when you need it, push the integers through a display format.

ds Findid, not
local stubs `r(varlist)'
forvalues i = 6/12 {
    local I : di %02.0f `i'
    local stubs: subinstr local stubs "`I'" "", all
}
local stubs: list uniq stubs
reshape long `stubs', i(Findid) j(year)

Alternatively in this case, you can insist on the literal strings. Only the loop would be different:

foreach i in 06 07 08 09 10 11 12 {
    local stubs: subinstr local stubs "`i'" "", all
}

EDIT: Daniel's solution is naturally equivalent.

[Maye Ehab]

Thank you very much for your reply. Nick Cox and Daniel Bela
I think I did not state it clear. The years are only two 2006 and 2012. Hence writing forvalues i =6/12 might not be correct, right? Is there a way to use foreach for only two numbers.

I also tried something else to solve this and it worked but it might be a longer solution, I renamed the variables using the below code.
plz give me your opinion

foreach v in *_06 {
  rename `v' `v'_2006
}
foreach v in *_12 {
  rename `v' `v'_2012

[Nick Cox]

It should not matter if you cycle over 06 07 08 09 10 11 12. Stata won't replace what it doesn't find. Conversely, writing

forvalues i = 06/12 { 

does imply to Stata that you want 6 7 8 9 10 11 12.

foreach i in 06 12 {    
    local stubs: subinstr local stubs "`i'" "", all
}

would be fine.


On your latest code: No; that won't work. Let's consider why.

Focus on age_06 age_12 etc. which are variables in your original dataset. Sure, you can store their variable labels, but not usefully as they are not variables after the reshape. Conversely, after reshape you want to assign a variable label to age, but it was never a variable in the original dataset so you have not stored its variable label.

There will be a way to do this, but you have to explain how the new variable labels are related to the old variable labels.

Sometimes it's easier just to type them in!

[Maye Ehab]

I tried the below code but it gave me an error message: "option / not allowed".

ds Findid, not
local stubs `r(varlist)'

foreach i in 06 12 {    
local I : di %02.0f `i'
    local stubs: subinstr local stubs "`I'" "", all
}
local stubs: list uniq stubs // NOW ELIMINATE DUPLICATE MENTIONS OF THE STUBS
display `"`stubs'"' // this may be helpful to actually see what you put into -reshape-
reshape long `stubs', i(Findid) j(year)//

and when I tried the below solution, it did not work properly either.
all the variables ending with 06 and some variable ending with 12remained in their wide format (i.e. age_06 attr_98_06_12 panel_wt_98_06_12 panel_wt_06_12 hhtype_06

ds Findid, not
local stubs `r(varlist)'
forvalues i = 6/12 {
    if (inrange(`i',0,9)) local replacestring 0`i'
    else local replacestring `i'
    local stubs: subinstr local stubs "`replacestring'" "", all
}
local stubs: list uniq stubs
display `"`stubs'"' // this may be helpful to actually see what you put into -reshape-
reshape long `stubs', i(Findid) j(year)

[Maye Ehab]

I figured out what is wrong in the first code. the reshape command was missing string option.

was not able to figure out the error in the second code though.

Many thanks for your support.