topic Re: How do I transpose the table in SAS Procedures

How do I transpose the table

danielhu — Mon, 25 May 2015 12:51:43 GMT

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
…	…	….	….

Re: How do I transpose the table

Sarojkumar — Tue, 26 May 2015 03:49:45 GMT

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

Re: How do I transpose the table

Karthikeyan — Tue, 26 May 2015 06:59:35 GMT

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

Re: How do I transpose the table

danielhu — Tue, 26 May 2015 07:04:59 GMT

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

Re: How do I transpose the table

AskoLötjönen — Tue, 26 May 2015 07:10:30 GMT

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;