topic This INTNX-expression has renounced reason and logic in SAS Data Management

This INTNX-expression has renounced reason and logic

EinarRoed — Mon, 11 Nov 2013 11:32:52 GMT

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?

Re: This INTNX-expression has renounced reason and logic

Jagadishkatam — Mon, 11 Nov 2013 12:01:51 GMT

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

Re: This INTNX-expression has renounced reason and logic

EinarRoed — Mon, 11 Nov 2013 12:21:54 GMT

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_DT

INPUT, REQ_MAT_DURATION

OUTPUT VALUE

29MAR2013

29MAR2027

24MAR2012

24MAR2013

01JUL2016

01JUL2026

24FEB2009

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?

Re: This INTNX-expression has renounced reason and logic

Jagadishkatam — Mon, 11 Nov 2013 12:32:06 GMT

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

Re: This INTNX-expression has renounced reason and logic

EinarRoed — Tue, 12 Nov 2013 10:34:53 GMT

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.