12-01-2017 03:59 PM
I am using the Create Table transformation in DI Studio to extract data from an Oracle table to a SAS Data set.
I have an Oracle DATE data type which I want stored in a SAS data set as a SAS date so that I can use the field in Visual Analytics as a normal SAS date variable for analytics/reports.
Because I am using some User Defined Formats in the transformation sql I have to use Explicit Pass-Through.
My current understanding is that I would need to use the Oracle To_Char(<date>) function to load the oracle date field in a SAS data set as a Character format since there is not DATE type in SAS.
However I don't think that would allow me to use that variable as a date in VA to for instance filter the table for all dates in the last 30 days.
Am I missing something here? Is there a method for storing an oracle DATE field as a SAS date if the DATE field is extracted using pass-through sql?
12-01-2017 08:50 PM - edited 12-01-2017 08:53 PM
SAS uses the numerical data type to store date and datetime values. A date value is the count of days since 1/1/1960, a datetime value is the count of seconds since 1/1/1960.
You then use date or datetime formats in SAS to make these counts human readable as dates or datetimes.
The Oracle DATE field has a precision down to the second. If you load such an Oracle DATE field into SAS then the SAS/Access engine for Oracle will convert this Oracle DATE to a SAS DateTime value.
You can either use this SAS DateTime value for loading into VA or you can convert this value to a SAS Date value using the Datepart() function. If you convert to a SAS Date then also change the permanent format applied to the variable to something like DATE9. by using a FORMAT statement.
Here the relevant docu link: