topic Re: Determine if a variable contains a SAS date value (part 2) in SAS Programming

Determine if a variable contains a SAS date value (part 2)

Patrick — Sun, 08 Feb 2015 06:11:37 GMT

Hi all

This is a follow up from where I was looking for a programmatic way to determine if a format is time, date or datetime related. Apparently there is no hidden gem in the SAS provided software to easily get such an answer.

Attached is some code which creates such a format grouping for Gregorian time, date & datetime formats. If this is something of interest to someone else then what I would like to get is some feedback/quality checking if I've got this right or if there are formats in the wrong group (only SAS provided formats).

Re: Determine if a variable contains a SAS date value (part 2)

Ksharp — Sun, 08 Feb 2015 07:25:24 GMT

Patrick,

Well Done. Hope one day I could use it . But I still encourage you to ask SAS to build functions isDate() isDateTime() isTime() , that would be very convenient for SAS users. Because You also need to consider another scenario : someone create a new Date format by proc format + picture , what you are going to do ?

Xia Keshan

Re: Determine if a variable contains a SAS date value (part 2)

Patrick — Sun, 08 Feb 2015 08:57:17 GMT

Hi Xia

I might propose some function like SASfmtgroup() and hope you're going then to vote this up.

I don't have an idea of how to deal with user implemented formats so that it's always going to work.

What I need this format grouping for:

I have to implement a process which takes a SAS table as input and writes an XML as output. The XML has pre-defined output formats for time, date & datetime values. I'm happy if can fully automate for SAS provided formats and then provide an input parameter where a user can define additional variables as containing time, date or datetime values.

Re: Determine if a variable contains a SAS date value (part 2)

Ksharp — Sun, 08 Feb 2015 09:06:39 GMT

"I might propose some function like SASfmtgroup() and hope you're going then to vote this up."

I don't know if I would vote.

"I don't have an idea of how to deal with user implemented formats so that it's always going to work."

I believe SAS has such ability .

Re: Determine if a variable contains a SAS date value (part 2)

Patrick — Sun, 08 Feb 2015 09:32:07 GMT

"I believe SAS has such ability"

How? Can you please explain?

The only idea I had so far was to apply the user written format and then test if I can read the string using SAS informats. But that's certainly not going to work in all cases.

Re: Determine if a variable contains a SAS date value (part 2)

Ksharp — Sun, 08 Feb 2015 09:35:57 GMT

I mean the people who born the SAS . i.e. the developers who build the SAS .

Re: Determine if a variable contains a SAS date value (part 2)

Patrick — Sun, 08 Feb 2015 11:07:10 GMT

SAS Data step 2 (Proc DS2) has more data types.

With "standard" SAS it could be quite a daunting task to come up with a 100% solution for such a format grouping. With picture formats there should be a way to determine the groups via the datatype control attribute (if only one type used in a format) - but one can also use a function as part of a format definition and I don't see how this could be covered (even by SAS R&D).

Re: Determine if a variable contains a SAS date value (part 2)

jakarman — Sun, 08 Feb 2015 16:13:54 GMT

Time is a measurement. It is impossible to get back just from some digits to what the measurement is about and what the unit are.

liters gallons kilometer inches knots m/s kilos mol (etc.). To make it worse with time measurements it is the most badly disturbed one by social history.

Re: Determine if a variable contains a SAS date value (part 2)

Reeza — Thu, 12 Feb 2015 02:19:28 GMT

The code from the Characterize task in SAS UE is interesting. It looks like it creates macro variables of the most commonly used date/time formats and uses that.

Re: Determine if a variable contains a SAS date value (part 2)

Patrick — Thu, 12 Feb 2015 09:51:24 GMT

Thank you for this link. Really interesting that even in SAS Studio some developer needed to specifically code for these formats.

Where it also would be VERY helpful imho to have such "date format detection" deeper integrated into the SAS language would be for the SAS/Access engines so that "SASDATEFMT" and "DBSASTYPE" would only be required for user defined formats.

I'm designing right now for such a case with at least 30 date related SAS variables using ISO formats to be loaded into Oracle.

Re: Determine if a variable contains a SAS date value (part 2)

Reeza — Thu, 12 Feb 2015 09:57:07 GMT

I think your or someone else's suggestion of a format category or vtypecategory function is valid, and would upvote such an idea.

If I could find it among all the spam.

Re: Determine if a variable contains a SAS date value (part 2)

Patrick — Thu, 12 Feb 2015 10:01:41 GMT

LOL - I'll wait a few days then before I post such an idea.

