BookmarkSubscribeRSS Feed
EinarRoed
Pyrite | Level 9

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?

4 REPLIES 4
Jagadishkatam
Amethyst | Level 16

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
EinarRoed
Pyrite | Level 9

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?

Jagadishkatam
Amethyst | Level 16

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
EinarRoed
Pyrite | Level 9

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 890 views
  • 3 likes
  • 2 in conversation