Hi All,
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 31
1000 28FEB2017 31
1000 31MAR2017 31
1000 30APR2017 31
1000 31MAY2017 31
2000 31JAN2017 30
2000 28FEB2017 30
2000 31MAR2017 30
2000 30APR2017 30
;
run
I need to calculate the day a payment will be due each month based on reporting date and payment day. I can simply merge the payment day to reporting date but for instance there are not 31 days in Feb and for any month without 31 days I will be returned with missing values.
How can I get the dataset below using the data above
account_id | reporting_date | payment_day | payment_due_date |
1000 | 31-Jan-17 | 31 | 31-Jan-17 |
1000 | 28-Feb-17 | 31 | 28-Feb-17 |
1000 | 31-Mar-17 | 31 | 31-Mar-17 |
1000 | 30-Apr-17 | 31 | 30-Apr-17 |
1000 | 31-May-17 | 31 | 31-May-17 |
2000 | 31-Jan-17 | 30 | 30-Jan-17 |
2000 | 28-Feb-17 | 30 | 28-Feb-17 |
2000 | 31-Mar-17 | 30 | 30-Mar-17 |
2000 | 30-Apr-17 | 30 | 30-Apr-17 |
Adnan
You want payment day on day PD of the month. If the month is too short to have day PD, then use end-of-month:
data have;
input account_id reporting_date payment_day;
informat reporting_date date9.;
payment_date= min(intnx('month',reporting_date,-1,'end')+payment_day
,intnx('month',reporting_date,0,'end'));
format reporting_date payment_date date9.;
cards;
1000 31JAN2017 31
1000 28FEB2017 31
1000 31MAR2017 31
1000 30APR2017 31
1000 31MAY2017 31
2000 31JAN2017 30
2000 28FEB2017 30
2000 31MAR2017 30
2000 30APR2017 30
;
run;
You want payment day on day PD of the month. If the month is too short to have day PD, then use end-of-month:
data have;
input account_id reporting_date payment_day;
informat reporting_date date9.;
payment_date= min(intnx('month',reporting_date,-1,'end')+payment_day
,intnx('month',reporting_date,0,'end'));
format reporting_date payment_date date9.;
cards;
1000 31JAN2017 31
1000 28FEB2017 31
1000 31MAR2017 31
1000 30APR2017 31
1000 31MAY2017 31
2000 31JAN2017 30
2000 28FEB2017 30
2000 31MAR2017 30
2000 30APR2017 30
;
run;
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.
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.