SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Transpose a variable with matched info

Reply
Occasional Contributor
Posts: 14

Transpose a variable with matched info

 

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,

 

Super User
Posts: 10,018

Re: Transpose a variable with matched info

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

PROC Star
Posts: 1,167

Re: Transpose a variable with matched info

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;

 

 

 

Ask a Question
Discussion stats
  • 2 replies
  • 240 views
  • 0 likes
  • 3 in conversation