DATA Step, Macro, Functions and more

Calculate Payment due date based on payment day

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Calculate Payment due date based on payment day

 

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_idreporting_datepayment_daypayment_due_date
100031-Jan-173131-Jan-17
100028-Feb-173128-Feb-17
100031-Mar-173131-Mar-17
100030-Apr-173130-Apr-17
100031-May-173131-May-17
200031-Jan-173030-Jan-17
200028-Feb-173028-Feb-17
200031-Mar-173030-Mar-17
200030-Apr-173030-Apr-17

 

 

Adnan


Accepted Solutions
Solution
a month ago
Trusted Advisor
Posts: 1,022

Re: Calculate Payment due date based on payment day

Posted in reply to Adnan_Razaq

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;

View solution in original post


All Replies
Solution
a month ago
Trusted Advisor
Posts: 1,022

Re: Calculate Payment due date based on payment day

Posted in reply to Adnan_Razaq

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 52 views
  • 0 likes
  • 2 in conversation