Hi,
I have a dataset below, I want to seperate data into four groups by Group (TX, Cont) and Timepoint (1, 2).
here is my code:
data want1;
set want;
if timepoint = 1 then
if group = "TX" then do
result1_1 = result1;
result2_1 = result2;
else result1_2 = result1;
result2_2 = result2;
end;
else if timepoint = 2 then
if group = "TX" then do
result1_3 = result1;
result2_3 = result2;
else result1_4= result1;
result2_4 = result2;
output;
end;
run;
is my code wrong? the ouput is not what I want.
Here is my data:
Group | Timepoint | Result1 | result2 |
A | 1 | 3 | 2 |
A | 2 | 2 | 3 |
A | 1 | 4 | 3 |
A | 2 | 5 | 4 |
A | 1 | 6 | 4 |
A | 2 | 5 | 4 |
A | 1 | 4 | 5 |
A | 2 | 7 | 5 |
A | 1 | 23 | 5 |
A | 2 | 6 | 5 |
B | 1 | 5 | 5 |
B | 2 | 8 | 6 |
B | 1 | 6 | 6 |
B | 2 | 7 | 6 |
B | 1 | 456 | 6 |
B | 2 | 56 | 7 |
B | 1 | 655 | 7 |
B | 2 | 9 | 8 |
B | 1 | 5 | 8 |
B | 2 | 6 | 9 |
B | 1 | 5 | 23 |
B | 2 | 3 | 56 |
B | 1 | 8 | 456 |
B | 2 | 4 | 655 |
I want my result like below:
Result1_1 | Result2_1 | Result1_2 | Result2_2 | Result1_3 | Result2_3 | Result1_4 | Result2_4 |
3 | 2 | 2 | 3 | 5 | 5 | 8 | 6 |
4 | 3 | 5 | 4 | 6 | 6 | 7 | 6 |
6 | 4 | 5 | 4 | 456 | 6 | 56 | 7 |
4 | 5 | 7 | 5 | 655 | 7 | 9 | 8 |
23 | 5 | 6 | 5 | 5 | 8 | 6 | 9 |
5 | 23 | 3 | 56 | ||||
8 | 456 | 4 | 655 |
Thanks,
Please provide data in a data step so we don't have to write one that may be wrong.
Consider:
data want; input Group $ Timepoint Result1 result2 ; datalines; A 1 3 2 A 2 2 3 A 1 4 3 A 2 5 4 A 1 6 4 A 2 5 4 A 1 4 5 A 2 7 5 A 1 23 5 A 2 6 5 B 1 5 5 B 2 8 6 B 1 6 6 B 2 7 6 B 1 456 6 B 2 56 7 B 1 655 7 B 2 9 8 B 1 5 8 B 2 6 9 B 1 5 23 B 2 3 56 B 1 8 456 B 2 4 655 ; run; proc sort data=want; by group timepoint; run; proc transpose data=want out= wanttrans; by group timepoint; var result1 result2; run; data temp; set wanttrans; grplbl = catx('_',_name_,group,timepoint); run; proc transpose data=temp out=temptrans; id grplbl; var col: ; run;
The TempTrans looks like of like what you say you want. Since I could not tell what your 1_1 or 1_4 was supposed to represent I used values of the group and timepoint variables in the name. So LONG group or timepoint values might cause problems.
Please provide data in a data step so we don't have to write one that may be wrong.
Consider:
data want; input Group $ Timepoint Result1 result2 ; datalines; A 1 3 2 A 2 2 3 A 1 4 3 A 2 5 4 A 1 6 4 A 2 5 4 A 1 4 5 A 2 7 5 A 1 23 5 A 2 6 5 B 1 5 5 B 2 8 6 B 1 6 6 B 2 7 6 B 1 456 6 B 2 56 7 B 1 655 7 B 2 9 8 B 1 5 8 B 2 6 9 B 1 5 23 B 2 3 56 B 1 8 456 B 2 4 655 ; run; proc sort data=want; by group timepoint; run; proc transpose data=want out= wanttrans; by group timepoint; var result1 result2; run; data temp; set wanttrans; grplbl = catx('_',_name_,group,timepoint); run; proc transpose data=temp out=temptrans; id grplbl; var col: ; run;
The TempTrans looks like of like what you say you want. Since I could not tell what your 1_1 or 1_4 was supposed to represent I used values of the group and timepoint variables in the name. So LONG group or timepoint values might cause problems.
Hi Ballardw,
Thanks so much for your help. That works!!!
I'll pay attention to dataset next time.
C
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.