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.
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;
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?
Suppose they have 5 consecutive months. Which 3 do you want included, first 3, middle 3 or last 3?
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.
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
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;
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.