Hi,
I want to transpose tables as shown below.
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,
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.