Imported column may be a date or a datetime

[SyidaRox]

Hello - I have some code that reads an excel file, there are two columns in this file.  One is a customer identification number and the other is a date or a datetime.  I have no control over the contents of the excel file so I need to be able to read the date/datetime field and convert it to a date.  When I use the datepart function and the column is a date it turns the date to 1/1/1960 but works perfectly fine if the column is a datetime value. 

I have searched long and hard for a reasonable solution without success. Is there a way to make the value a date value without compromising the data, regardless of the datatype (date/datetime) of the column?

[gamotte]

Hello,

have you tried the anydtdte informat ?

filename a temp;

data _NULL_;
file a;
put "01Jan2020";
put "05Jan2020:00:00:00";
run;

data a;
infile a;
attrib date format=date9. informat=anydtdte.;
input date;
run;

[andreas_lds · Accepted solution]

@gamotte that would be the perfect solution, if the data would be provided in a reasonable file-format, but @SyidaRox has to process an excel-file.

@SyidaRox: I would define the variable to be a date in the excel-file an reject any file not fulfilling that requirement. Data has to comply with a specification to get reliable results.

The following code checks the variable "maybe_date" and creates a data-step with the date-version of the variable.

proc import datafile="crap.xlsx" out=work.narf dbms=xlsx replace;
   sheet='sheet_name';
run;

data _null_;
   set work.narf(obs=1);

   if index(vformat(maybe_date), 'DATETIME') then do;
      call execute('data work.narf_fixed;');
      call execute('set work.narf(rename=(maybe_date=_datetime));');
      call execute('attrib maybe_date length=8 format=date9.;');
      call execute('maybe_date = datepart(_datetime);');
      call execute('drop _datetime;');
      call execute('run;');
   end;
run;

[SyidaRox]

Worked perfectly. Thank you!

[Kurt_Bremser]

Try this:

proc sql;
select int(max(datevar)) into :maxval from imported;
quit;

%if &maxval > 80000 %then %do;

data imported;
set imported;
format datevar yymmddd10.;
datevar = datepart(datevar);
run;

%end;

A value of 80000 would constitute a date very far into the future, but a datetime still on 1960-01-01, so it's a good cutoff.

[Astounding]

I would simplify @Kurt_Bremser 's solution to be:

data want;
   set have;
   if datevar > '01jan2030'd then datevar = datepart(datevar);
run;

This should also handle cases where the date variable changes row by row, sometimes a date and sometimes a datetime.

[Kurt_Bremser]

The raw value 86400 corresponds to a date of 2196-07-21, so I would use a date in that range as cutoff, to be on the safe side (dates for contract expiration or similar could easily fall beyond 2030).