Help calculating a number field that represents months from a datetime field

[ScotchCat]

Hi - I have a data table that contains a field that represents number of months and a datetime field.   

I'm attempting to subtract the 'Months' field from the 'LastDate' field to create the 'NewDateNeeded'. 

Any assistance appreciated!!   

Months	LastDate	NewDateNeeded
2	01SEP2021:00:00:00	01JUL2023:00:00:00
1	01MAR2022:00:00:00	01FEB2022:00:00:00
4	01MAY2019:00:00:00	01JAN2019:00:00:00
3	01DEC2022:00:00:00	01SEP2022:00:00:00
4	01JUL2023:00:00:00	01MAR2023:00:00:00
2	01JAN2019:00:00:00	01NOV2018:00:00:00

[PaigeMiller]

Since your "dates" are actually what SAS considers to be date/time values, you need to use a date/time interval in INTCK.

In a SAS data step, use

newdateneeded = intck('dtmonth',lastdate,-2,'s');
format newdateneeded datetime18.;

[ballardw]

I think we have a typo and want INTNX instead of INTCK here.

[ScotchCat]

P.S. I forgot to mention that my table has well over 5K rows, typing in the month to subtract individually is not an option.  I've tried the field name instead without success.  Please Help!!  

[ballardw]

---

@ScotchCat wrote:

P.S. I forgot to mention that my table has well over 5K rows, typing in the month to subtract individually is not an option.  I've tried the field name instead without success.  Please Help!!  

---

The way your initial post reads very strongly implies that  your existing data set has a months variable to apply to the given Lastdate value. If that is not the case then where have you provided information on why month is wanted? No where.

What "field name"???? You haven't shown anything like a "field name" that would hold anything related to a number of months.

So show us an example of your actual data set. Don't add or remove anything. Then using that example of the data walk us through description of how we are supposed to know that Lastdate needs to be shifted by 2 months, 1 month or 16 months. Whatever is needed.

[PaigeMiller · Accepted solution]

Try this modification of my earlier code

newdateneeded = intnx('dtmonth',lastdate,-months,'s');
format newdateneeded datetime18.;

[ScotchCat]

Thank You!!!   This worked perfectly!!