topic Re: Help calculating a number field that represents months from a datetime field in New SAS User

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

ScotchCat — Mon, 11 Sep 2023 20:37:06 GMT

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

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

PaigeMiller — Mon, 11 Sep 2023 21:10:53 GMT

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.;

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

ballardw — Mon, 11 Sep 2023 21:31:43 GMT

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

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

ScotchCat — Tue, 12 Sep 2023 14:27:22 GMT

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!!

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

ballardw — Tue, 12 Sep 2023 15:13:21 GMT

@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.

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

PaigeMiller — Tue, 12 Sep 2023 16:27:01 GMT

Try this modification of my earlier code

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

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

ScotchCat — Tue, 12 Sep 2023 16:37:08 GMT

Thank You!!! This worked perfectly!!