BookmarkSubscribeRSS Feed
zorro3338
Calcite | Level 5

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

3 REPLIES 3
Haikuo
Onyx | Level 15

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?

Ksharp
Super User

data want;

merge A B ;

run;

??

Reeza
Super User

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.;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 706 views
  • 0 likes
  • 4 in conversation