Tranposing table from column to rows

[TrueTears]

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.

[Reeza · Accepted solution]

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]

Hi @TrueTears 

you can do this:

Options missing=0;

Proc transpose data=have out=want name=year;
var _numeric_
By fips;
Id name;
Run;