Extracting date from a character variable that might have partial date or datetime representation

RichardAD — Wed, 19 Mar 2025 18:22:09 GMT

In SQL I would like to

INPUT(datestr,?? ...some informat...) as DATE format DATE11.

Is there a singular informat that can deal with a variety of date representations, some of which may be incomplete ?

Consider sample data and output

options nocenter nodate nonumber;title;

data x;
  attrib 
    datestr length=$30 
    date length=8 format=date11.     label='ANYDTDTE19.'
    dtim length=8 format=datetime19. label='ANYDTDTM19.'
    dtme length=8 format=datetime19. label='E8601DT19.'
  ;
  input 
    @1 datestr $CHAR30. 
    @1 date anydtdte19. 
    @1 dtim anydtdtm19.
    @1 dtme E8601DT19.
  ;
  datalines ;
12/01/2017
12/3/17
1/5/18T12:34:56
1/5/18 12:34:56
2021-12-07
2021-12-07T10
2021-12-07T11:08
2021-12-07T12:37:25
2021-12-08 12
2021-12-08 13:08
2021-12-08 14:37:25
2022
2023-02
20211207
20211207T10
20211207T11:08
20211207T12:37:25
20211208 12
20211208 13:08
20211208 14:37:25
;

proc print label ;
  title "option DATESTYLE=%sysfunc(getoption(DATESTYLE))";
  title2 "date values output with date11. datetime with datetime19." ;
  title3 "column label is the informat used against [datestr]" ;
run;

option DATESTYLE=MDY
date values output with date11. datetime with datetime19.
column label is the informat used against [datestr]

Obs    datestr                ANYDTDTE19.            ANYDTDTM19.             E8601DT19.

  1    12/01/2017             01-DEC-2017     01DEC2017:00:00:00                      .
  2    12/3/17                03-DEC-2017     03DEC2017:00:00:00                      .
  3    1/5/18T12:34:56                  .                      .                      .
  4    1/5/18 12:34:56        05-JAN-2018     05JAN2018:12:34:56                      .
  5    2021-12-07             07-DEC-2021     07DEC2021:00:00:00                      .
  6    2021-12-07T10                    .                      .                      .
  7    2021-12-07T11:08                 .                      .     07DEC2021:11:08:00
  8    2021-12-07T12:37:25    07-DEC-2021     07DEC2021:12:37:25     07DEC2021:12:37:25
  9    2021-12-08 12                    .                      .                      .
 10    2021-12-08 13:08       08-DEC-2021     08DEC2021:13:08:00     08DEC2021:13:08:00
 11    2021-12-08 14:37:25    08-DEC-2021     08DEC2021:14:37:25     08DEC2021:14:37:25
 12    2022                             .                      .                      .
 13    2023-02                01-FEB-2023     01FEB2023:00:00:00                      .
 14    20211207               07-DEC-2021     07DEC2021:00:00:00                      .
 15    20211207T10                      .                      .                      .
 16    20211207T11:08                   .                      .                      .
 17    20211207T12:37:25                .                      .                      .
 18    20211208 12                      .                      .                      .
 19    20211208 13:08         08-DEC-2021     08DEC2021:13:08:00                      .
 20    20211208 14:37:25      08-DEC-2021     08DEC2021:14:37:25                      .

Is there some informat better than ANYDTDTM or ANYDTDTE?

In SQL to use a variety of inputting attempts would COALESCE (input-way-1, input-way-2, ... input-way-n) be plausible (while trying to get cpu use down)

Re: Extracting date from a character variable that might have partial date or datetime representatio

PaigeMiller — Wed, 19 Mar 2025 20:04:52 GMT

The problem caused by the letter T is easy to fix, just replace the letter T with a space (use the TRANSLATE function).

The other problems caused by just having a year, or just having an hour, require you to write your own code to deal with those.

Example:

if length(datestr)=4 and notdigit(datestr)=0 then dateval=mdy(1,1,input(datestr,4.));

topic Extracting date from a character variable that might have partial date or datetime representation in SAS Programming

Extracting date from a character variable that might have partial date or datetime representation

Re: Extracting date from a character variable that might have partial date or datetime representatio