Loop to change varnames to apply reshape

[Lars Pete]

Dear all,

My longitudinal data variables are named as: s1dsrc s1relig s1bplacf where 1 goes from 1 to 12 (12 survey waves. 36 variables). Household identifier is hhid.

1. Can I use reshape directly to convert the data from wide to long?

2. If not, how to apply loop to change the variable names: s1dsrc to sdsrc1, s1relig to srelig1 and s1bplacf to sbplacf1 so that I could use another loop that does: reshape long sdsrc srelig bplacf, i(hhid) j(wave). where "wave" is new variable.

I really appreciate your helpful responses and time.

Best regards.

[Clyde Schechter]

You can use -reshape- directly.

reshape long s@drc s@relig s@bplacf, i(unique_id) j(wave)

Now, if there are 36 such items instead of 3, that can be tedious and error prone to write out. So you can do it this way:

ds s1*
local stubs `r(varlist)'
local stubs: subinstr local stubs "1" "@", all
reshape long `stubs', i(unique_id) j(wave)

In this code, replace unique_id by the actual name of the variable(s) that uniquely identify the observations in the data set.

[Nick Cox]

See also https://www.stata.com/support/faqs/d...-with-reshape/ for various tips and tricks in this territory.

[Lars Pete]

Hi Nick, thanks a lot for the valuable resource.

Hi Clyde, thanks a lot for the solution. However, I get an error:

. reshape long `stubs', i(hhidpn) j(wave)
(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15)
variable s1@hhidpn implied name too long
r(198);

end of do-file

r(198);

and

. local stubs `r(varlist)'

. local stubs: subinstr local stubs "1" "@", all

