Working with a customer where we have decided to utilise the current Oracle Data Warehouse as much as possible as the source of data for reporting, given the significant investment in the data warehouse over the last few years.
Rather than extract the Oracle data into SAS we were testing using SAS Information Maps and Web Report Studio over the top of the Oracle Star Schema’s. I have to say I was impressed with the performance of the Web Report Studio reports given the number of layerswe were traversing, i.e
Web Report Studio > Information Map > SAS Workspace Server > Proc Sql >SAS Access to Oracle > Oracle (and back again).
Anyway one issue we found was that we couldn’t apply SAS Date Formats against the Oracle date fields, if we did it would just show ******* instead of a date.
We could use a datepart function to substring the date field and that worked ok, but that could only be applied in the Information Map, not against the Metadata definition which means other tools such as SAS OLAP Cubes and Enterprise Guide etc wouldn’t pick it up.
We tried changing the Metadata definition for datefields from DATETIME20. to say DATE9. , but then our friends ****** would appear. Even worse if we reapplied the DATETIME20. format to the metadata definition it set all the dates to 1960. We had to delete the metadata definition and reimport it to get the correct dates again.
The solution was in the “Usage Note 6413: SASDATEFMT option can be defined globally in the config file or as an environment variable with SAS/ACCESS Interface to Oracle” here:
http://support.sas.com/kb/6/413.html
Although it talks about SAS 8 we applied the following statement to the sasv9.cfg file:
-set SASDATEFMT date
Rebooted the workspace server, deleted and re-imported the Oracle Table Metadata and wallah the Oracle date fields were know registered as Date. and aplpying date formats in the Metadata definition, Information Maps or Enterprise Guide worked the way they should!
As an aside in working through the options we worked out that thwre is a magic number that if you take it away from the numeric Oracle data value you get the numeric SAS date value, whihc makes sense I suppose given they both juts start counting days to manage dates, they just start counting from different points.
0 Comments.