topic Re: Returning values in Three Consecutive Months in SAS Programming

Returning values in Three Consecutive Months

OMGlob — Thu, 28 Aug 2014 15:24:25 GMT

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:

Person	Claim	Month	Year
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

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.

Re: Returning values in Three Consecutive Months

stat_sas — Thu, 28 Aug 2014 20:06:11 GMT

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;

Re: Returning values in Three Consecutive Months

ballardw — Thu, 28 Aug 2014 20:38:36 GMT

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

Re: Returning values in Three Consecutive Months

OMGlob — Thu, 28 Aug 2014 22:21:54 GMT

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.

Re: Returning values in Three Consecutive Months

OMGlob — Fri, 29 Aug 2014 13:33:40 GMT

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?

Re: Returning values in Three Consecutive Months

Ksharp — Fri, 29 Aug 2014 13:58:03 GMT

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

Re: Returning values in Three Consecutive Months

Loko — Fri, 29 Aug 2014 14:24:18 GMT

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;