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
72 | 01SEP2020 |
72 | 01OCT2020 |
72 | 01NOV2020 |
72 | 01DEC2020 |
72 | 01FEB2021 |
72 | 01MAR2021 |
72 | 01APR2021 |
72 | 01MAY2021 |
72 | 01JUN2021 |
desired output is as follows:
72 | 5 |
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;
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;
Please show the log when describing unexpected results.
Please read the documentation on the INTCK and LAG functions, then complete your modification of the
intck('month',lag(inc_mth_dt),month)
expression.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.