Re: Determine if a variable contains a SAS date value (part 2)

Patrick — Thu, 12 Feb 2015 10:15:14 GMT

The "only thing" I would like is to get access to some SAS implemented format grouping for SAS provided formats. I agree that a date format applied to a numeric SAS variable doesn't necessarily mean that a SAS date value is stored in this variable. I would be more than happy though for SAS to work/behave with such an assumption so that I would only have to code for the very special cases where this is not true (can't think of any though).

Re: Determine if a variable contains a SAS date value (part 2)

jakarman — Fri, 13 Feb 2015 08:24:34 GMT

Patrick the documentation SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition is organized by categories.

The SAS/ACCES module does not mention an Oracel ISO datetime type there are only Oracle internal timestamps. Looking at Oracle TO_DSINTERVAL there is a conversion form internal timestamp to an ISO format (character string) vice versa. As you are saying to convert internal to ISO on both sides I am seeing some unnecessary complication with that approach. Of course you could make the design statement that no advance oought interfaces may be used only data exchange by the means of text-files am uild it all by yourself. But what is the business case of this.

Re: Determine if a variable contains a SAS date value (part 2)

Patrick — Sat, 14 Feb 2015 01:28:24 GMT

Yes, I know that the documentation categorizes formats - but this information is nowhere accessible from within a SAS program and it appears it is also not fully used by the SAS/Access engines. So if we need some automatic detection if a SAS variable contains a date, time or datetime value (derived from the format applied to this SAS variable) then we need to code for this - and it appears that such coding has even been done within SAS Studio as 's example demonstrates.

In regards of the SAS/Access engine to Oracle have a look into below example and what goes wrong there. I know how to solve it but it requires explicit coding (using options "SASDATEFMT" and "DBSASTYPE"). What I would like is that the Access engine would detect this automatically based on formats and convert them. I understand that SAS couldn't just change defaults - but something like an option for extended format recognition would serve the purpose as well.

Code

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

data work.sastable;

format d_date9 date9. dt_datetime20 datetime20.;

format d_b8601da b8601da. dt_b8601dt b8601dt. dt_datetime21 datetime21.;

d_date9=date();d_b8601da=date();

dt_datetime20=datetime();dt_b8601dt=datetime();dt_datetime21=datetime();

output;

stop;

run;

proc append base=oradb.oratable data=work.sastable;

run;quit;

proc append base=oradb.oratable data=work.sastable;

run;quit;

And here the log

6 options sastrace=',,,d' sastraceloc=saslog nostsuffix;

28 data work.sastable;

29 format d_date9 date9. dt_datetime20 datetime20.;

30 format d_b8601da b8601da. dt_b8601dt b8601dt. dt_datetime21 datetime21.;

31 d_date9=date();d_b8601da=date();

32 dt_datetime20=datetime();dt_b8601dt=datetime();dt_datetime21=datetime();

33 output;

34 stop;

35 run;

NOTE: The data set WORK.SASTABLE has 1 observations and 5 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

ORACLE_9: Prepared: on connection 2

SELECT * FROM oradb.ORATABLE

ORACLE_10: Prepared: on connection 2

SELECT * FROM oradb.ORATABLE

37 proc append base=oradb.oratable data=work.sastable;

38 run;

NOTE: Appending WORK.SASTABLE to ORADB.ORATABLE.

NOTE: BASE data set does not exist. DATA file is being copied to BASE file.

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.

ORACLE_11: Executed: on connection 3

CREATE TABLE oradb.ORATABLE(d_date9 DATE,dt_datetime20 DATE,d_b8601da DATE,dt_b8601dt DATE,dt_datetime21 DATE)

ORACLE_12: Prepared: on connection 3

INSERT INTO oradb.ORATABLE (d_date9,dt_datetime20,d_b8601da,dt_b8601dt,dt_datetime21) VALUES

