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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.