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
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 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.