BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
ScotchCat
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Try this modification of my earlier code

 

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

View solution in original post

6 REPLIES 6
PaigeMiller
Diamond | Level 26

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

 

--
Paige Miller
ballardw
Super User

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

ScotchCat
Obsidian | Level 7

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
Super User

@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
Diamond | Level 26

Try this modification of my earlier code

 

newdateneeded = intnx('dtmonth',lastdate,-months,'s');
format newdateneeded datetime18.;
--
Paige Miller
ScotchCat
Obsidian | Level 7

Thank You!!!   This worked perfectly!!  

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 223 views
  • 0 likes
  • 3 in conversation