proc transpose two variables (kind of)

[accintron]

I'm wondering if there is a way to transpose two variables at once using proc transpose... but one variable would essentially be transposed as the column names... Or can you utilize prefix= to call on an existing variable to name the columns?

My data looks like so:

Site_ID    S1_6_tot    Quarter_Year

1              35               Q1 2017

1              45               Q2 2017

1              81               Q3 2017

1              126             Q4 2017

Essentially I would like the pertinent Quarter Year to be the name of the column and have the S1_6_tot number the column value like:

Site_ID   Q1_2017   Q2_2017   Q3_2017  Q4_2017

1             35              45              81             126 

Thanks!

[novinosrin · Accepted solution]

 
data have;

input Site_ID    S1_6_tot    Quarter_Year & $15.;
cards;
1              35               Q1 2017
1              45               Q2 2017
1              81               Q3 2017
1              126             Q4 2017
;


proc transpose data=have out=want(drop=_name_);
by site_id;
var S1_6_tot;
id Quarter_Year;
run;

[accintron]

Thank you! Didn't realize I could use an ID statement to call Quarter_Year values. 

[Reeza]

Seems like a straight forward proc transpose. Use ID and IDLABEL to specify the naming variable.
You may need to sort first if you have more than one site id.

proc transpose data=have out=want;
by site_id;
var s1_6_tot;
id quarter_year;
idlabel quarter_year;
run;

[accintron]

Thank you Reeza! This was more straight forward then I thought it would be. Thanks for your input as well 🙂