Converting a datetime open date variable to month end date

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Converting a datetime open date variable to month end date

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


Accepted Solutions
Solution
‎04-21-2015 10:55 AM
Grand Advisor
Posts: 10,210

Re: Converting a datetime open date variable to month end date

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


All Replies
Grand Advisor
Posts: 10,210

Re: Converting a datetime open date variable to month end date

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.

Contributor
Posts: 29

Re: Converting a datetime open date variable to month end date

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

Grand Advisor
Posts: 10,210

Re: Converting a datetime open date variable to month end date

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

Esteemed Advisor
Posts: 6,646

Re: Converting a datetime open date variable to month end date

A MONTH interval needs a date value as input, not datetime.

Use

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 3,825

Re: Converting a datetime open date variable to month end date

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

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

Contributor
Posts: 29

Re: Converting a datetime open date variable to month end date

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
Esteemed Advisor
Posts: 6,646

Re: Converting a datetime open date variable to month end date

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;


---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎04-21-2015 10:55 AM
Grand Advisor
Posts: 10,210

Re: Converting a datetime open date variable to month end date

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.

Esteemed Advisor
Posts: 6,646

Re: Converting a datetime open date variable to month end date

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Grand Advisor
Posts: 10,210

Re: Converting a datetime open date variable to month end date

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

Esteemed Advisor
Posts: 6,646

Re: Converting a datetime open date variable to month end date

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Respected Advisor
Posts: 4,606

Re: Converting a datetime open date variable to month end date

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
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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