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 |
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.