Add support for ISO8601 for date, time, and datetime literals in SAS

ScottBass · ‎08-15-2016

If I assume that my RDBMS supports ISO8601 for date, time, and datetime literals (and if it doesn't, it should), then it would be convenient if SAS did the same.

Sample Code:

%let date=%sysfunc(date(),e8601da.);
%let time=%sysfunc(time(),e8601tm.);
%let datetime=%sysfunc(datetime(),e8601dt.);

%put &=date;
%put &=time;
%put &=datetime;

My results as of the time of this post:

27         %put &=date;
DATE=2016-08-16
28         %put &=time;
TIME=10:32:06
29         %put &=datetime;
DATETIME=2016-08-16T10:32:06

I only have SQL Server to test against right now, but these values all "work" for SQL Server:

SELECT
	CAST('2016-08-16' AS DATE)
	,CAST('10:32:06' AS TIME)
	,CAST('2016-08-16T10:32:06' AS DATETIME)

But not for SAS:

data _null_;
   date="&date"d;
   time="&time"t;
   datetime="&datetime"dt;
   put
      date=date9.
      time=time.
      datetime=datetime.
   ;
run;

If SAS supported the ISO8601 standard, this would make the interchange of date, time, and datetime literals easier when querying data between SAS, RDBMS (implicit pass-through) and RDBMS (explicit pass-through).

RW9 · ‎08-16-2016

This is what input/put are for? I suspect you have this request because you are using macro language to create code? That would be the problem to me, Base SAS has many functions and data structures associated with data types and these make working with and processing data far easier to manipulate and convert. However macro code does not, it is a text generator. Here is an examplewhich still creates your three macro variables, but uses base to do it:

data _null_;
   call symputx('date',put(date(),yymmdd10.));
   call symputx('time',put(time(),time5.));
   call symputx('datetime',put(datetime(),e8601dt.));
run;

%put Date=&date. Time=&time. Datetime=&datetime.;

ChrisNZ · ‎08-18-2016

The cast function is [somewhat] equivalent to the input function in SAS.

I suppose that you are asking for an easy way to inerpret any time/date strings.

SAS made this quite easy with the new anydt* informats.

There seems to be a glitch for the date value though, whether using the generic informat anydtdte. or the specific informat e8601da.This looks like a defect.

%let date    =%sysfunc( date()    ,e8601da. );
%let time    =%sysfunc( time()    ,e8601tm. );
%let datetime=%sysfunc( datetime(),e8601dt. );

data T;
  DT=input("&date"    ,anydtdte.);
  TM=input("&time"    ,anydttme.);
  DT=input("&datetime",anydtdtm.);
  putlog "Generic informats" / "&date=" DT date9. / "&time=" TM time. / "&datetime=" DT datetime. /;

  DT=input("&date"    ,e8601da.);
  TM=input("&time"    ,e8601tm.);
  DT=input("&datetime",e8601dt.);
  putlog "Specific informats" / "&date=" DT date9. / "&time=" TM time. / "&datetime=" DT datetime. /;

  DT=input("&date"    ,yymmdd10.);
  putlog "yymmdd informat" /"&date=" DT date9. ;
run;

Generic informats

2016-08-19=*********

10:41:16=10:41:16

2016-08-19T10:41:16=19AUG16:10:41:16

Specific informats

2016-08-19=*********

10:41:16=10:41:16

2016-08-19T10:41:16=19AUG16:10:41:16

yymmdd informat

2016-08-19=19AUG2016

ScottBass · ‎08-26-2016

Nope...

I'm asking SAS to support the ISO8601 standard, a standard not in existence when SAS was founded, such that a date, time, and datetime literal would have the same format, whether I'm working in SAS or an RDBMS (that is ISO8601 compliant)

This would allow me to create one set of macro variables, regardless of whether I'm programming in SAS or explicit RDBMS pass-through.

This would be useful in my SCD2 processing - sometimes I do it in SAS because of it's richer programming language, and sometimes I do it in explicit pass-through when I need the performance of the database.

It's a minor request and wouldn't break existing code. I'm not asking for SAS to abandon the current functionality, only augment it:

Date literal:

'15APR2016'd

'2016-04-15'd

Time literal:

Same

Datetime literal:

'25DEC2016:06:15:30'dt

'2016-12-25T06:15:30'dt

ChrisNZ · ‎08-27-2016

But sas supports the iso format. That's what the sas format e8601da does.
And you use the cast function with the rdbms so the code is not interchangeable.
I don't understand what you want. Is it that the d string suffix accept 2 types of date values in the string (and likewise for the t and dt suffixes)?

ScottBass · ‎11-24-2016

"But sas supports the iso format."

No it doesn't.

What are your results when you run this code?

data _null_;
* Date literal: ;
date='15APR2016'd;
date='2016-04-15'd;
 
* Datetime literal: ;
datetime='25DEC2016:06:15:30'dt;
datetime='2016-12-25T06:15:30'dt;
run;

ChrisNZ · ‎11-24-2016

CAST('2016-08-16' AS DATE) in SQL

is the same as

input ('2016-08-16', e8601da.) or

input ('2016-08-16', anydtdte10.) in SAS

Most RDBMSes support the literal '01jan2016' and SAS supports the literal '01jan2016'd

SQL server also supports the literal '2016-08-16', but Oracle or SAS don't.

So your proposal is that sas should support the literal '2016-08-16'd

This idea was hard to decipher from your first post as you mentioned the cast() function and seemed to want an equivalent function in SAS, which exists.

I agree that it is a good idea. 🙂

Peter_C · ‎05-06-2017

Scott
Looks like you need new constant types defined - because D, T and DT are already defined.
Perhaps the constant-types should imply 8601, for example

'2016-12-25T06:15:30'8601dt

ScottBass · ‎05-10-2017

Hi Peter,

I was hoping the underlying SAS code could derive this from the data format itself.

Perhaps it could borrow some code from the anydtdtm format?

data _null_;
   x="25DEC2017:12:34:56";
   dt=input(x,anydtdtm19.);
   put dt=datetime19.;

   x="2017-12-25 12:34:56";
   dt=input(x,anydtdtm91.);
   put dt=datetime19.;
run;

Which yields:

24         data _null_;
25            x="25DEC2017:12:34:56";
26            dt=input(x,anydtdtm21.);
27            put dt=datetime21.;
28         
29            x="2017-12-25 12:34:56";
30            dt=input(x,anydtdtm21.);
31            put dt=datetime21.;
32         run;

dt=25DEC2017:12:34:56
dt=25DEC2017:12:34:05

Not sure why the 2nd example gets the seconds wrong? But it's certainly trying to convert the string to a date.

And yep, that input format is not documented: https://support.sas.com/documentation/cdl/en/leforinforref/64790/HTML/default/viewer.htm#p1hsn1ji141...

But I was hoping the new and improved d, t, and dt literals would work with SAS or ISO8601 literals, and be able to "do the right thing" based on the input data format.

Peter_C · ‎05-10-2017

ScottOne way to get the seconds wrong - it is possible the default length for your informat is shorter than the length of your string.I resist use of the ANYDTxxx informats especially where the application might not be interactive. I believe there will be places where the default and current value for the ANYdateStyle system option (the one which decided 11/12/16 is in US or UK datestyle) will not be appropriate for the syntax to be compiled. In a "black box" application like batch processing, I would not want unneccessary risk.So, I suggest a new set of constant types.
Still hoping the principle gets endorsed Peter

ChrisNZ · ‎05-12-2017

@Peter_C Looks like you need new constant types defined - because D, T and DT are already defined.

There is no reason that

'2016-12-25'd

or

'20161225'd

and even

'25 DECEMBER 2016'd

should no be as valid a syntax as

'25DEC2016'd

Fully numeric dates only when they have 8 digits and the year first though (thanks to the pervert US syntax 😉 ).