Hi,
I have the following dataset
data have;
input account_id reporting_date payment_day;
informat reporting_date date9.;
format reporting_date date9.;
cards;
1000 31JAN2017 10
1000 28FEB2017 10
1000 31MAR2017 10
1000 30APR2017 10
1000 31MAY2017 10
2000 31JAN2017 15
2000 28FEB2017 15
2000 31MAR2017 15
2000 30APR2017 15
;
run;
what I need is to use payment day and reporting month to get payment due date for each particular reporting month. For example for the first record the desired output will be 10JAN2017 and second record 10FEB2017
Split the date up with the month() and year() functions and then rebuild it with the mdy() function
data have;
input account_id reporting_date payment_day;
informat reporting_date date9.;
format reporting_date date9.;
cards;
1000 31JAN2017 10
1000 28FEB2017 10
1000 31MAR2017 10
1000 30APR2017 10
1000 31MAY2017 10
2000 31JAN2017 15
2000 28FEB2017 15
2000 31MAR2017 15
2000 30APR2017 15
;
run;
data want(drop= rmon ryear);
format due_date date9.;
set have;
rmon=month(reporting_date);
ryear=year(reporting_date);
due_date=mdy(rmon,payment_day,ryear);
run;
Split the date up with the month() and year() functions and then rebuild it with the mdy() function
data have;
input account_id reporting_date payment_day;
informat reporting_date date9.;
format reporting_date date9.;
cards;
1000 31JAN2017 10
1000 28FEB2017 10
1000 31MAR2017 10
1000 30APR2017 10
1000 31MAY2017 10
2000 31JAN2017 15
2000 28FEB2017 15
2000 31MAR2017 15
2000 30APR2017 15
;
run;
data want(drop= rmon ryear);
format due_date date9.;
set have;
rmon=month(reporting_date);
ryear=year(reporting_date);
due_date=mdy(rmon,payment_day,ryear);
run;
You could use something like the following:
payment_due_date = mdy(month(reporting_date), payment_day, year(reporting_date));
Best regards, Jos
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.