Reshape long with local for many stubs does not work

[Max Schwarzer]

Hello, I want to reshape a dataset with many stubs. I tried to put the stubs in a local following link below. When I run it Stata tells me the variable doesn't exist, even though a variable with this name exists and it works if I put in the stubs manually. I am sure it's a quite obvious error, but I can't figure out my mistake.

https://www.stata.com/statalist/arch.../msg00506.html

Here is a minimum working example:

clear
input ID SD_C1_01_2017 SD_C1_02_2017 SD_C1_01_2018 SD_C1_02_2018
1 50 85 30 40
2 30 0 0 40
3 10 20 0 0
end 


* Put all the needed future in a local macro
unab stublist: SD_C1*


* Cut out the year (e.g. _2007) at the end to obtain the actual stubs
foreach v of local stublist {
    local stubs `"`stubs' `= substr("`v'",1,length("`v'")-4)'"' 
} 


* Reshape automatically (does not work)
reshape long `stubs' , i(ID) j(Year)


* Reshape manually (works)
reshape long SD_C1_01_ SD_C1_02_ , i(ID) j(Year)

[Wouter Wakker]

Given the data example, your local "stubs" looks like this:

. di "`stubs'"
 SD_C1_01_ SD_C1_02_ SD_C1_01_ SD_C1_02_

These are indeed the right stubs, but it contains duplicates because you will have on stub per variable. To get the unique stubs you could do this:

local stubs_uniq : list uniq stubs

. di "`stubs_uniq'"
SD_C1_01_ SD_C1_02_

Then, reshape should run without problems.

[Max Schwarzer]

Perfect, that works! Thanks a lot.

[Anne Dam]

Hello,

I have exactly the same problem.

Is there a chance you could share what the final working code would look like for the example data?

- Or does anybody know where "local stubs_uniq : list uniq stubs " fit into the original example, and what is taken out from it?

Thanks for your time!

[Chen Samulsion]

Just insert right code provided by Wouter Wakker in #2 into original codes posted in #1

clear
input ID SD_C1_01_2017 SD_C1_02_2017 SD_C1_01_2018 SD_C1_02_2018
1 50 85 30 40
2 30 0 0 40
3 10 20 0 0
end 

* Put all the needed future in a local macro
unab stublist: SD_C1*

* Cut out the year (e.g. _2007) at the end to obtain the actual stubs
foreach v of local stublist {
    local stubs `"`stubs' `= substr("`v'",1,length("`v'")-4)'"' 
}

* To get the unique stubs
local stubs_uniq : list uniq stubs

* Reshape automatically (does not work)
reshape long `stubs_uniq' , i(ID) j(Year)

* What is taken out from locals
macro list _stublist _stubs _stubs_uniq

[Liz Broom]

Hi, I'm also trying to reshape using a local that contains all but 2 of my variables. All of the var in the list are unique but when I run the reshape code, I get an invalid syntax error. I was wondering if somebody might be able to help.

clear
input filename plate a1i a1ii a1iii a2i a2ii a2iii b1i b1ii b1iii b2i b2ii b2iii
1 1 3 5 6 2 4 3 6 4 7 6 3 2
2 2 5 7 3 2 4 6 8 5 3 4 6 7
end

// generate varlist of all var except filename and plate
ds
local vars : di `r(varlist)'
local omit filename plate
local measures : list vars - omit

// reshape
reshape wide `measures', i(filename) j(plate)

invalid syntax

I'd be very grateful for some guidance. Thanks so much!

Very best,
Liz

[Clyde Schechter]

-local vars : di `r(varlist)'- is the source of the problem. Let's look at what Stata thinks you want it to do with this.

`r(varlist)' at that point is a complete list of the variables in the data set: filename plate, a* and b*.

The -local vars :di `r(varlist)'- command therefore expands to -local vars : di filename plate a1i a1ii a1iii ...-. Now, the -di- command, when, as here, presented with a list of variables, does not display their names. To do that, the variable names have to be bound in quotes. Instead, it displays their values in the first observation of the data in memory. So -local vars :di `r(varlist)'- is expanded to -local vars 1 1 3 5 6 2 4 3 6 4 7 6 3 2- Your next two commands seek to remove filename and plate from this, but since neither of those is actually in there (the values of filename and plate in observation 1 are there, but not filename and plate themselves), they change nothing. So your -reshape- command now looks like
-reshape wide 1 1 3 5 6 2 4 3 6 4 7 6 3 2, i(filename) j(plate)-.

Since Stata expects wide to be followed by a list of variable names, and instead finds a list of numbers, it really can't do anything other than halt and complain.

In fact, the entire rigmarole involving -ds- and dancing with macro functions is unnecessary. What you want to do can be accomplished instead with a single command:

reshape wide a* b*, i(filename) j(plate)

That all said, I will just take this opportunity to remind you that, in most situations, Stata works better with long data than with wide. While there are some particular commands that require or work best with wide data, they are few in number and many of them have equivalent commands that work with long data instead. So, while it is easy enough to fix your code to do this -reshape-, let me just encourage you to think carefully about why you are doing this in the first place--it might just serve to make things more complicated for whatever you want to do next. Consider leaving the data as it is, or possibly even reshaping it -long-er.

[Liz Broom]

Hi Clyde, thank you for getting back to me and apologies for missing your reply!

Your solution does not work with my actual dataset as I have lots of variation in var names, however, I can add a prefix to the var of interest and that would work perfectly, so thank you!