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

[jakarman]

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.

[art297]

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.