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).
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
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.
"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 .
"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.
I mean the people who born the SAS . i.e. the developers who build the SAS .
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).
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.
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.
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.
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).
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.
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: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 *-*-*-*-*-*-*
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: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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.