Reshape from Wide to Long without Identifier and Variable number of Stubs

[Jonathan Schmalhofer]

Hello everyone,

I did stumble upon a new problem with my current dataset, which has a much more complicated wide format than I am used to. Maybe some of you allready did have such an odd case of wide data and know how to help.

I do have the following difficulties:

1. I have no identifier Variable for the Wide Variables.
2. My Dataset is quite wide. I do have about 7000 Variables.
3. The number of variables per ID is not constant, so for some IDs I have 5 and for others I have 10 variables.

I allready tried the suggestions from http://www.stata.com/support/faqs/da...-with-reshape/, but unfortunately this throws an error as well. My Data more or less looks as following:

clear
input str45 Year
"2010"
"2011"
"2012"
"2014"
end
input str45 A101Meas0010
"1.50"
"1.70"
"1.71"
"1.71"
input str45 A101Meas0020
"50"
"60"
"65"
"64"
input str45 A101Meas0020A
"51"
"62"
"64"
"68"
input str45 FE123Meas0010
"1.60"
"1.75"
"1.92"
"1.94"
input str45 FE123Meas0020
"60"
"72"
"88"
"92"
list

     +-------------------------------------------------------------+
     | Year   A10~0010   A10~0020   A1~0020A   FE1~0010   FE1~0020 |
     |-------------------------------------------------------------|
  1. | 2010       1.50         50         51       1.60         60 |
  2. | 2011       1.70         60         62       1.75         72 |
  3. | 2012       1.71         65         64       1.92         88 |
  4. | 2014       1.71         64         68       1.94         92 |
     +-------------------------------------------------------------+

In the end, I want the long data look like this:

     +--------------------------------------------------+
     | Year         ID   Meas0010   Meas0020  Meas0020A |
     |--------------------------------------------------|
  1. | 2010       A101       1.50         50          . |
  2. | 2010      FE123       1.60         51         60 |
  3. | 2011       A101       1.70         60          . |
  4. | 2011      FE123       1.75         62         72 |
  5. | 2012       A101       1.71         65          . |
  6. | 2012      FE123       1.92         64         88 |
  7. | 2014       A101       1.71         64          . |
  8. | 2014      FE123       1.94         68         92 |
     +--------------------------------------------------+

So using the STATA-FAQ-Link above, I tried this which gives me an error:

unab vars : *Meas*
local stubs : subinstr local vars "Meas0010" "", all
local stubs : subinstr local stubs "Meas0020" "", all
local stubs : subinstr local stubs "Meas0020A" "", all
reshape long "`stubs'", i(Year) j(Measurement) string
(note: j = Meas0010 Meas0020 Meas0020A)
(note: A101AMeas0010 not found)
variable A101Meas0010 not found
r(111);

Any Ideas how to reshape this? Any help and hint is highly appreciated. I never had to reshape such an odd structure before.

Additional Question: In the example above I did have to specifiy the Measurement-Names "Meas0010", "Meas0020" and "Meas0020A". Is it possible, to automate this as well? All Measurement Names start with the Keyword "Meas", so the Variablenames are always of the structure "IDMeasMeasurement-Name", e.g. "A101Meas0020A" stands for ID = "A101" and Measurement = "Meas0020A".

Cheers,
Jonathan

[Nick Cox]

Cross-posted on http://stackoverflow.com/questions/3...out-identifier

Please note our cross-posting policy which is that you should tell us about it. This is explicit in the FAQ Advice you were asked to read before posting.

8. May I cross-post to other forums?

People posting on Statalist may also post the same question on other listservers or in web forums. There is absolutely no rule against doing that.

But if you do post elsewhere, we ask that you provide cross-references in URL form to searchable archives. That way, people interested in your question can quickly check what has been said elsewhere and avoid posting similar comments. Being open about cross-posting saves everyone time.

If your question was answered well elsewhere, please post a cross-reference to that answer on Statalist.

SO carries a solution (and the advice not to be negative about Stata if you want help on it).

[Jonathan Schmalhofer]

Thank you for the solution offered in Stackoverflow, worked perfectly (Everyone having similar problems: see Link above posted by Nick Cox).
I'll will definitely double-check accordance with the FAQ more strictly next time. Sorry for the not mentioned cross-post.