BookmarkSubscribeRSS Feed
RichardAD
Quartz | Level 8

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)

1 REPLY 1
PaigeMiller
Diamond | Level 26

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.));

 

--
Paige Miller

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 439 views
  • 1 like
  • 2 in conversation