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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.