BookmarkSubscribeRSS Feed
SherriF
Obsidian | Level 7

 

Hi,

 

I want to transpose tables as shown below.

 

Untitled picture.png

 

I want to transpose year to column, meanwhile make sure the material go with the year.

 

I came up a way to do this is to contatenate  year and material columns first, and then do simple transpose. Later on, i use scan() statement to seperate the joined column.

I think this approach is a little tedious. is there any other better way to get the same result?

 

Thanks,

 

2 REPLIES 2
Ksharp
Super User

How you name these new variables in transposed data ?

Check MERGE skill me,matt,arthur.T proposed.

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

TomKari
Onyx | Level 15

I don't know if this is more tedious or not, but each step is simple!

 

Tom

 

proc sort data=Have;

by ID1 ID2 ID3 descending Year;

run;

data Years(keep=ID1 ID2 ID3 Year) Materials(keep=ID1 ID2 ID3 Material);

set OriginalTable;

run;

proc transpose data=Years out=TransposedYears(drop=_NAME_) prefix=Year;

by id1 id2 id3;

var Year;

run;

proc transpose data=Materials out=TransposedMaterials(drop=_NAME_) prefix=Material;

by id1 id2 id3;

var Material;

run;

proc sql;

create table Want as

select *

from TransposedYears Y inner join TransposedMaterials M

on Y.ID1 = M.ID1 and Y.ID2 = M.ID2 and Y.ID3 = M.ID3;

run;

 

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 900 views
  • 0 likes
  • 3 in conversation