Group by class

[stancemcgraw]

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;

[novinosrin]

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;

[ballardw]

---

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