Help using Base SAS procedures

How do I transpose the table

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

How do I transpose the table

I need some help on how to transpose my table, so that it looks like the second table.

ISINName20002001200220032004
UK123ABC50004030350020701050
UK124DEF6000433035506545678
UK125GHI50705030390683345
UK126JKL5040403039045567
YearNameISIN
2000ABC UK1235000
2001ABC UK1234030
2002ABC UK1233500
2003ABC UK1232070
2004ABC UK1231050
2000DEFUK1245000
….….

Accepted Solutions
Solution
‎05-26-2015 02:59 AM
Contributor
Posts: 37

Re: How do I transpose the table

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:

ObsISINNAMEY2000Y2001Y2002Y2003Y2004
1UK123ABC50004030350020701050
2UK124DEF6000433035506545678
3UK125GHI50705030390683345
4UK126JKL5040403039045567

Work.Want:

ObsNAMEISINYEARCOL1
1ABCUK123Y20005000
2ABCUK123Y20014030
3ABCUK123Y20023500
4ABCUK123Y20032070
5ABCUK123Y20041050
6DEFUK124Y20006000
7DEFUK124Y20014330
8DEFUK124Y20023550
9DEFUK124Y2003654
10DEFUK124Y20045678
11GHIUK125Y20005070
12GHIUK125Y20015030
13GHIUK125Y2002390
14GHIUK125Y2003683
15GHIUK125Y2004345
16JKLUK126Y20005040
17JKLUK126Y20014030
18JKLUK126Y2002390
19JKLUK126Y200345
20JKLUK126Y2004567

Thanks

View solution in original post


All Replies
Occasional Contributor
Posts: 14

Re: How do I transpose the table

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

Occasional Contributor
Posts: 17

Re: How do I transpose the table

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)

ISINName20002001200220032004
UK123ABC50004030350020701050
UK124DEF6000433035506545678
UK125GHI50705030390683345
UK126JKL5040403039045

567

Table 2

YearNameISIN
2000ABCUK1235000
2001ABCUK1234030
2002ABCUK1233500
2003ABCUK1232070
2004ABCUK1231050
2000DEFUK1245000
2001DEFUK1244330
2002DEFUK1243550

Table 3

YearUK123UK124
200050006000
200140304330
200235003550
20032070654
200410505678

Solution
‎05-26-2015 02:59 AM
Contributor
Posts: 37

Re: How do I transpose the table

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:

ObsISINNAMEY2000Y2001Y2002Y2003Y2004
1UK123ABC50004030350020701050
2UK124DEF6000433035506545678
3UK125GHI50705030390683345
4UK126JKL5040403039045567

Work.Want:

ObsNAMEISINYEARCOL1
1ABCUK123Y20005000
2ABCUK123Y20014030
3ABCUK123Y20023500
4ABCUK123Y20032070
5ABCUK123Y20041050
6DEFUK124Y20006000
7DEFUK124Y20014330
8DEFUK124Y20023550
9DEFUK124Y2003654
10DEFUK124Y20045678
11GHIUK125Y20005070
12GHIUK125Y20015030
13GHIUK125Y2002390
14GHIUK125Y2003683
15GHIUK125Y2004345
16JKLUK126Y20005040
17JKLUK126Y20014030
18JKLUK126Y2002390
19JKLUK126Y200345
20JKLUK126Y2004567

Thanks

Contributor
Posts: 44

Re: How do I transpose the table

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 325 views
  • 3 likes
  • 4 in conversation