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;