topic Re: Invalid date/time/datetime constant in SAS Programming

Invalid date/time/datetime constant

Babloo — Wed, 07 Nov 2018 11:52:11 GMT

Appreciate if someone of you guide me to resolve the error? INDDBG and INDDEI is a date fields and if it is missing I need to replace the missing values with '0001-01-01'd and '9999-12-31'd

106        			(case when INDDBG=.  then '0001-01-01'd
ERROR: Invalid date/time/datetime constant '0001-01-01'd.
107                    else INDDBG
108                    end) as INDDBG length = 8
109                    format = YYMMDD10.,
110                 (case when INDDEI=.  then '9999-12-31'd
ERROR: Invalid date/time/datetime constant '9999-12-31'd.
111                    else INDDEI

Re: Invalid date/time/datetime constant

Kurt_Bremser — Wed, 07 Nov 2018 12:21:30 GMT

You already know the correct format for a date literal, see here: https://communities.sas.com/t5/SAS-Programming/Extracting-date-from-day-month-year/td-p/497684

Re: Invalid date/time/datetime constant

Jagadishkatam — Wed, 07 Nov 2018 12:31:00 GMT

please try

case when INDDBG=.  then input('0001-01-01',yymmdd10.)
                  else INDDBG
                   end) as INDDBG length = 8
                   format = YYMMDD10.,
                (case when INDDEI=.  then input('9999-12-31',yymmdd10.)
                  else INDDEI

Re: Invalid date/time/datetime constant

kevinjoseph — Wed, 07 Nov 2018 12:35:56 GMT

data caperfy11mo01_07;

set caper.caperfy11mo01_12;

where ('08/31/2010'd < encdate1 < '03/01/2011'd);

run;

Re: Invalid date/time/datetime constant

Babloo — Wed, 07 Nov 2018 12:37:26 GMT

I tried your code, but the value for INDDBG appearing as '2001-01-01' instead of '0001-01-01'. What might be the likely cause for this issue?

Re: Invalid date/time/datetime constant

Jagadishkatam — Wed, 07 Nov 2018 12:40:12 GMT

likely there is no such year as 0001 so it is interpreting as 2001

Re: Invalid date/time/datetime constant

ballardw — Wed, 07 Nov 2018 15:35:56 GMT

@Babloo wrote:

I tried your code, but the value for INDDBG appearing as '2001-01-01' instead of '0001-01-01'. What might be the likely cause for this issue?

The earliest YEAR that SAS supports is 1581, the year England accepted the Gregorian calendar reforms IIRC. So anything referencing a year prior to that is going to have problems.

What data do you have that is using years prior to 1581?

Prior to that many more countries than current had somewhat different calendars for a number of historical reasons.

Re: Invalid date/time/datetime constant

PaigeMiller — Wed, 07 Nov 2018 16:10:03 GMT

So anything referencing a year prior to that ~~is going to have problems.~~

will not work.

But yes, it's hard to imagine anyone having data (other than perhaps from astronomy) that needs dates earlier than 1583.

Re: Invalid date/time/datetime constant

Kurt_Bremser — Wed, 07 Nov 2018 16:21:58 GMT

Databases often use 0001-01-01 as "default date" when a date column has a "not null" constraint.

Re: Invalid date/time/datetime constant

Peter_C — Wed, 07 Nov 2018 19:52:12 GMT

The only solution I could imagine to display such a date as 0001-01-01 needs a user defined FORMAT , INFORMAT
Keep the coding meaningful with
StoredValue = input( '0001-01-01', db0date. ) ;
Format storedValue db0date10. ;

Create these formats with
PROC format library= work ;
Value db0date .D = '0001-01-01'
Other = [ yymmddd10. ]
;
InValue db0date '0001-01-01' = .D
Other = [ yymmdd. ]
;
Run;

The policy I recommend is to store known artificial dates as a special missing value ( .D in my example above)