topic Re: manage data into four groups in SAS Programming

manage data into four groups

echoli — Thu, 08 Jun 2017 15:27:44 GMT

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,

Re: manage data into four groups

ballardw — Thu, 08 Jun 2017 17:03:32 GMT

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.

Re: manage data into four groups

echoli — Thu, 08 Jun 2017 18:52:42 GMT

Hi Ballardw,

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

I'll pay attention to dataset next time.