0

I have to reshape my dataset from wide to long. I have 500 variables that range from 2016 to 2007 and are recorded as abcd2016 and so on. I needed a procedure that allowed me to reshape without writing all the variables' names and I run:

unab vars : *2016 
local stubs16 : subinstr local vars "2016" "", all
unab vars : *2015 
local stubs15 : subinstr local vars "2015" "", all

and so on, then:

reshape long `stubs16' `stubs15' `stubs14' `stubs13' `stubs12' `stubs11' `stubs10' `stubs09' `stubs08' `stubs07', i(id) j(year)

but I get the error 

invalid syntax
r(198);

Why? Can you help me to fix it?

Macrina
  • 25
  • 8
  • 1
    One set of stubs should be enough! – Nick Cox Aug 06 '18 at 15:50
  • @NickCox I am not familiar with this syntax and these commands so thank you for your comment, I will follow your suggestion. – Macrina Aug 07 '18 at 06:58
  • One early source of this advice was https://www.stata.com/support/faqs/data-management/problems-with-reshape/ where using one stub is explicit. If the variables are say `a2016 b2016 c2016 a2017 b2017 `you would need to work on `*2016`, i.e. the wildcard you work on must include all the prefixes you need. – Nick Cox Aug 07 '18 at 08:02

1 Answers1

1

The idea is to just specify the stub when reshaping to long format. To that end, you need to remove the year part from the variable name and store unique stubs in a local that you can pass to reshape:

/* (1) Fake Data */
clear
set obs 100
gen id = _n
foreach s in stub stump head {
    forvalues t = 2008(1)2018 {
        gen `s'`t' = rnormal()
    }
}

/* (2) Get a list of stubs and reshape */
/* Get a list of variables that contain 20, which is stored in r(varlist) */
ds *20*
/* remove the year part */
local prefixes = ustrregexra("`r(varlist)'","20[0-9][0-9]","")
/* remove duplicates from list */
local prefixes: list uniq prefixes 
reshape long `prefixes', i(id) j(t)

This will store the numeric suffix in a variable called t.

dimitriy
  • 9,077
  • 2
  • 25
  • 50
  • Thanks a lot for your useful suggestion, Dimitriy. Just one clarification: what should I write here in my case `gen `s'`t' = rnormal()` ? – Macrina Aug 07 '18 at 06:50
  • 1
    Nothing. That command is just to create a toy dataset. In your case you already have the data. – Nick Cox Aug 07 '18 at 07:58
  • @Macrina un-accepting the answer to the question is not a good practice. It is best to leave a follow-up comment instead asking for clarification. –  Aug 22 '18 at 19:22
  • @PearlySpencer if the meaning of acceptance is 'solving the mistake' I think it's better not accepting the answer. I had two kinds of mistakes: one, as noted by Nick, was that I repeated 10 times stubs' names; the second one was related to my Stata app and my laptop, that required "Microsoft Visual C++ 2008 Service Pack 1" x86 e x64" to be installed. I thank Dimitriy for his answer, but it was Nick's answer that helped me. Anyway, thank you everybody. We can close this thread. – Macrina Aug 23 '18 at 12:19
  • @Macrina `reshape` does not require Microsoft's Visual C++ to be installed. @Dimitriy revised his answer for you and it is only fair to reward him for his effort. The act of accepting an answer means that this is correct and helpful. Keep in mind that un-accepting answers for trivial reasons may discourage people from helping you in the future. –  Aug 23 '18 at 12:23
  • @DimitriyV.Masterov Plus one from me for the effort! –  Aug 23 '18 at 12:39
  • @PearlySpencer, of course it is not required by `reshape` but by Stata 64 bit to work on my OS, as I told you, and manage the amount of data that I had to reshape. It is just a secondary issue that I discovered once fixed the other mistake thanks to Nick. I did not know of this second meaning of the "acceptance" that you are mentioning now (as a reward for the effort). Thanksfulness is for sure what I feel for Dimitriy's effort. Never questioned that his answer is correct but, anyway, I solved my issue thanks to Nick. To conclude, it's a matter of "definitions", I did not want to be unpolite. – Macrina Aug 23 '18 at 12:49
  • @DimitriyV.Masterov I cannot put the +1 but I have accepted ;-) – Macrina Aug 23 '18 at 12:50
  • 1
    @Macrina what OS are you on? I have never heard of this requirement. Out of curiosity, can you please provide a link to a page documenting this requirement? –  Aug 23 '18 at 12:55