counting consecutive months in sas

[yunnieling]

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

[mkeintz · Accepted solution]

If the data are sorted by id/month and month is stored as a sas date value, then:

data want;
  set have;
  by id;
  if first.id=1 or intck('month',lag(month),month)^=1 then consec_months=1;
  else consec_months+1;
  if last.id;
run;

[yunnieling]

thanks, the result shows each member and most recent month, but not the total count of consecutive months.

[mkeintz]

Please show the log when describing unexpected results.

[yunnieling]

Log:26
27 data want;
28 set idk;
29 by eci_id ;
30 if first.eci_id=1 or intck('month',lag(inc_mth_dt),month)^=1 then consec_months=1;
31 else consec_months+1;
32 if last.eci_id;
33 run;

NOTE: Variable month is uninitialized.
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
4162545 at 30:24
NOTE: There were 4162545 observations read from the data set WORK.IDK.
NOTE: The data set WORK.WANT has 65205 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.35 seconds
cpu time 0.35 seconds


Below is the result.
ECI_ID INC_MTH_DT month consec_months
72 01SEP2021 . 1
125 01SEP2021 . 1
203 01SEP2021 . 1
325 01SEP2021 . 1
329 01SEP2021 . 1
371 01OCT2020 . 1
438 01SEP2021 . 1
466 01MAY2019 . 1

[mkeintz]

Please read the documentation on the INTCK and LAG functions, then complete your modification of the

           intck('month',lag(inc_mth_dt),month)

expression.