. reshape long `stubs', i(hhidpn) j(wave)
characteristic contents too long
    The maximum value of the contents is 67,784.
r(1004);

end of do-file

r(1004);

How can I work my way around it? I appreciate your help. Thanks much.

[Nick Cox]

Please show us the complete code you used and show the results of

ds s1*

[Lars Pete]

Hi Nick Nick Cox ,
There are other variables too, h1* and r1*. But I showed only s1* for simplicity. Please find the complete code and output below.

sort hhidpn
drop r* s*
ds h1*
local stubs `r(varlist)'
local stubs: subinstr local stubs "1" "@", all
reshape long `stubs', i(hhidpn) j(wave)

The complete output is:

.
. sort hhidpn
.
. drop r* s*

. ds h1*
h10anyfin   h10hhidc    h1atotw     h11afstck   h12afothr   h13aftran   h14abond    h15aothr    h1icap      h13issi     h14ifiraw   h11inpvra   h14inpov
h10anyfam   h11hhidc    h1anethb    h11afbond   h12afdebt   h13arles    h14achck    h15adebt    h1iothr     h13ifssi    h15iirawy1  h11povthr   h14inpovr
h11anyfin   h12hhidc    h10afchck   h11afrles   h12afhoub   h13absns    h14acd      h15ahoub    h1itot      h13ifcap    h15iiraw    h11povfam   h15nhmliv
h11anyfam   h13hhidc    h10afcd     h11afbsns   h12afmrtb   h13astck    h14atran    h15amrtb    h1ifcap     h13ifothr   h15ifiraw   h11povhhi   h15pvthra
h12anyfin   h14hhidc    h10afstck   h11afothr   h12aftran   h13abond    h14aothr    h15aira     h1ifothr    h13iftot    h1nhmliv    h11inpov    h15pvfama
h12anyfam   h15hhidc    h10afbond   h11afdebt   h12arles    h13achck    h14adebt    h15ahous    h1iftot     h14pickhh   h1pvthra    h11inpovr   h15pvhhia
h13anyfin   h1afchck    h10afrles   h11afhoub   h12absns    h13acd      h14ahoub    h15amort    h10pickhh   h14icap     h1pvfama    h12nhmliv   h15inpova
h13anyfam   h1afcd      h10afbsns   h11afmrtb   h12astck    h13atran    h14amrtb    h15ahmln    h10icap     h14iothr    h1pvhhia    h12pvthra   h15inpvra
h14anyfin   h1afstck    h10afothr   h11aftran   h12abond    h13aothr    h14aira     h15afhous   h10iothr    h14itot     h1inpova    h12pvfama   h15povthr
h14anyfam   h1afbond    h10afdebt   h11arles    h12achck    h13adebt    h14ahous    h15atoth    h10itot     h14issi     h1inpvra    h12pvhhia   h15povfam
h15anyfin   h1afrles    h10afhoub   h11absns    h12acd      h13ahoub    h14amort    h15atotn    h10issi     h14ifssi    h1povthr    h12inpova   h15povhhi
h15anyfam   h1afbsns    h10afmrtb   h11astck    h12atran    h13amrtb    h14ahmln    h15atotf    h10ifssi    h14ifcap    h1povfam    h12inpvra   h15inpov
h1hhresp    h1afothr    h10aftran   h11abond    h12aothr    h13aira     h14afhous   h15atotb    h10ifcap    h14ifothr   h1povhhi    h12povthr   h15inpovr
h10hhresp   h1afdebt    h10arles    h11achck    h12adebt    h13ahous    h14atoth    h15atotw    h10ifothr   h14iftot    h1inpov     h12povfam   h1sswrer
h11hhresp   h1afhoub    h10absns    h11acd      h12ahoub    h13amort    h14atotn    h15anethb   h10iftot    h15pickhh   h1inpovr    h12povhhi   h1sswrnr
h12hhresp   h1afmrtb    h10astck    h11atran    h12amrtb    h13ahmln    h14atotf    h1afira     h11pickhh   h15icap     h10nhmliv   h12inpov    h1sswrxa
h13hhresp   h1aftran    h10abond    h11aothr    h12aira     h13afhous   h14atotb    h10afira    h11icap     h15iothr    h10pvthra   h12inpovr   h10sswrer
h14hhresp   h1aira      h10achck    h11adebt    h12ahous    h13atoth    h14atotw    h11afira    h11iothr    h15itot     h10pvfama   h13nhmliv   h10sswrnr
h15hhresp   h1arles     h10acd      h11ahoub    h12amort    h13atotn    h14anethb   h12afira    h11itot     h15issi     h10pvhhia   h13pvthra   h10sswrxa
h1cpl       h1absns     h10atran    h11amrtb    h12ahmln    h13atotf    h15afchck   h13afira    h11issi     h15ifssi    h10pvhhiad  h13pvfama   h13sswrer
h1anyfin    h1astck     h10aothr    h11aira     h12afhous   h13atotb    h15afcd     h14afira    h11ifssi    h15ifcap    h10inpova   h13pvhhia   h13sswrnr
h1anyfam    h1abond     h10adebt    h11ahous    h12atoth    h13atotw    h15afstck   h15afira    h11ifcap    h15ifothr   h10inpovad  h13inpova   h13sswrxa
h10cpl      h1achck     h10ahoub    h11amort    h12atotn    h13anethb   h15afbond   h1afmort    h11ifothr   h15iftot    h10inpvra   h13inpvra   h1hhres
h11cpl      h1acd       h10amrtb    h11ahmln    h12atotf    h14afchck   h15afrles   h10afmort   h11iftot    h10iirawy1  h10inpvrad  h13povthr   h1child
h12cpl      h1atran     h10aira     h11afhous   h12atotb    h14afcd     h15afbsns   h11afmort   h12pickhh   h10iiraw    h10povthr   h13povfam   h10hhres
h13cpl      h1aothr     h10ahous    h11atoth    h12atotw    h14afstck   h15afothr   h12afmort   h12icap     h10ifiraw   h10povfam   h13povhhi   h10child
h14cpl      h1adebt     h10amort    h11atotn    h12anethb   h14afbond   h15afdebt   h13afmort   h12iothr    h11iirawy1  h10povhhi   h13inpov    h11hhres
h15cpl      h1ahoub     h10ahmln    h11atotf    h13afchck   h14afrles   h15afhoub   h14afmort   h12itot     h11iiraw    h10povhhid  h13inpovr   h11child
h1hhid      h1amrtb     h10afhous   h11atotb    h13afcd     h14afbsns   h15afmrtb   h15afmort   h12issi     h11ifiraw   h10inpov    h14nhmliv   h12hhres
h10hhid     h1ahous     h10atoth    h11atotw    h13afstck   h14afothr   h15aftran   h1afhmln    h12ifssi    h12iirawy1  h10inpovd   h14pvthra   h12child
h11hhid     h1amort     h10atotn    h11anethb   h13afbond   h14afdebt   h15arles    h10afhmln   h12ifcap    h12iiraw    h10inpovr   h14pvfama   h13hhres
h12hhid     h1ahmln     h10atotf    h12afchck   h13afrles   h14afhoub   h15absns    h11afhmln   h12ifothr   h12ifiraw   h10inpovrd  h14pvhhia   h13child
h13hhid     h1afhous    h10atotb    h12afcd     h13afbsns   h14afmrtb   h15astck    h12afhmln   h12iftot    h13iirawy1  h11nhmliv   h14inpova   h14hhres
h14hhid     h1atoth     h10atotw    h12afstck   h13afothr   h14aftran   h15abond    h13afhmln   h13pickhh   h13iiraw    h11pvthra   h14inpvra   h14child
h15hhid     h1atotn     h10anethb   h12afbond   h13afdebt   h14arles    h15achck    h14afhmln   h13icap     h13ifiraw   h11pvfama   h14povthr   h15hhres
h1ohrshh    h1atotf     h11afchck   h12afrles   h13afhoub   h14absns    h15acd      h15afhmln   h13iothr    h14iirawy1  h11pvhhia   h14povfam   h15child
h1hhidc     h1atotb     h11afcd     h12afbsns   h13afmrtb   h14astck    h15atran    h1pickhh    h13itot     h14iiraw    h11inpova   h14povhhi

. local stubs `r(varlist)'

