How to count distinct case IDs based on month and indicator variable

[jusjolly]

Hi folks,

The data looks like this

caseID          servicedate              indicator           sBP

10000           2020-01-10              1                       110

10000           2020-01-11              0                       99

10000           2020-03-10              1                       101

10000           2020-04-11              0                       124

10001           2020-01-11              1                       127

10001           2020-02-20              1                       98

10001           2020-03-15              0                       88

10001           2020-03-29              1                       109

I am trying to tabulate

(1) the number of unique case IDs which had a sBP measurement per month (it does not matter what the sBP measurement was, just that it occurred) where the indicator variable equals 1. In the above example, I would want to count row 1, 3, 5, 6 and 8.

As well as

(2) the total number of sBP measurements overall per month (again the value of the measurement is not important). Here, we would like to count all rows.

The date format is yymmdn6. I was hoping to somehow specify the month in the servicedate  

[japelin]

how about this?

I'm using the monyy5. format to create the year and month values.

data have;
  length  caseid servicedate indicator sBP ymc $5.;
  input caseid servicedate:yymmdd10. indicator sBP;
  format servicedate yymmdd10. ;
  ymc=put(servicedate,monyy5.);
datalines;
10000           2020-01-10              1                       110
10000           2020-01-11              0                       99
10000           2020-03-10              1                       101
10000           2020-04-11              0                       124
10001           2020-01-11              1                       127
10001           2020-02-20              1                       98
10001           2020-03-15              0                       88
10001           2020-03-29              1                       109
;
run;

proc sort data=have out=want1 nodupkey;
  by caseid ymn;
  where indicator=1;
run;
proc tabulate data=want1 out=out1;
  var caseid;
  table all;
  where indicator=1;
run;

proc tabulate data=have out=out2;
  class ymc;
  table ymc;
run;

[PeterClemmensen]

@japelin, you have ymc in your test data and ymn in your first proc sort 🙂

[PeterClemmensen]

@jusjolly, this type of question is easier to solve if you post your desired result. 

Anyways, I think this is what you want

data have;
input caseid servicedate:yymmdd10. indicator sBP;
format servicedate yymmdd10.;
ymn=put(servicedate,monyy5.);
datalines;
10000 2020-01-10 1 110 
10000 2020-01-11 0 99  
10000 2020-03-10 1 101 
10000 2020-04-11 0 124 
10001 2020-01-11 1 127 
10001 2020-02-20 1 98  
10001 2020-03-15 0 88  
10001 2020-03-29 1 109 
;

proc sql;
   select caseID
        , put(servicedate,monyy5.) as month
		, count(*) as count
   from have
   where indicator = 1
   group by caseID, calculated month;

   select caseid
         ,put(servicedate,monyy5.) as month
         ,count(sBP) as count
   from have
   group by caseID, calculated month;
quit;