Tricky wide to long conversion

[Brendan Churchill]

Dear Statalist users

I need help turning a wide file into a long file, however, I'm having some difficulty as there is no wave identifier variable and the wave identifying part in the variable is the second letter in the variable - LCFA005A = C is wave 3, LDFA005A = D is wave 4.

How do I convert it successfully into a long file with a wave identifier?

Any help would be appreciated

Best
Brendan

* Example generated by -dataex-. To install: ssc    install    dataex
clear
input double(LSAYID LCFA005A LDFA005A)
24852 . .
24854 . 0
24855 . .
24857 . .
24858 . .
24860 . .
24861 . .
24864 . .
24865 . .
24866 . .
24868 . .
24869 . .
24870 . .
24872 . .
24873 . .
24875 . .
24876 . .
24880 . .
24881 . .
24883 . .
24884 . .
24885 . .
24889 . .
24890 . .
24891 . .
24892 . .
24893 . .
24894 . .
24895 . 1
24896 . .
24897 . .
24898 . .
24899 . .
24901 . .
24902 . .
24903 . .
24904 . .
24906 . .
24908 . .
24909 . 1
24910 . .
24911 . .
24912 . .
24913 . .
24914 . .
24915 . .
24916 . .
24917 . .
24918 . .
24919 . .
24920 . .
24921 . .
24923 . .
24924 . .
24925 . .
24926 . .
24927 . .
24930 . .
24932 . .
24933 . .
24936 . .
24938 . .
24943 . .
24945 . .
24949 . 0
24952 . .
24954 . 0
24956 . .
24957 . .
24959 . .
24962 . .
24963 . .
24964 . .
24968 . .
24969 . .
24970 . .
24973 . .
24974 . 1
24975 . .
24977 . .
24979 . .
24980 . .
24981 . .
24984 . .
24986 . .
24987 . .
24989 . .
24990 . .
24995 . .
24996 . .
24998 . .
24999 . .
25000 . .
25003 . .
25004 . .
25005 . .
25009 . .
25010 . .
25012 . .
25013 . .
end
label values LCFA005A LCFA005A
label values LDFA005A LDFA005A
label def LDFA005A 0 "0 Not applicable", modify
label def LDFA005A 1 "1 Applicable", modify

[Andrew Musau]

rename (L?FA005A) (L#FA005A), addnumber(2)
reshape long L@FA005A, i(LSAYID) j(wave)

Res.:

. l in 1/10, sep(10)

     +----------------------------------+
     | LSAYID   wave            LFA005A |
     |----------------------------------|
  1. |  24852      2                  . |
  2. |  24852      3                  . |
  3. |  24854      2                  . |
  4. |  24854      3   0 Not applicable |
  5. |  24855      2                  . |
  6. |  24855      3                  . |
  7. |  24857      2                  . |
  8. |  24857      3                  . |
  9. |  24858      2                  . |
 10. |  24858      3                  . |
     +----------------------------------+

[Brendan Churchill]

Oh this is perfect thank you, Andrew

What if I had a much longer list of variables - is there a way to that or will I need to do this more manually

Best
Brendan

[Andrew Musau]

As long as the mapping rules are logical, e.g., as in your example, A is mapped to 3, B to 4 and so on, there will be no need to do anything manually. The code in #2 is valid for one character mapped to a number, where # in addnumber(#) refers to the starting point of a series of consecutive integers. If you have 2 or more characters, then modifying the code is straightforward, but the critical issue relates to your mapping rules.

[Nick Cox]

It's easier for me to write a loop from first principles than wrestle with the more esoteric parts of rename groups.

At a guess you have fewer than 10 waves, so can adjust this accordingly. I am also guess that LSAYID is an identifier

* Example generated by -dataex-. To install: ssc    install    dataex
clear
input double(LSAYID LCFA005A LDFA005A)
24852 . .
end
label values LCFA005A LCFA005A
label values LDFA005A LDFA005A
label def LDFA005A 0 "0 Not applicable", modify`'
label def LDFA005A 1 "1 Applicable", modify

rename LSAYID ID 

ds ID, not 

local stubs 
foreach v of var `r(varlist)' { 
    local letter = substr("`v'", 2, 1)
    local number = strpos("ABCDEFGHIJ", "`letter'")
    local prefix = substr("`v'", 1,1)  + substr("`v'", 3, .)
    local new = "`prefix'" + "`number'"
    local stubs `stubs' `prefix'
    rename `v' `new'
    
}

