BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
TrueTears
Obsidian | Level 7

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

2 REPLIES 2
Reeza
Super User

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;
ed_sas_member
Meteorite | Level 14

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: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 474 views
  • 0 likes
  • 3 in conversation