SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

This INTNX-expression has renounced reason and logic

Reply
Frequent Contributor
Posts: 89

This INTNX-expression has renounced reason and logic

I have an input table with the columns PAYOUT_DT (numeric, date9) and REQ_MAT_DURATION (numeric, 2.0).

It's connected to an Extract transformation with a column using this expression:

intnx('year', PAYOUT_DT, REQ_MAT_DURATION, 'same')

An input row has these values:

PAYOUT_DT=24MAR2012

REQ_MAT_DURATION=2

Based on this I would expect the output value to be 24MAR2014, but it's 24MAR2013.

Where's the logic in this? :smileyshocked:

It works as intended for most other rows, but no all. Some output values are one year off, like above. What might cause the discrepancies?

Trusted Advisor
Posts: 1,131

Re: This INTNX-expression has renounced reason and logic

your logic is right and its giving the right output of 24MAR2014.

i used the below code

data have;

PAYOUT_DT='24MAR2012'd;

REQ_MAT_DURATION=2;

next=intnx('year',PAYOUT_DT,REQ_MAT_DURATION,'same');

format PAYOUT_DT next date9.;

run;

Thanks,

Jagadish

Thanks,
Jag
Frequent Contributor
Posts: 89

Re: This INTNX-expression has renounced reason and logic

Thanks, yes the logic definitely seems sound, but there's still discrepancies based on the data in my DVH.

Here's an example where the two upper rows get incorrect output values, while the two lower ones get correct output values.

INPUT, PAYOUT_DTINPUT, REQ_MAT_DURATIONOUTPUT VALUE
29MAR2013
15
29MAR2027

24MAR2012   

2
24MAR2013
01JUL2016
10
01JUL2026                     
24FEB2009
10
24FEB2019                     

The output years in the first two rows are 1 year lower than they should be.

Any idea what might cause this kind of discrepancy?

Trusted Advisor
Posts: 1,131

Re: This INTNX-expression has renounced reason and logic

Please check my code below and it gives the desired output, including correct outputs for the first and second row.

just wanted to check if you are passing the numeric dates in intnx().

data have;

    input PAYOUT_DT :date9. REQ_MAT_DURATION;

    output_value=intnx('year',PAYOUT_DT,REQ_MAT_DURATION,'same');

    format     PAYOUT_DT  output_value date9.;

cards;

29MAR2013     15    

24MAR2012   2     

01JUL2016    10                          

24FEB2009    10  

;

Thanks,

Jagadish

Thanks,
Jag
Frequent Contributor
Posts: 89

Re: This INTNX-expression has renounced reason and logic

Thanks for the help. As it turned out, several of the values in the field REQ_MAT_DURATION had hidden decimals. DI Studio rounded them up automatically, however the INTNX-function handled them "raw".

Problem resolved. Smiley Happy

Ask a Question
Discussion stats
  • 4 replies
  • 317 views
  • 3 likes
  • 2 in conversation