local stubs : list uniq stubs 
di "`stubs'"

ds 

reshape long `stubs', i(ID) j(wave)

list

[Brendan Churchill]

Hi

I have a follow-up tricky reshape that I would like some help with please. I would like to reshape this wide file into long. I want to reshape *state *hgage *esdtl *mrcurr but I do not want to reshape activity1 activity2 because this data comes from some sequence analysis data so I don't need that changed.

Is this possible. I tried Nick's code which was very helpful beforehand but I don't think it worked even with me trying to adapt it

Any help, appreciated as always
Brendan

* Example generated by -dataex-. For more info, type help dataex
clear
input str7 xwaveid byte(ghhstate ghgage gesdtl gmrcurr) int gwscei byte ghhsos int gancob byte(gedhigh1 gtchad) float(activity1 activity2 activity3) byte(hhhstate hesdtl htchad hmrcurr) int hwscei byte hhhsos int hancob byte hedhigh1
"0100003" 4 54 1 3  580 0 1101 9 3 3 3 3 4 5 3 4    0 1 1101 9
"0100005" 4 22 6 2    0 1 1101 8 1 5 5 5 4 5 1 6    0 1 1101 8
"0100010" 2 42 3 6    0 0 6101 2 0 1 1 1 2 5 0 6    0 0 6101 2
"0100015" 1 50 1 1  719 1 1101 8 3 3 3 3 1 1 3 1  851 1 1101 5
"0100016" 1 25 1 1  748 0 1101 3 0 3 3 3 1 1 0 1 1055 0 1101 3
"0100018" 5 46 2 1  431 0 1101 4 2 1 1 1 5 2 2 1  235 0 1101 4
"0100019" 5 50 1 1 1400 0 1101 1 2 3 3 3 5 1 2 1 2300 0 1101 1
"0100020" 5 19 2 6  333 0 1101 5 0 1 2 2 5 1 0 6  582 0 1101 5
"0100021" 5 15 4 6    0 0 1101 9 0 1 1 1 5 5 0 6    0 0 1101 9
"0100025" 1 19 2 6  100 0 1101 8 0 3 3 3 1 2 0 6   14 0 1101 8
"0100026" 3 16 1 6  115 3 1101 8 0 2 2 2 3 1 0 6  287 3 1101 8
"0100028" 1 38 1 6  939 1 1101 8 0 3 3 3 1 1 0 6 1001 1 1101 8
"0100029" 1 47 5 1    0 0 1101 9 4 5 5 5 1 5 4 1    0 0 1101 9
"0100030" 1 49 1 1 2129 0 2100 4 4 3 3 3 1 1 4 1 2200 0 2100 4
"0100031" 1 20 1 6  603 0 1101 5 0 3 3 3 1 1 0 6  673 0 1101 5
"0100032" 1 18 1 6  650 0 1101 8 0 2 2 2 1 2 0 6  300 0 1101 8
"0100033" 1 16 2 6   96 0 1101 9 0 2 2 2 1 6 0 6    0 0 1101 9
"0100037" 1 49 1 6  941 2 1101 9 0 3 3 3 1 1 0 6  975 2 1101 9
"0100038" 1 35 1 1  620 3 1101 8 1 3 3 3 1 1 1 1  620 3 1101 8
"0100039" 1 33 1 1  800 3 1101 5 1 3 3 3 1 1 1 1  810 3 1101 5
"0100042" 3 45 6 1    0 1 1101 5 2 5 5 5 3 6 2 1    0 1 1101 5
"0100043" 3 51 1 1  715 1 1101 9 0 3 3 3 3 1 0 1  720 1 1101 9
"0100055" 8 31 1 1 1352 0 1101 3 1 3 3 3 8 1 1 1 1669 0 1101 3
"0100056" 8 29 1 2 1900 0 1101 5 0 3 3 3 8 1 0 1 1300 0 1101 5
"0100057" 2 51 2 1  975 0 1101 1 3 1 2 1 2 2 3 1  968 0 1101 1
"0100058" 2 51 1 1 6479 0 1101 3 3 3 3 3 2 1 3 1 7855 0 1101 3
"0100059" 2 26 1 6 1036 0 1101 3 0 3 3 3 2 1 0 2 1381 0 1101 3
"0100069" 5 55 2 4  225 1 1101 9 4 5 5 3 5 6 4 4    0 1 1101 9
"0100071" 1 47 1 4 1465 0 1101 2 2 3 3 3 1 1 2 2 1444 0 1101 2
"0100082" 1 66 6 1    0 1 7103 8 3 5 5 5 1 5 3 1    0 1 7103 8
"0100083" 1 67 6 1    0 1 2100 9 3 5 5 5 1 6 3 1    0 1 2100 9
"0100084" 1 64 6 6    0 0 1101 9 0 5 5 5 1 6 0 6    0 0 1101 9
"0100097" 2 37 1 1  620 0 1101 9 1 3 3 3 2 1 1 1    0 0 1101 9
"0100098" 2 39 5 1    0 0 2100 2 1 5 5 5 2 6 1 1    0 0 2100 2
"0100100" 3 39 1 3 1269 3 1101 2 2 2 2 2 3 1 2 3 1016 3 1101 2
"0100103" 2 33 1 1 3808 0 1101 3 0 3 3 3 2 1 0 1 4987 0 1101 3
"0100105" 2 67 6 5    0 0 1101 9 5 5 5 5 2 6 5 5    0 0 1101 9
"0100106" 2 23 1 6  815 0 1101 8 0 3 4 4 2 1 0 6  807 0 1101 8
"0100107" 5 52 1 6  900 0 1101 9 0 3 3 3 5 1 0 6  920 0 1101 9
"0100113" 6 56 1 1 1275 1 1101 9 3 3 3 3 6 6 3 1    0 1 1101 9
"0100114" 6 56 2 1  375 1 1101 9 3 3 3 3 6 2 3 1  400 1 1101 9
"0100137" 2 51 1 6  750 1 1101 5 0 3 3 3 2 1 0 6 1093 1 1101 5
"0100138" 3 34 1 6  750 0 1101 8 1 3 3 3 3 5 2 6    0 0 1101 8
"0100140" 5 46 1 6 1000 0 1101 3 0 2 2 2 5 2 0 6  600 0 1101 3
"0100147" 6 47 1 3  750 1 1101 5 1 3 3 3 6 1 3 3  710 1 1101 5
"0100148" 6 44 2 3  500 2 1101 9 3 3 3 3 6 2 3 3  500 1 1101 9
"0100149" 6 18 3 6    0 1 1101 9 0 3 3 3 6 2 0 6  470 1 1101 9
"0100150" 1 60 5 1    0 0 2100 9 3 3 3 3 1 6 3 1    0 0 2100 9
"0100151" 1 64 6 1    0 0 2100 8 3 3 3 3 1 6 3 1    0 0 2100 8
"0100152" 1 43 1 1 1389 0 1101 2 3 3 3 3 1 1 3 1  913 0 1101 2
"0100158" 3 42 1 2  830 3 1101 9 1 3 3 3 3 1 1 1    0 3 1101 9
"0100164" 3 51 1 1  750 0 1101 9 2 3 3 3 3 1 2 1 1154 0 1101 9
"0100165" 3 47 1 1  800 0 1101 5 2 3 3 3 3 1 2 1 1050 0 1101 5
"0100166" 3 17 2 6  298 0 1101 8 0 2 2 2 3 2 0 6  110 0 1101 8
"0100167" 3 16 5 6    0 0 1101 9 0 1 1 1 3 5 0 6    0 0 1101 9
"0100177" 2 28 1 1 1174 3 1101 3 0 3 3 3 6 1 0 3 2050 0 1101 3
"0100178" 6 27 1 2  980 0 1101 2 0 3 3 3 6 1 0 2 1200 3 1101 2
"0100185" 3 58 1 4  972 1 1101 9 3 3 3 3 3 1 3 4  735 1 1101 9
"0100186" 3 39 1 6  683 0 1101 4 1 3 3 3 3 1 1 2  730 1 1101 4
"0100187" 3 37 1 1    0 0 1101 4 2 3 3 3 3 1 2 1 1200 0 1101 4
"0100188" 6 34 1 1  750 1 1101 5 2 3 3 3 6 1 2 1 1131 1 1101 5
"0100195" 3 52 1 4 1178 0 1101 9 1 3 3 3 3 1 1 4 1226 0 1101 9
"0100196" 3 23 1 6 1300 0 1101 8 0 3 3 3 3 1 0 6 1400 0 1101 8
"0100197" 1 33 6 1    0 0 1101 3 1 3 3 3 1 6 1 1    0 0 1101 3
"0100198" 5 53 1 1  555 3 1101 8 3 3 3 3 5 1 2 1    0 3 1101 8
"0100199" 5 47 2 1    0 3 1101 3 3 3 3 3 5 2 3 1    0 3 1101 3
"0100201" 5 16 6 6    0 3 1101 9 0 5 5 5 5 2 0 6  150 3 1101 8
"0100206" 2 34 1 1  990 0 1101 9 1 3 3 3 2 1 1 1 1100 0 1101 9
"0100207" 2 16 2 6  253 0 1101 9 0 1 1 1 2 1 0 6  400 0 1101 9
"0100208" 2 31 1 1  464 0 1101 4 0 3 3 3 2 1 0 1  646 0 1101 4
"0100209" 2 33 3 1    0 0 5203 5 0 3 3 3 2 1 0 1  810 0 5203 5
"0100217" 1 38 1 6 1726 0 1201 2 0 3 3 3 1 1 0 6 1414 0 1201 2
"0100218" 3 55 6 4    0 0 1101 3 2 5 5 5 3 6 2 4    0 0 1101 3
"0100246" 1 30 6 1  200 0 1101 3 3 3 3 3 1 2 3 1  150 0 1101 3
"0100249" 1 57 6 1    0 0 7203 1 2 5 5 5 1 6 2 1    0 0 7203 1
"0100250" 1 22 1 6  865 0 7203 3 0 3 3 4 1 1 0 6 1055 0 7203 3
"0100257" 2 38 1 6 1239 0 1101 3 0 3 3 3 2 1 0 6 1534 0 1101 3
"0100259" 5 38 1 2 1425 0 2100 5 1 3 3 3 5 1 1 2 1500 0 2100 5
"0100260" 1 53 4 4    0 1 1101 9 3 5 5 5 1 6 3 4    0 3 1101 9
"0100261" 1 24 5 6    0 1 1101 8 0 5 5 5 1 6 0 6    0 1 1101 8
"0100268" 1 49 6 4    0 1 1101 9 4 5 5 5 1 6 4 4    0 1 1101 9
"0100270" 2 47 2 1  550 3 1101 9 2 3 3 3 2 2 2 1   50 3 1101 9
"0100271" 2 48 1 1 2100 3 1101 5 2 3 3 3 2 1 2 1 2500 3 1101 5
"0100272" 2 16 2 6  115 3 1101 9 0 2 2 2 2 2 0 6  120 3 1101 9
"0100274" 3 38 5 1    0 0 1101 2 1 3 5 5 3 6 1 1    0 0 1101 2
"0100279" 3 53 1 4  500 0 2303 5 2 3 3 3 3 1 2 4    0 0 2303 5
"0100283" 2 29 1 6  856 0 1101 5 0 3 3 3 2 1 0 6  991 0 1101 5
"0100284" 5 54 1 1    0 3 1101 2 3 3 3 3 5 1 3 1  349 3 1101 2
"0100285" 5 62 1 1    0 3 1101 4 3 3 3 3 5 2 3 1    0 3 1101 4
"0100287" 2 52 1 1 1266 0 3302 8 2 3 3 3 2 1 2 1 3490 0 3302 8
"0100288" 2 61 6 1    0 0 3302 8 2 5 5 5 2 6 2 1    0 0 3302 8
"0100289" 2 45 1 1 1200 0 1101 5 4 3 3 3 2 1 4 1 1100 0 1101 5
"0100290" 2 44 1 1 1003 0 1101 1 4 3 3 3 2 1 4 1 1000 0 1101 1
"0100299" 4 42 1 1    0 0 1101 5 2 3 3 3 4 1 2 1    0 0 1101 5
"0100300" 4 45 2 1  230 0 1101 2 2 3 3 3 4 2 2 1  300 0 1101 2
"0100305" 3 26 1 6 1138 2 1101 3 0 3 3 3 3 1 0 6 1025 2 1101 3
"0100306" 3 24 2 6  529 1 1101 3 0 3 3 3 3 1 0 6  998 1 1101 3
"0100309" 4 60 5 1    0 3 1101 8 2 3 3 3 4 1 2 1  670 3 1101 8
"0100310" 1 82 6 5    0 1 1101 9 4 5 5 5 1 6 4 5    0 1 1101 9
"0100315" 2 33 1 1 2895 0 1101 1 1 3 3 3 2 1 2 1 3500 0 1101 1
end
label values ghhstate GHHSTATE
label def GHHSTATE 1 "[1] NSW", modify
label def GHHSTATE 2 "[2] VIC", modify
label def GHHSTATE 3 "[3] QLD", modify
label def GHHSTATE 4 "[4] SA", modify
label def GHHSTATE 5 "[5] WA", modify
label def GHHSTATE 6 "[6] TAS", modify
label def GHHSTATE 8 "[8] ACT", modify
label values ghgage GHGAGE
label values gesdtl GESDTL
label def GESDTL 1 "[1] Employed FT", modify
label def GESDTL 2 "[2] Employed PT", modify
label def GESDTL 3 "[3] Unemployed, looking for FT work", modify
label def GESDTL 4 "[4] Unemployed, looking for PT work", modify
label def GESDTL 5 "[5] Not in the labour force, marginally attached", modify
label def GESDTL 6 "[6] Not in the labour force, not marginally attached", modify
label values gmrcurr GMRCURR
label def GMRCURR 1 "[1] Legally married", modify
label def GMRCURR 2 "[2] De facto", modify
label def GMRCURR 3 "[3] Separated", modify
label def GMRCURR 4 "[4] Divorced", modify
label def GMRCURR 5 "[5] Widowed", modify
label def GMRCURR 6 "[6] Never married and not de facto", modify
label values gwscei GNUM
label values ghhsos GHHSOS
label def GHHSOS 0 "[0] Major Urban", modify
label def GHHSOS 1 "[1] Other Urban", modify
label def GHHSOS 2 "[2] Bounded Locality", modify
label def GHHSOS 3 "[3] Rural Balance", modify
label values gancob GCOUNTRY
label def GCOUNTRY 1101 "[1101] Australia", modify
label def GCOUNTRY 1201 "[1201] New Zealand", modify
label def GCOUNTRY 2100 "[2100] United Kingdom", modify
label def GCOUNTRY 2303 "[2303] France", modify
label def GCOUNTRY 3302 "[3302] Czech Republic", modify
label def GCOUNTRY 5203 "[5203] Malaysia", modify
label def GCOUNTRY 6101 "[6101] China (excludes SARs and Taiwan)", modify
label def GCOUNTRY 7103 "[7103] India", modify
label def GCOUNTRY 7203 "[7203] Azerbaijan", modify
label values gedhigh1 GEDHIGHB
label def GEDHIGHB 1 "[1] Postgrad - masters or doctorate", modify
label def GEDHIGHB 2 "[2] Grad diploma, grad certificate", modify
label def GEDHIGHB 3 "[3] Bachelor or honours", modify
label def GEDHIGHB 4 "[4] Adv diploma, diploma", modify
label def GEDHIGHB 5 "[5] Cert III or IV", modify
label def GEDHIGHB 8 "[8] Year 12", modify
label def GEDHIGHB 9 "[9] Year 11 and below", modify
label values gtchad GTCHAD
label def GTCHAD 0 "[0] No children ever", modify
label values activity1 activity
label values activity2 activity
label values activity3 activity
label def activity 1 "Study only", modify
label def activity 2 "Work & study", modify
label def activity 3 "Work only", modify
label def activity 5 "NILF", modify
label def activity 4 "Unemployed", modify
label values hhhstate HHHSTATE
label def HHHSTATE 1 "[1] NSW", modify
label def HHHSTATE 2 "[2] VIC", modify
label def HHHSTATE 3 "[3] QLD", modify
label def HHHSTATE 4 "[4] SA", modify
label def HHHSTATE 5 "[5] WA", modify
label def HHHSTATE 6 "[6] TAS", modify
label def HHHSTATE 8 "[8] ACT", modify
label values hesdtl HESDTL
label def HESDTL 1 "[1] Employed FT", modify
label def HESDTL 2 "[2] Employed PT", modify
label def HESDTL 5 "[5] Not in the labour force, marginally attached", modify
label def HESDTL 6 "[6] Not in the labour force, not marginally attached", modify
label values htchad HTCHAD
label def HTCHAD 0 "[0] No children ever", modify
label values hmrcurr HMRCURR
label def HMRCURR 1 "[1] Legally married", modify
label def HMRCURR 2 "[2] De facto", modify
label def HMRCURR 3 "[3] Separated", modify
label def HMRCURR 4 "[4] Divorced", modify
label def HMRCURR 5 "[5] Widowed", modify
label def HMRCURR 6 "[6] Never married and not de facto", modify
label values hwscei HNUM
label values hhhsos HHHSOS
label def HHHSOS 0 "[0] Major Urban", modify
label def HHHSOS 1 "[1] Other Urban", modify
label def HHHSOS 2 "[2] Bounded Locality", modify
label def HHHSOS 3 "[3] Rural Balance", modify
label values hancob HCOUNTRY
label def HCOUNTRY 1101 "[1101] Australia", modify
label def HCOUNTRY 1201 "[1201] New Zealand", modify
label def HCOUNTRY 2100 "[2100] United Kingdom", modify
label def HCOUNTRY 2303 "[2303] France", modify
label def HCOUNTRY 3302 "[3302] Czech Republic", modify
label def HCOUNTRY 5203 "[5203] Malaysia", modify
label def HCOUNTRY 6101 "[6101] China (excludes SARs and Taiwan)", modify
label def HCOUNTRY 7103 "[7103] India", modify
label def HCOUNTRY 7203 "[7203] Azerbaijan", modify
label values hedhigh1 HEDHIGHB
label def HEDHIGHB 1 "[1] Postgrad - masters or doctorate", modify
label def HEDHIGHB 2 "[2] Grad diploma, grad certificate", modify
label def HEDHIGHB 3 "[3] Bachelor or honours", modify
label def HEDHIGHB 4 "[4] Adv diploma, diploma", modify
label def HEDHIGHB 5 "[5] Cert III or IV", modify
label def HEDHIGHB 8 "[8] Year 12", modify
label def HEDHIGHB 9 "[9] Year 11 and below", modify

[William Lisowski]

This question was subsequently reposted, with the datex output badly mangled, at

https://www.statalist.org/forums/for...ave-identifier

and answered there.