topic Re: Need to use intnx and other formula and I'm stuck in New SAS User

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

maliksmom2000 — Mon, 14 Jan 2019 20:50:19 GMT

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'))

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

SASKiwi — Mon, 14 Jan 2019 21:11:41 GMT

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.

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

SuryaKiran — Mon, 14 Jan 2019 21:13:15 GMT

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;

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

PaigeMiller — Mon, 14 Jan 2019 21:14:48 GMT

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.

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

Reeza — Mon, 14 Jan 2019 21:54:15 GMT

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'))