Reshape long for many similarly labeled variables

[Jack Reimer]

I have list of ~2,000 variables that I am trying to reshape from wide to long. The variables are distinguished by three separate numbers clarified below where the first asterisk is between 1-7, the second asterisk is either 1-11, 777, 888, or 99. The final asterisk is any number between 1-16. Every time that I try to reshape it either tells me that the "characteristic contents are too long" or it breaks and tells me that first_1_second_1_person_0 does not exist. I am new-ish to reshape and would appreciate any help I can get about where I might go wrong.

clear all
set obs 10

gen id = _n
local first_number 1 2 3 4 5 6 7
local second_number 1 2 3 4 5 6 7 8 9 10 11 777 888 99
local final_number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

foreach i of local first_number{
    foreach j of local second_number {
        foreach k of local final_number {
            gen first_`i'_second_`j'_person_`k' = 1
        }
    }
}

unab mylist: first_*_second_*_person_*
foreach v of local mylist {
    local stubs `"`stubs' `=substr("`v'",1,length("`v'")-2)'"'
}    
reshape long `stubs', i(id) j(person 1 2 3 4 5 6 7 8 9)

[Clyde Schechter]

There is an easier way to do this. I assume that you ultimately want to go fully long with this data, not just long on the final number. It can be done with a single reshape.

clear all
set obs 10

gen id = _n
local first_number 1 2 3 4 5 6 7
local second_number 1 2 3 4 5 6 7 8 9 10 11 777 888 99
local final_number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

foreach i of local first_number{
    foreach j of local second_number {
        foreach k of local final_number {
            gen first_`i'_second_`j'_person_`k' = 1
        }
    }
}

rename first_#_second_#_person_# first_second_person_#[1]_#[2]_#[3]

reshape long first_second_person_, i(id) j(_j) string
split _j, gen(_jj) destring parse("_")
rename _jj1 first_num
rename _jj2 seoond_num
rename _jj3 final_num
drop _j

By the way, that -reshape- takes a while to run, and in real data with more observations it might really take a long time. Be patient.

[Jack Reimer]

Thank you so much for your reply! This works great. My initial intention actually is to only go long on the final number. Is there a way to do this?

[Clyde Schechter]

Sure.

clear all
set obs 10

gen id = _n
local first_number 1 2 3 4 5 6 7
local second_number 1 2 3 4 5 6 7 8 9 10 11 777 888 99
local final_number 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

foreach i of local first_number{
    foreach j of local second_number {
        foreach k of local final_number {
            gen first_`i'_second_`j'_person_`k' = 1
        }
    }
}

unab stubs: first_*_second_*_person_1
local stubs: subinstr local stubs "person_1" "person_", all

reshape long `stubs', i(id) j(final_number)