BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SasGuy614
Fluorite | Level 6

I have a date field titled "dateopened" that is formatted as datetime22.3 but it will not work with the intnx function. I was under the impression that the intnx function works well with datetime formats. Every time I run the code I get missing values along with the following error message: NOTE: Invalid argument to function INTNX.

TEST = intnx('month',DateOpened,0,'e');


What is the most efficient way to convert a datetime intra-month variable to the month end datetime variable?


Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

30Jun1975 0:00:00.000 is the beginning of the day. You asked SAS to advance a datetime to the end of the month which is being interpreted as the last increment before the next day.

I submit that from you data you are not actually using the time component and should be looking at the date. There has become an obnoxious, in my opinion, indiscriminate use of datetime fields when the data is actually only dates, possibly perpetrated by some Micro$oft defaults.

Convert the datetimes to date using:

DateOpened = Datepart(DateOpened);

PCAmonthOpened = DatePart(PCAmonthOpened);

format DateOpened PcAmonthOpened date9.;

THEN get the end of the month using the Month in intnx.

View solution in original post

12 REPLIES 12
ballardw
Super User

Run proc contents on the dataset. Verify that the variable is 1)numeric and 2) formatted as datetime.

Invalid argument would generally mean that your DateOpened is either character OR is a number that would represent a date or datetime outside of valid dates in SAS. Since dates between 1581 AD and 19999 AD are okay, I suspect it is character that appears as if it is formatted.

SasGuy614
Fluorite | Level 6

Thanks for the suggestion. I ran proc contents and the type: Num, Format: Datetime22.3, and Informat: Datetime22.3

ballardw
Super User

To increment datetime valued variables by date intervals use DT at the start of the interval amount: DTMONTH instead of MONTH.

Patrick
Opal | Level 21

Just to make sure that the answer from gets the attention it deserves.

TEST = intnx('dtmonth',DateOpened,0,'e');

SasGuy614
Fluorite | Level 6

I tried your suggestion but time is not formatted correctly for me. The time converts to 11:59:59 PM but I need it to be 12:00:00 AM. Below is a sample of what the current query and export looks like.

Proc contents: Variable - DateOpened

proc contents and the type: Num, Format: Datetime22.3, and Informat: Datetime22.3

Code:

PCAmonthOpened = intnx('dtmonth',DateOpened,0,'e');

format PCAmonthOpened datetime22.3;

AccountNumberDateOpenedPCAmonthOpenedDesiredField
1000xxx26Jun1975 0:00:00.00030Jun1975 23:59:59.00030Jun1975 0:00:00.000
1000xxx06Jun2006 0:00:00.00030Jun2006 23:59:59.00030Jun2006 0:00:00.000
1000xxx06Jun2006 0:00:00.00030Jun2006 23:59:59.00030Jun2006 0:00:00.000
1000xxx22May2006 0:00:00.00031May2006 23:59:59.00031May2006 0:00:00.000
1000xxx22May2006 0:00:00.00031May2006 23:59:59.00031May2006 0:00:00.000
1000xxx23Apr2007 0:00:00.00030Apr2007 23:59:59.00030Apr2007 0:00:00.000
Kurt_Bremser
Super User

I'd say that by definition, 12:00:00 AM is the start of a new day and implicitly month.

So try

TEST = intnx('month',datepart(DateOpened),0,'end')*86400;

format TEST DATETIME22.3;


ballardw
Super User

30Jun1975 0:00:00.000 is the beginning of the day. You asked SAS to advance a datetime to the end of the month which is being interpreted as the last increment before the next day.

I submit that from you data you are not actually using the time component and should be looking at the date. There has become an obnoxious, in my opinion, indiscriminate use of datetime fields when the data is actually only dates, possibly perpetrated by some Micro$oft defaults.

Convert the datetimes to date using:

DateOpened = Datepart(DateOpened);

PCAmonthOpened = DatePart(PCAmonthOpened);

format DateOpened PcAmonthOpened date9.;

THEN get the end of the month using the Month in intnx.

Kurt_Bremser
Super User

From the POV of database administration, datetime values need less space than separate date and time values, so it makes sense to "condense" the values there. Obviously some Micromoron mistook Excel for a database system Smiley Sad

ballardw
Super User

My concern is that I have several data sources where the value is supposed to be a date and time from the collection design point forward were not a part of the system at all. The data sources were told to provide a date value in the extracted data. I get datetime values with the "time" component 100% of 00:00:00.00. So in these cases I tend to think a default, or possibly lazy programmer no-cookie, since there is no time component needed to compress for efficiency. When I need times a do appreciate the datetime as it usually simplifies many issues. Except for the system I worked with that reported both 00:00 and 24:00 hours...

Kurt_Bremser
Super User

Another reason for switching dates to datetime values is this:

When working across timezones, the same point in time may resolve to different dates, so it may be necessary to design the dates as datetime in the database so that future uses of the DB can actually put the GMT datetime there.

We had to do it for our main DB design so that it could be used in Russia.

PGStats
Opal | Level 21

The error might go away if you use "DTMONTH' as argument to INTNX(). DTMONTH is the interval name (expressed in seconds instead of days) required for datetime values. - PG

PG

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 3560 views
  • 1 like
  • 5 in conversation