Skip to content
Advertisement

counting consecutive months in sas

From the sample data below, I’m trying to count how many consecutive months a member has. In the event if an ID has a gap month in the middle, the output should show the most recent count of consecutive months this member had, see example below.

dataset=one

ID Month
72 01SEP2020
72 01OCT2020
72 01NOV2020
72 01DEC2020
72 01FEB2021
72 01MAR2021
72 01APR2021
72 01MAY2021
72 01JUN2021

desired output is as follows:

ID months_ct
72 5

Advertisement

Answer

How about

data have;
input id month : date9.;
datalines;
72 01SEP2020
72 01OCT2020
72 01NOV2020
72 01DEC2020
72 01FEB2021
72 01MAR2021
72 01APR2021
72 01MAY2021
72 01JUN2021
;

data want;
   do until (last.id);
      set have;
      by id;
      if first.id=1 or intck('month',lag(month),month)^=1 then consec_months=1;
      else consec_months + 1;
   end;
run;
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement