topic Re: Transposing multiple variables in SAS Procedures

Transposing multiple variables

michelconn — Fri, 15 Jul 2016 04:42:00 GMT

I looking to take three different exposures and tranpose their means to a single "site" variable. I'll post sample data to show what I mean. So I want to condense the site variable into one observation and tranpose the mean variable into three different variable for each exposure. For the real data I have a lot more IDs, sites, timepoints, etc. Any one have any ideas on how I would go about this? I was thinking about splitting up the data but wasn't sure of a good approach. Thanks for any help.

What I have
Date	Animal_ID	Site	Timepoint	Exposure	Mean
1-Jul	12	W1	Day 1	a	13
1-Jul	12	C	Day 1	a	23
1-Jul	12	W1	Day 1	b	25
1-Jul	12	C	Day 1	b	14
1-Jul	12	W1	Day 1	c	23
1-Jul	12	C	Day 1	c	51
1-Jul	13	W1	Day 1	a	23
1-Jul	13	C	Day 1	a	52
1-Jul	13	W1	Day 1	b	36
1-Jul	13	C	Day 1	b	15
1-Jul	13	W1	Day 1	c	29
1-Jul	13	C	Day 1	c	31

What I Want
Date	Animal_ID	Site	Timepoint	Mean A	Mean B	Mean C
1-Jul	12	W1	Day1	13	25	23
1-Jul	12	C	Day1	23	14	51
1-Jul	13	W1	Day1	23	36	29
1-Jul	13	C	Day1	52	15	31

Re: Transposing multiple variables

Reeza — Fri, 15 Jul 2016 05:32:51 GMT

Proc transpose should work, make sure to sort the data by the BY variables first.

Proc transpose data = have out=want prefix=mean;

by date animal_id site timepoint;

vat mean;

id exposure;

run;

Re: Transposing multiple variables

michelconn — Fri, 15 Jul 2016 05:36:38 GMT

Thank you I'll give that a try tomorrow morning. I always have such a hard time wrapping my head around transposing data.

Re: Transposing multiple variables

Ksharp — Fri, 15 Jul 2016 05:48:23 GMT

Check the MERGE skill proposed by Me,Matt,Arthur.T .

http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf

data have;
infile cards expandtabs truncover;
input (Date	Animal_ID	Site	Timepoint	Exposure) ($)	Mean;
cards;
1-Jul	12	W1	Day1	a	13	 
1-Jul	12	C	Day1	a	23	 
1-Jul	12	W1	Day1	b	25	 
1-Jul	12	C	Day1	b	14	 
1-Jul	12	W1	Day1	c	23	 
1-Jul	12	C	Day1	c	51	 
1-Jul	13	W1	Day1	a	23	 
1-Jul	13	C	Day1	a	52	 
1-Jul	13	W1	Day1	b	36	 
1-Jul	13	C	Day1	b	15	 
1-Jul	13	W1	Day1	c	29	 
1-Jul	13	C	Day1	c	31
;
run;

data temp(index=(xx=(Date	Animal_ID	Site	Timepoint)));
 set have;
 by Date	Animal_ID Timepoint	Exposure notsorted;
 if first.Animal_ID	then group=0;
 group+first.Exposure;
run;
proc sql;
 select distinct catt('temp(where=(group=',group,') rename=(Mean=Mean_',Exposure,'))')
  into : merge separated by ' '
   from temp;
quit;
data want;
 merge &merge;
 by Date	Animal_ID	Site	Timepoint;
 drop Exposure group;
run;