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)
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.));
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.