New Contributor
Posts: 3

Returning values in Three Consecutive Months

Hello all,

I have a data set with a bunch of people in it, each of whom have different claims at different times.  I need a code to return all claims which are billed for three consecutive months for that particular person.  For example, I have the following:

PersonClaimMonthYear
A1Mar2013
A2Apr2013
A3May2013
A4Aug2014
B5Sep2012
B6Oct2012
B7Oct2012
C8Dec2013

I basically just need to it return the 'Claim' values of 1, 2, and 3 since person A billed in three consecutive months.  How would I do this?

Much appreciated.

Posts: 1,270

Re: Returning values in Three Consecutive Months

data want;
set have;
by person;
Mon=month(input(catt(Month,year),monyy7.));
lg=mon-lag(mon);
if first.person then cnt=1;
if not first.person and lg>1 then cnt+1;
run;

proc sql;
select person,claim,month,year from want
group by person,cnt
having count(distinct mon)=3
order by person, claim;
quit;

New Contributor
Posts: 3

Re: Returning values in Three Consecutive Months

This is mostly working, but I don't think it's taking years into account years.  Defining the Mon variable, Oct 2012 and Oct 2013 have the same value, no?  How do I account for this?

Super User
Posts: 13,583

Re: Returning values in Three Consecutive Months

Suppose they have 5 consecutive months. Which 3 do you want included, first 3, middle 3 or last 3?

New Contributor
Posts: 3

Re: Returning values in Three Consecutive Months

Ballardw, I would like it to include all five months in that case.  They just have to have at least three.

Stat@SAS, thanks, I will try that code at work tomorrow.

Super User
Posts: 10,787

Re: Returning values in Three Consecutive Months

```data have;
input (person claim month year ) ( : \$40.);
date=input(cats('01',month,year),date9.);
format date date9.;
cards;
A     1     Mar     2013
A     2     Apr     2013
A     3     May     2013
A     4     Aug     2014
B     5     Sep     2012
B     6     Oct     2012
B     7     Oct     2012
C     8     Dec     2013
;
run;

data temp;
set have;
if lag(person) eq person and intck('month',lag(date),date)=1 then do;
lag_n=_n_-1;
set have point=lag_n ;output;
set have point=_n_ ;output;
end;
run;
proc sql;
create table want as
select * from (select distinct * from temp)
group by person
having count(*) gt 2 ;
quit;
```

Xia Keshan

Super Contributor
Posts: 319

Re: Returning values in Three Consecutive Months

Hello,

Something like this (assuming the dataset is sorted):

data have;
input Person \$ Claim Month \$ Year ;
datalines;
A 1 Mar 2013
A 2 Apr 2013
A 3 May 2013
A 4 Aug 2014
A 5 Sep 2014
A 6 Oct 2014
A 7 Nov 2014
A 8 Dec 2014
A 9 Jan 2015
B 5 Sep 2012
B 6 Oct 2012
B 7 Nov 2013
C 8 Dec 2013
;

data want;
set have;
by person;

retain flag;

put flag "after";

curmonth=month(input(cats(month,year),monyy7.));
prevmonth=month(input(cats((lag(month)),lag(year)),monyy7.));
curdate=mdy(curmonth,1,year);
prevdate=intnx('month',mdy(prevmonth,1,lag(year)),1);

if first.person then flag=1;
else if curdate=prevdate then flag+1;
else flag=1;

if flag gt 3 then flag=1;

drop cur: prev:;
run;

Discussion stats
• 6 replies
• 1099 views
• 0 likes
• 5 in conversation