Calculate Payment due date based on payment day

[Adnan_Razaq]

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

[mkeintz · Accepted solution]

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;