topic Re: Group by class in SAS Programming

Group by class

stancemcgraw — Tue, 23 Jul 2019 22:12:45 GMT

I would like to categorize and count procedure type by studynum. What is the easiest way with the least amount of text?

Data have:

procedures studynum

xray 5

orif 5

irrigation 5

CT 6

ETT 6

central line 6

data want:

studynum xray orif irrigation CT ETT Central line

5 2 1 1 0 0 0

6 1 0 0 1 1 1

I also want to group them. So CT and Xray would be Imaging=1. And irrigation would be Wound/Skin=1;

Re: Group by class

novinosrin — Tue, 23 Jul 2019 22:41:21 GMT

data have;
input procedures   $15.      studynum	;
cards;
xray                    5
xray                      5
orif                        5
irrigation              5
CT                      6
ETT                   6
central line       6
;
 

proc freq data =have noprint;
tables studynum*procedures/sparse out=temp;
run;

proc transpose data=temp out=want(drop=_:);
by studynum;
var count;
id procedures;
run;

Re: Group by class

ballardw — Tue, 23 Jul 2019 22:48:45 GMT

@stancemcgraw wrote:

I would like to categorize and count procedure type by studynum. What is the easiest way with the least amount of text?

Data have:

procedures studynum

xray 5

xray 5

orif 5

irrigation 5

CT 6

ETT 6

central line 6

data want:

studynum xray orif irrigation CT ETT Central line

5 2 1 1 0 0 0

6 1 0 0 1 1 1

I also want to group them. So CT and Xray would be Imaging=1. And irrigation would be Wound/Skin=1;

Something like this is likely easiest.

options missing='0';
proc report data=have;
   columns studynum procedures ;
   define studynum / group;
   define procedures/ across '';
run ;
options missing='.';

Since your example data did not include anything with Skin or Wound kind of guessing here.

You can create groups on the fly or change the appearance of an existing value with a custom format such as:

proc format library=work;
value $mygroup
'CT', 'Xray' = 'Imaging'
'irrigation' = 'Wound/Skin'
;
run;
options missing='0';

proc report data=have;
   columns studynum procedures ;
   define studynum / group;
   define procedures/ across '' format=$mygroup.;
run ;
options missing='.';

Untested as no actual data provided. The values on the left of the = in proc format MUST be as the values appear in the actual data set. If you have values of 'CT' 'Ct' and/or 'ct' in the actual data then each of those would have to appear on the line for 'Imaging', or a separate line each such as 'ct'='Imaging' or some combination of those. The requirement though is that the value on the left only appear one time left of an equal sign.