Converting a datetime open date variable to month end date

[SasGuy614]

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

[ballardw]

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]

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

[ballardw]

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

[Kurt_Bremser]

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

Use

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

[Patrick]

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

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

[SasGuy614]

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;

AccountNumber	DateOpened	PCAmonthOpened	DesiredField
1000xxx	26Jun1975 0:00:00.000	30Jun1975 23:59:59.000	30Jun1975 0:00:00.000
1000xxx	06Jun2006 0:00:00.000	30Jun2006 23:59:59.000	30Jun2006 0:00:00.000
1000xxx	06Jun2006 0:00:00.000	30Jun2006 23:59:59.000	30Jun2006 0:00:00.000
1000xxx	22May2006 0:00:00.000	31May2006 23:59:59.000	31May2006 0:00:00.000
1000xxx	22May2006 0:00:00.000	31May2006 23:59:59.000	31May2006 0:00:00.000
1000xxx	23Apr2007 0:00:00.000	30Apr2007 23:59:59.000	30Apr2007 0:00:00.000

[Kurt_Bremser]

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 · Accepted solution]

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]

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

[ballardw]

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]

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]

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