DATA Step, Macro, Functions and more

manage data into four groups

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 76
Accepted Solution

manage data into four groups

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:

GroupTimepointResult1result2
A132
A223
A143
A254
A164
A254
A145
A275
A1235
A265
B155
B286
B166
B276
B14566
B2567
B16557
B298
B158
B269
B1523
B2356
B18456
B24655

 

I want my result like below:

Result1_1Result2_1Result1_2Result2_2Result1_3Result2_3Result1_4Result2_4
32235586
43546676
64544566567
4575655798
235655869
    523356
    84564655

Thanks,

 


Accepted Solutions
Solution
‎06-08-2017 02:52 PM
Super User
Posts: 10,526

Re: manage data into four groups

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.

 

View solution in original post


All Replies
Solution
‎06-08-2017 02:52 PM
Super User
Posts: 10,526

Re: manage data into four groups

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.

 

Frequent Contributor
Posts: 76

Re: manage data into four groups

Hi Ballardw,

 

Thanks so much for your help. That works!!!

I'll pay attention to dataset next time.

 

C

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 94 views
  • 0 likes
  • 2 in conversation