topic Re: Lookup date in SAS Procedures

Lookup date

zorro3338 — Wed, 11 Mar 2015 01:24:08 GMT

Hi everyone, hoping someone could assist me with a SAS statement to produce an output I'm after.

I have two separate datasets as below:

Policy ClaimDate

ABC Jan 2012

ABC Aug 2012

ABC Jul 2014

Policy RenewalDate

ABC Sep 2012

ABC Sep 2013

ABC Sep 2014

For this policy, I'm trying to group the ClaimDate into the RenewalDate. For example, if the claim is pre Sep 2012, I would like to group it into Sep 2012. If the claim is between Oct 2012 and Sep 2013, I would like to group it into Sep 2013.. and so forth.

The final output would be something like this:

Policy ClaimDate RenewalDate

ABC Jan 2012 Sep 2012

ABC Aug 2012 Sep 2012

ABC Jul 2014 Sep 2014

Thanks in advance!

-zorro3338

Re: Lookup date

Haikuo — Wed, 11 Mar 2015 13:37:26 GMT

Your rules needs to more specific given your example, what happens when both Aug2012 and Jan2012 is prior to Sep2012 in both rows? what happens to Sep 2013 renewaldate? How do you determine which goes to which?

Re: Lookup date

Ksharp — Wed, 11 Mar 2015 15:23:03 GMT

data want;

merge A B ;

run;

Re: Lookup date

Reeza — Wed, 11 Mar 2015 16:10:47 GMT

Sounds like you the September that's in the future.

Assuming SAS Dates, since you haven't specified, something like the following:

*claimDate=input(claim_date, yearmon7.); *if claim_date is a character variable can be converted using the input function;

if month(claimDate)<=9 then RenewalDate=mdy(9,1, year(claimDate));

else RenewalDate=mdy(9,1, year(claimDate)+1);

Format renewalDate monyy7.;