CODEWITHSUNDEEP
sqlsas
Stan Young
Stan Young

Reputation: 43

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

Upvotes: 0

Views: 363

Answers (1)

PeterClemmensen
PeterClemmensen

Reputation: 4937

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;

Upvotes: 1

Related Questions