I am unsure of how to do something like the following. Say I have the following minimal working example table:
name fips 1991 1992 1993 1994 pol1 1001 0 0 1 1 pol2 1002 1 0 0 0 pol4 1002 0 1 1 0 pol1 1004 0 1 0 0 pol5 1004 1 1 1 1 pol6 1004 0 1 0 1
there are 6 possible names for the "name" variable ranging from pol1, pol2, ..., pol6. The variable "fips" is the ID. There are 4 years, from 1991 to 1994. I wish to obtain the following "transposed" table:
fips year pol1 pol2 pol3 pol4 pol5 pol6 1001 1991 0 0 0 0 0 0 1001 1992 0 0 0 0 0 0 1001 1993 1 0 0 0 0 0 1001 1994 1 0 0 0 0 0 1002 1991 0 1 0 0 0 0 1002 1992 0 0 0 1 0 0 1002 1993 0 0 0 1 0 0 1002 1994 0 0 0 0 0 0 1004 1991 0 0 0 0 1 0 1004 1992 1 0 0 0 1 1 1004 1993 0 0 0 0 1 0 1004 1994 0 0 0 0 1 1
The rule for the transpose is very simple: Take fips = 1001 as an example, for name = pol1, make the transpose such that the 0 and 1's correspond to the relevant year, for any names that do not correspond to the fips, set all the values to 0. In this case, in the original table, fips=1001 only corresponds to name=pol1, the renaming pol2, pol3, ..., pol6 do not appear, so all the values are 0 in the transposed table.
Thank you for any help in advance.
Transpose to a long format and then transpose back to wide format.
Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
Untested code below, should give you the idea.
proc transpose data=have out=long;
by name fips;
var Y1991-Y1994;
run;
proc sort data=long;
by fips year;
run;
proc transpose data=long out=want prefix=pol;
by fips year;
var col1;
id name;
run;
Transpose to a long format and then transpose back to wide format.
Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
Untested code below, should give you the idea.
proc transpose data=have out=long;
by name fips;
var Y1991-Y1994;
run;
proc sort data=long;
by fips year;
run;
proc transpose data=long out=want prefix=pol;
by fips year;
var col1;
id name;
run;
Hi @TrueTears
you can do this:
Options missing=0;
Proc transpose data=have out=want name=year;
var _numeric_
By fips;
Id name;
Run;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.