DATA Step, Macro, Functions and more

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

Reply
Respected Advisor
Posts: 4,173

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

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

Attachment
Super User
Posts: 10,046

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

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

Respected Advisor
Posts: 4,173

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

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.

Super User
Posts: 10,046

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

"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 .

Respected Advisor
Posts: 4,173

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

"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.

Super User
Posts: 10,046

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

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

Respected Advisor
Posts: 4,173

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

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

Super User
Posts: 19,877

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

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.

Respected Advisor
Posts: 4,173

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

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.

Super User
Posts: 19,877

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

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.

Respected Advisor
Posts: 4,173

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

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

Trusted Advisor
Posts: 3,215

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

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.

---->-- ja karman --<-----
Respected Advisor
Posts: 4,173

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

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

Trusted Advisor
Posts: 3,215

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

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.  

---->-- ja karman --<-----
Respected Advisor
Posts: 4,173

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

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;

27        

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

     

36        

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:MISmiley FrustratedS','NLS_DATE_LANGUAGE=American'),TO_DATE(:d_b8601da,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),TO_DATE(:dt_b8601dt,'DDMONYYYY:HH24:MISmiley FrustratedS','NLS_DATE_LANGUA

GE=American'),TO_DATE(:dt_datetime21,'DDMONYYYY:HH24:MISmiley FrustratedS','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 *-*-*-*-*-*-*

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

NOTE: PROCEDURE APPEND used (Total process time):

      real time           1.91 seconds

      cpu time            0.03 seconds

     

38       !     quit;

39        

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:MISmiley FrustratedS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"DT_DATETIME20",'DDMONYYYY:HH24:MISmiley FrustratedS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"D_B8601DA",'DDMONYYYY:HH24:MISmiley FrustratedS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"DT_B8601DT",'DDMONYYY

Y:HH24:MISmiley FrustratedS','NLS_DATE_LANGUAGE=American'),TO_DATE(:"DT_DATETIME21",'DDMONYYYY:HH24:MISmiley FrustratedS','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

Capture.PNG

Ask a Question
Discussion stats
  • 16 replies
  • 754 views
  • 5 likes
  • 5 in conversation