. local stubs: subinstr local stubs "1" "@", all

. reshape long `stubs', i(hhidpn) j(wave)
(j = 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15)
variable h1@anyfin implied name too long
r(198);

end of do-file

r(198);

Output for ds r1* s1* h1* is too big but similar as shown for ds h1*.
Thank you. I appreciate your help.

Best regards.

[Nick Cox]

The problem may lie in a tacit assumption that waves are labelled by single-digit numbers, which as you know is wrong.

You can see that the output of ds includes h10* h11@ h12@ h13@ h14@ h15* by virtue of their being subsets of h1*.

So replacing 1 in variable names with @ will replace 11 with @@ and similarly 10 12 13 14 15 will get mapped to @0 @2 @3 @4 @5. which isn't what you want.

I would try

sort hhidpn
drop r* s*
ds h15*
local stubs `r(varlist)'
local stubs: subinstr local stubs "15" "@", all
reshape long `stubs', i(hhidpn) j(wave)

[Nick Cox]

By h10* h11@ h12@ h13@ h14@ h15* I mean h10* h11* h12* h13* h14* h15*

[Lars Pete]

Hi Nick Nick Cox

Thanks a lot. I used the exact code and this approach worked much better. However, some variables with wave numbers still escaped our commands as shown below:

[CODE]
sort hhidpn
drop r* s* i*
ds h15*
local stubs `r(varlist)'
local stubs: subinstr local stubs "15" "@", all
reshape long `stubs', i(hhidpn) j(wave)
ds h*
[CODE]

. ds h*
hhidpn      haoahdhh    h3afnethb   hafdebt     hachck      hahous      hatotw      hpickhh     hiftot      h9inpovd    hnhmliv     hpovhhi     h4sswrxa    h13sswrnr
hanyfin     h1ohrshh    hafchck     hafhoub     hacd        hamort      hanethb     hicap       hiirawy1    h9inpovrd   hpvthra     hinpov      h7sswrer    h13sswrxa
hanyfam     hhhidc      hafcd       hafmrtb     hatran      hahmln      hafira      hiothr      hiiraw      h10pvhhiad  hpvfama     hinpovr     h7sswrnr    hhhres
hhhresp     h2oopmd     hafstck     haftran     haothr      hafhous     hafmort     hitot       hifiraw     h10inpovad  hpvhhia     h1sswrer    h7sswrxa    hchild
hcpl        h2oopma     hafbond     harles      hadebt      hatoth      hafhmln     hissi       h9pvhhiad   h10inpvrad  hinpova     h1sswrnr    h10sswrer
hhid        h2oopmdf    hafrles     habsns      hahoub      hatotn      h2itot2     hifssi      h9inpovad   h10povhhid  hinpvra     h1sswrxa    h10sswrnr
hacohort    h2oopmaf    hafbsns     hastck      hamrtb      hatotf      h2ifirawy1  hifcap      h9inpvrad   h10inpovd   hpovthr     h4sswrer    h10sswrxa
hhhid       h2afnethb   hafothr     habond      haira       hatotb      h2iftot2    hifothr     h9povhhid   h10inpovrd  hpovfam     h4sswrnr    h13sswrer

And if I instead do:

sort hhidpn
drop r* s* i*
ds h10*
local stubs `r(varlist)'
local stubs: subinstr local stubs "10" "@", all
reshape long `stubs', i(hhidpn) j(wave)
ds h*

I have:

. ds h*
hhidpn      hhhid       h2oopmaf    hafrles     harles      haothr      hahmln      hanethb     hpickhh     hifothr     hpvfama     hinpov      hpovhhid
hanyfin     haoahdhh    h2afnethb   hafbsns     habsns      hadebt      hafhous     hafira      hicap       hiftot      hpvhhia     hinpovr     hinpovd
hanyfam     h1ohrshh    h3afnethb   hafothr     hastck      hahoub      hatoth      hafmort     hiothr      hiirawy1    hinpova     hhhres      hinpovrd
hhhresp     hhhidc      hafchck     hafdebt     habond      hamrtb      hatotn      hafhmln     hitot       hiiraw      hinpvra     hchild      hsswrer
hcpl        h2oopmd     hafcd       hafhoub     hachck      haira       hatotf      h2itot2     hissi       hifiraw     hpovthr     hpvhhiad    hsswrnr
hhid        h2oopma     hafstck     hafmrtb     hacd        hahous      hatotb      h2ifirawy1  hifssi      hnhmliv     hpovfam     hinpovad    hsswrxa
hacohort    h2oopmdf    hafbond     haftran     hatran      hamort      hatotw      h2iftot2    hifcap      hpvthra     hpovhhi     hinpvrad

Thanks again.

Best regards.

[Nick Cox]

Is that a question? Unfortunately, I don't know anything about your data that you don't. Perhaps some questions were only asked in certain waves. Perhaps others refer to information constant across waves. You may need to do extra work working out (1) which variables you want (2) whether you need to add to the stubs so far inferred.