Determining the number of observations in a SAS data set efficiently

[stancemcgraw]

Hello,

   I have a dataset that shows the type of procedure, listed under proc1 and the ID (studynum). Each studynum may have several different types of procedure/procedure groups that occurred on different dates. I want to count each type of observation by patient.

Data have:

Studynum      Proc1                      Proc date

1                  Debridement             8/18/15

1                   Other                       9/8/15 

1                   Other                       8/3/15

1                   Woundvac                8/4/15

1                   Debridement            8/4/15

2                   Amputation              10/5/15

2                   Other                      10/6/15

2                   Other                       10/7/15

Data Want:

Studynum    Debridement      Other       Woundvac     Amputation

1                         2                    2                  1                     0

2                         0                    2                  0                     1

[Astounding · Accepted solution]

One approach would get counts for each combination that exists in your data:

proc freq data=have;

tables StudyNum * Proc1 / noprint out=counts (keep=StudyNum Proc1 count);

run;

Then transform the data set of counts into a wider form:

proc transpose data=counts out=want;

by StudyNum;

var count;

id Proc1;

run;

Of course, this requires that any possible value for PROC1 is actually a valid name for a variable.  Also, it gives you missing values instead of zeros for combinations that didn't occur.  If you wanted to, you could go through the results later and change missing values to zero:

data want_final;

set want;

array nums {*} _numeric_;

do k=1 to dim(nums);

   if nums{k} = . then nums{k} = 0;

end;

run;