Need to use intnx and other formula and I'm stuck

[maliksmom2000]

Scenario - I have a column with a due date.  If the month is 10,11 or 12, the due date is December 31st of three years from the due date.  If the month due is in months 1-9, it's due on the same day just 39 months from the due date.  I had two formulas provided to me but they aren't coming back right:

ifn(MONTH(t1.INACMSA) in (10,11,12), MDY(MONTH(t1.INACMSA),DAY(t1.INACMSA),YEAR(t1.INACMSA)+3),INTNX('MONTH',t1.INACMSA,39,'S'))

ifn(MONTH(t1.INACMSA) IN (10,11,12), MDY(12,31,YEAR(t1.INACMSA)+3),intnx('MONTH',t1.INACMSA,36,'S'))

[SASKiwi]

Looks like you are trying to do this in SQL - is that right?. If so then I'm not sure if the IFN function will work properly. Using a CASE statement is probably a better approach.

[SuryaKiran · Accepted solution]

Hello,

Do you really have date values or datetime values, if datetime then you need to extract the date part from it (datepart() function helps). 

Alternatively this should work:

data test ;
format INACMSA date date9.;
INACMSA='12JAN2017'd;
Date=ifn(MONTH(INACMSA) IN (10,11,12),INTNX('YEAR',INACMSA,3,'e'),INTNX('MONTH',INACMSA,39,'s'));
run;

[PaigeMiller]

Instead of telling us "they aren't coming back right", show us (some of) the inputs and show us the outputs so we can know what is happening.

[Reeza]

Not sure why you're using both MDY and INTNX, I'd probably recommend sticking with the same one. 

This works fine for me. 

data demo;
format date date9.;
input date date9.;
cards;
01Jan2014
03Jan2018
01Oct2014
31Oct2014
01Nov2015
30Nov2016
01Dec2016
31Dec2017
01Apr2018
30Sep2015
;;;;
run;

proc sql;
create table want as
select *, case when month(date) in (10, 11, 12) then intnx('year', date, 3, 'e')
               else intnx('month', date, 39, 's') end as due_date format=date9.
from demo;
quit;

---

@maliksmom2000 wrote:

Scenario - I have a column with a due date.  If the month is 10,11 or 12, the due date is December 31st of three years from the due date.  If the month due is in months 1-9, it's due on the same day just 39 months from the due date.  I had two formulas provided to me but they aren't coming back right:

ifn(MONTH(t1.INACMSA) in (10,11,12), MDY(MONTH(t1.INACMSA),DAY(t1.INACMSA),YEAR(t1.INACMSA)+3),INTNX('MONTH',t1.INACMSA,39,'S'))

ifn(MONTH(t1.INACMSA) IN (10,11,12), MDY(12,31,YEAR(t1.INACMSA)+3),intnx('MONTH',t1.INACMSA,36,'S'))

---