using stack command without losing data

[Erin Burkett]

Hi everyone.

I'm working with Census data and these are my 5 columns: STATE_str, AGE, TOTAL, MALE, FEMALE (see attached screenshot).

The last three columns represent total population, male population, and female population.

My goal is to combine TOTAL, MALE, and FEMALE into one column (POPULATION) and create a new variable (SEX) that specifies 0, 1, 2 (0 for TOTAL; 1 for MALE, and 2 for FEMALE), but I need to do this by STATE_str and AGE so that for each state and age combination there are now 3 observations - one for each of the three population values. My final data set should have the following four columns:

STATE_str AGE SEX POPULATION

I can successfully combine the three TOTAL, MALE, & FEMALE variables into one column and create a unique code using the stack command and syntax:

stack TOTAL MALE FEMALE, into(POPEST)

But this results in loss of the first two columns (STATE_str & AGE).


I then tried the following syntax:

sort STATE_str AGE
by STATE_str AGE: stack TOTAL MALE FEMALE, into(POPEST)

which resulted in the return "stack may not be combined with by".

Any ideas for how to work around this? I have searched the forums for help using stack and seem to be finding only graphing-related help.

Thanks very much.

[Sergiy Radyakin]

This looks like a reshape task to me. Rename your vars MALE FEMALE TOTAL into something like g1, g2, g3. Then reshape long.

[Erin Burkett]

Thank you. I was successful using the syntax:

sort STATE_str AGE
rename TOTAL pop1 
rename MALE pop2
rename FEMALE pop3
reshape long pop, i( STATE_str AGE) j(SEX)

which resulted in a data set with the following columns:

STATE_str AGE SEX pop

The SEX variable automatically assigned "0" to TOTAL, "1" to MALE, and "2" to FEMALE.