Collapsing data in SPSS long format

Question

I have a dataset that looks like this:

Year ID Sex Age_end_of_year Count Age_group
2008 1  1   0               2     1     
2008 1  1   1               1     1  
2008 1  1   2               6     1 
2008 1  2   0               2     1     
2008 1  2   1               5     1  
2008 1  2   2               6     1 
2008 2  1   0               5     1
2008 2  1   1               4     1
2008 2  1   2               7     1
2008 2  2   0               2     1
2008 2  2   1               3     1
2008 2  2   2               2     1
.
.
.
2016 99 1   45              20    3

Sex (1 or 2), Age_end_of_year goes from 0-45, and Count can have any value 0-100. Age_group contains three categories (0-15yo, 16-30yo, 31-45yo). The dataset include well over 200k observations. I would like to keep the dataset in a long format for now, but I would like the ID to return only once per year, something like this:

Year ID Age_group_1_female Age_group_2_female Age_group_3_female Age_group_1_male ...
2008 1  8                  7                  9                  4   
2008 2  14                 3                  8                  2
2008 3  1                  2                  10                 1
2008 4  1                  14                 8                  9
.
.
.
2016 99 4                  2                  4                  9

In other words, I want to replace the Sex, Age_group and Count variables with single variables per the above example, while dropping the Age_end_of_year variable. The new variables should collapse the Count-data by sex and age_group. I've played around with aggregate, tried to transpose and restructure using the wizard but I can't get it to work. Any help appreciated!

eli-k eli-k · Accepted Answer · 2020-10-06T14:15:53

casestovars function will do the restructure as you require, but in order to improve the resulting variable names, I'll first create a string variable for sex.

string sexT (a10).
if sex=1 sexT="Male".
if sex=2 sexT="Female".

*now for the restructure.
sort cases by year id SexT Age_group.
casestovars /id=year id /index=SexT Age_group /drop=Age_end_of_year sex/sep="_".

Collapsing data in SPSS long format

1 Answers