Invalid date/time/datetime constant

[Babloo]

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

[Kurt_Bremser]

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

[Jagadishkatam]

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

[kevinjoseph]

data caperfy11mo01_07;

set caper.caperfy11mo01_12;

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

run;

[Babloo]

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?

[Jagadishkatam]

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

[ballardw]

---

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

[PaigeMiller]

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.

[Kurt_Bremser]

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

[Peter_C · Accepted solution]

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)