(TO_DATE(:d_date9,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),TO_DATE(:dt_datetime20,'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American'),TO_DATE(:d_b8601da,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),TO_DATE(:dt_b8601dt,'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUA

GE=American'),TO_DATE(:dt_datetime21,'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American'))

NOTE: There were 1 observations read from the data set WORK.SASTABLE.

NOTE: The data set ORADB.ORATABLE has 1 observations and 5 variables.

ORACLE_13: Executed: on connection 3

INSERT statement ORACLE_12

ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*

NOTE: PROCEDURE APPEND used (Total process time):

real time 1.91 seconds

cpu time 0.03 seconds

38 ! quit;

ORACLE_14: Prepared: on connection 2

SELECT * FROM oradb.ORATABLE

ORACLE_15: Prepared: on connection 3

SELECT * FROM oradb.ORATABLE

40 proc append base=oradb.oratable data=work.sastable;

41 run;

NOTE: Appending WORK.SASTABLE to ORADB.ORATABLE.

WARNING: Variable d_date9 has format 'DATETIME20.'n on the BASE data set and format 'DATE9.'n on the DATA data set. 'DATETIME20.'n used.

WARNING: Variable d_b8601da has format 'DATETIME20.'n on the BASE data set and format 'B8601DA.'n on the DATA data set. 'DATETIME20.'n used.

WARNING: Variable dt_b8601dt has format 'DATETIME20.'n on the BASE data set and format 'B8601DT.'n on the DATA data set. 'DATETIME20.'n used.

WARNING: Variable dt_datetime21 has format 'DATETIME20.'n on the BASE data set and format 'DATETIME21.'n on the DATA data set. 'DATETIME20.'n used.

ORACLE_16: Prepared: on connection 3

INSERT INTO oradb.ORATABLE ("D_DATE9","DT_DATETIME20","D_B8601DA","DT_B8601DT","DT_DATETIME21") VALUES

(TO_DATE(:"D_DATE9",'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"DT_DATETIME20",'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"D_B8601DA",'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"DT_B8601DT",'DDMONYYY

Y:HH24:MI:SS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"DT_DATETIME21",'DDMONYYYY:HH24:MI:SS','NLS_DATE_LANGUAGE=American'))

NOTE: There were 1 observations read from the data set WORK.SASTABLE.

NOTE: 1 observations added.

NOTE: The data set ORADB.ORATABLE has . observations and 5 variables.

ORACLE_17: Executed: on connection 3

INSERT statement ORACLE_16

ORACLE: *-*-*-*-*-*-* COMMIT *-*-*-*-*-*-*

NOTE: PROCEDURE APPEND used (Total process time):

real time 1.88 seconds

cpu time 0.02 seconds

41 ! quit;

And in Oracle

Re: Determine if a variable contains a SAS date value (part 2)

jakarman — Sat, 14 Feb 2015 12:06:47 GMT

Patrick, ok that is showing very well a lot of issues around date/datetime and the SAS/ACCESS interface translation.

You probably also knows that storing dates times and datetime is a crime as there is no standard. The result of that is every system is using his own standard, SAS standards, Oracle Standards, Teradata Standards, Excel standards, Sql Server standards and many more. Even some mistakes with one supplier can be made, see: https://communities.sas.com/message/244963 (31dec9999 being different in SAS VA or SAS Base must be an error)

That is having the consequence of needing a conversion when data is going from one to the other. The format usage as convention is an approach avoiding mentioning that conversion.

I agree that format convention is having a lot of pitfalls loopholes causing errors mistakes and confusing.

I agree as that could be an improvement for SAS to be done with high prio.

Going to your code.

1/ I am missing the ora table creation it must be the ora_11 one alle being created as date type in Oracle. Automaticly created.

Datetime Datatypes and Time Zone Support (oracle) is telling Oracle does only have date types as known wit SAS datetime (yep confusing)

2/ That ora_12 statement is a surprise.

- I would expect a bulkload approach instead it is executing a single insert (bad for performance)

- There are constants generated to Oracle. It did recognize SAS date() as Today and translated that as some constant. TO_DATE (oracle)

It will put correct Oracle values into Oracle as your first observation.

There must be something with: SAS(R) 9.4 SQL Procedure User's Guide (SQLCONSTDATETIME) . As for me I do not see the trigger changing obs values to constants

3/ Adding those values out of SAS to the Oracle is going wrong (Oracle only having the equivalent of SAS datetime and not SAS date types)
Showing the SAS datetime-vales of the original SAS-Date ones are those zero-point SAS date values in your second row.

Needing a overwrite for the conversion (you have those errors) you could use DBTYPE DBSASTYPE.

An other one is: SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition (SASDATEFMT) The related examples are Oracle/Teradata

The goal is influencing the conversion! Formats labels etc. that are SAS specific are not stored into a RDBMS. That is getting lost as information.

Re: Determine if a variable contains a SAS date value (part 2)

art297 — Sun, 15 Feb 2015 19:23:13 GMT

Patrick: In addition to suggesting it to SAS, I think you should create a page on sasCommunity.org so that even more people can improve on the code.

The attached file is a slight modification of your original code, namely not creating and submitting an include block, rearranging ing the if/then/else conditions so that time is the last category checked, and adding some code so that user defined picture formats are included.