Hi,
I have a data table in the following form:
Company | Var | year_2001 | year_2002 |
A | x | 11 | 31 |
A | y | 12 | 32 |
A | z | 13 | 33 |
B | x | 21 | 41 |
B | y | 22 | 42 |
And I would like this table to be transformed into the following table:
Company | year | x | y | z |
A | year_2001 | 11 | 12 | 13 |
A | year_2002 | 31 | 32 | 33 |
B | year_2001 | 21 | 22 | . |
B | year_2002 | 41 | 42 | . |
So here what I need is to transform the values of "Var" into columns, and to transform the "2001" and "2002" columns into a new column "Year".
Thanks!
data want;
input company $ var $ year_2001 year_2002;
datalines;
A x 11 31
A y 12 32
A z 13 33
B x 21 41
B y 22 42
;
run;
proc print data=want;
run;
proc transpose data=want
out=have
name=year;
by company;
id var;
run;
proc print data=have;
run;
EDIT - realized after posting that I probably should have reversed the data set names. 🙂
2001 and 2002 are not valid SAS variable names 🙂
data want;
input company $ var $ year_2001 year_2002;
datalines;
A x 11 31
A y 12 32
A z 13 33
B x 21 41
B y 22 42
;
run;
proc print data=want;
run;
proc transpose data=want
out=have
name=year;
by company;
id var;
run;
proc print data=have;
run;
EDIT - realized after posting that I probably should have reversed the data set names. 🙂
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.