Transpose a variable with matched info

[SherriF]

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,

[Ksharp]

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]

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;