Help using Base SAS procedures

Transposing multiple variables

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Transposing multiple variables

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

Accepted Solutions
Solution
‎07-17-2016 10:52 AM
Super User
Posts: 17,912

Re: Transposing multiple variables

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;

 

View solution in original post


All Replies
Solution
‎07-17-2016 10:52 AM
Super User
Posts: 17,912

Re: Transposing multiple variables

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;

 

Contributor
Posts: 45

Re: Transposing multiple variables

Thank you I'll give that a try tomorrow morning. I always have such a hard time wrapping my head around transposing data.
Super User
Posts: 9,691

Re: Transposing multiple variables

[ Edited ]

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;

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 268 views
  • 1 like
  • 3 in conversation