topic Re: proc transpose two variables (kind of) in New SAS User

proc transpose two variables (kind of)

accintron — Wed, 28 Aug 2019 17:58:00 GMT

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!

Re: proc transpose two variables (kind of)

novinosrin — Wed, 28 Aug 2019 18:01:06 GMT


 
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;

Re: proc transpose two variables (kind of)

Reeza — Wed, 28 Aug 2019 18:02:50 GMT

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;

Re: proc transpose two variables (kind of)

accintron — Wed, 28 Aug 2019 18:06:17 GMT

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

Re: proc transpose two variables (kind of)

accintron — Wed, 28 Aug 2019 18:27:26 GMT

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