I need some help on how to transpose my table, so that it looks like the second table.
ISIN | Name | 2000 | 2001 | 2002 | 2003 | 2004 |
UK123 | ABC | 5000 | 4030 | 3500 | 2070 | 1050 |
UK124 | DEF | 6000 | 4330 | 3550 | 654 | 5678 |
UK125 | GHI | 5070 | 5030 | 390 | 683 | 345 |
UK126 | JKL | 5040 | 4030 | 390 | 45 | 567 |
Year | Name | ISIN | ||||
2000 | ABC | UK123 | 5000 | |||
2001 | ABC | UK123 | 4030 | |||
2002 | ABC | UK123 | 3500 | |||
2003 | ABC | UK123 | 2070 | |||
2004 | ABC | UK123 | 1050 | |||
2000 | DEF | UK124 | 5000 | |||
… | … | …. | …. |
data
have
;
input ISIN $ NAME $ Y2000 Y2001 Y2002 Y2003 Y2004 ;
CARDS;
UK123 ABC 5000 4030 3500 2070 1050
UK124 DEF 6000 4330 3550 654 5678
UK125 GHI 5070 5030 390 683 345
UK126 JKL 5040 4030 390 45 567
;
RUN;
PROC PRINT DATA = HAVE;RUN;
PROC TRANSPOSE DATA = HAVE OUT = WANT(RENAME = (_NAME_ = YEAR )) ;by name ISIN ;run;
proc print data = want;run;
The Variable names must begin with a character or '_' , so I added a 'Y' at the beginning , you can process it in a datastep to the way you want.
Work.Have:
Obs | ISIN | NAME | Y2000 | Y2001 | Y2002 | Y2003 | Y2004 |
1 | UK123 | ABC | 5000 | 4030 | 3500 | 2070 | 1050 |
2 | UK124 | DEF | 6000 | 4330 | 3550 | 654 | 5678 |
3 | UK125 | GHI | 5070 | 5030 | 390 | 683 | 345 |
4 | UK126 | JKL | 5040 | 4030 | 390 | 45 | 567 |
Work.Want:
Obs | NAME | ISIN | YEAR | COL1 |
1 | ABC | UK123 | Y2000 | 5000 |
2 | ABC | UK123 | Y2001 | 4030 |
3 | ABC | UK123 | Y2002 | 3500 |
4 | ABC | UK123 | Y2003 | 2070 |
5 | ABC | UK123 | Y2004 | 1050 |
6 | DEF | UK124 | Y2000 | 6000 |
7 | DEF | UK124 | Y2001 | 4330 |
8 | DEF | UK124 | Y2002 | 3550 |
9 | DEF | UK124 | Y2003 | 654 |
10 | DEF | UK124 | Y2004 | 5678 |
11 | GHI | UK125 | Y2000 | 5070 |
12 | GHI | UK125 | Y2001 | 5030 |
13 | GHI | UK125 | Y2002 | 390 |
14 | GHI | UK125 | Y2003 | 683 |
15 | GHI | UK125 | Y2004 | 345 |
16 | JKL | UK126 | Y2000 | 5040 |
17 | JKL | UK126 | Y2001 | 4030 |
18 | JKL | UK126 | Y2002 | 390 |
19 | JKL | UK126 | Y2003 | 45 |
20 | JKL | UK126 | Y2004 | 567 |
Thanks
Hi Danielhu,
Do you face any specific issues while transposing your dataset? You can use Proc Transpose which is the simplest of ways to transpose the dataset and you can get the detailed documentation on proc transpose in sas support site as well.
Regards,
Saroj
Hi Saroj,
Sorry for not being clearer, I am trying to change table 1 to table 2. What I am getting using proc transpose only gets me to table 3. Of which I have to manually stack UK 124 and UK123 etc below UK123. This is the problem because my data is quite large. Moreover, after tranposing, my "name variable" disappears. Following is my sas code.
proc transpose data=sas.au_mv
out=sas.au_mv;
Id ISIN;
var 2000 2001 2002 2003 2004;
run;
Table 1 (the header is in the first row; year 2000-2004)
ISIN | Name | 2000 | 2001 | 2002 | 2003 | 2004 |
UK123 | ABC | 5000 | 4030 | 3500 | 2070 | 1050 |
UK124 | DEF | 6000 | 4330 | 3550 | 654 | 5678 |
UK125 | GHI | 5070 | 5030 | 390 | 683 | 345 |
UK126 | JKL | 5040 | 4030 | 390 | 45 | 567 |
Table 2
Year | Name | ISIN | |
2000 | ABC | UK123 | 5000 |
2001 | ABC | UK123 | 4030 |
2002 | ABC | UK123 | 3500 |
2003 | ABC | UK123 | 2070 |
2004 | ABC | UK123 | 1050 |
2000 | DEF | UK124 | 5000 |
2001 | DEF | UK124 | 4330 |
2002 | DEF | UK124 | 3550 |
Table 3
Year | UK123 | UK124 |
2000 | 5000 | 6000 |
2001 | 4030 | 4330 |
2002 | 3500 | 3550 |
2003 | 2070 | 654 |
2004 | 1050 | 5678 |
data
have
;
input ISIN $ NAME $ Y2000 Y2001 Y2002 Y2003 Y2004 ;
CARDS;
UK123 ABC 5000 4030 3500 2070 1050
UK124 DEF 6000 4330 3550 654 5678
UK125 GHI 5070 5030 390 683 345
UK126 JKL 5040 4030 390 45 567
;
RUN;
PROC PRINT DATA = HAVE;RUN;
PROC TRANSPOSE DATA = HAVE OUT = WANT(RENAME = (_NAME_ = YEAR )) ;by name ISIN ;run;
proc print data = want;run;
The Variable names must begin with a character or '_' , so I added a 'Y' at the beginning , you can process it in a datastep to the way you want.
Work.Have:
Obs | ISIN | NAME | Y2000 | Y2001 | Y2002 | Y2003 | Y2004 |
1 | UK123 | ABC | 5000 | 4030 | 3500 | 2070 | 1050 |
2 | UK124 | DEF | 6000 | 4330 | 3550 | 654 | 5678 |
3 | UK125 | GHI | 5070 | 5030 | 390 | 683 | 345 |
4 | UK126 | JKL | 5040 | 4030 | 390 | 45 | 567 |
Work.Want:
Obs | NAME | ISIN | YEAR | COL1 |
1 | ABC | UK123 | Y2000 | 5000 |
2 | ABC | UK123 | Y2001 | 4030 |
3 | ABC | UK123 | Y2002 | 3500 |
4 | ABC | UK123 | Y2003 | 2070 |
5 | ABC | UK123 | Y2004 | 1050 |
6 | DEF | UK124 | Y2000 | 6000 |
7 | DEF | UK124 | Y2001 | 4330 |
8 | DEF | UK124 | Y2002 | 3550 |
9 | DEF | UK124 | Y2003 | 654 |
10 | DEF | UK124 | Y2004 | 5678 |
11 | GHI | UK125 | Y2000 | 5070 |
12 | GHI | UK125 | Y2001 | 5030 |
13 | GHI | UK125 | Y2002 | 390 |
14 | GHI | UK125 | Y2003 | 683 |
15 | GHI | UK125 | Y2004 | 345 |
16 | JKL | UK126 | Y2000 | 5040 |
17 | JKL | UK126 | Y2001 | 4030 |
18 | JKL | UK126 | Y2002 | 390 |
19 | JKL | UK126 | Y2003 | 45 |
20 | JKL | UK126 | Y2004 | 567 |
Thanks
In SAS you cannot have variable names starting with number, so you have add some literal prefix.
You have to remove these prefixes from Year column in data step after proc transpose.
proc transpose data=have out=want(rename=(_Name_ =Year Col1=VALUE));
var Year2000 Year2001 Year2002 Year2003 Year2004;
by isin Name;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.