SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

DI Studio Explicit pass-through convert oracle DATE type to SAS DATE

Occasional Contributor
Posts: 11

DI Studio Explicit pass-through convert oracle DATE type to SAS DATE

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?







Respected Advisor
Posts: 4,185

Re: DI Studio Explicit pass-through convert oracle DATE type to SAS DATE

[ Edited ]


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:

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation