topic DI Studio Explicit pass-through convert oracle DATE type to SAS DATE in SAS Data Management

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

gsmith — Fri, 01 Dec 2017 20:59:12 GMT

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?

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

Patrick — Sat, 02 Dec 2017 01:53:45 GMT

@gsmith

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:

http://documentation.sas.com/?docsetId=acreldb&docsetTarget=p06jk0u30uhuj5n18fqw9sxr25lk.htm&docsetVersion=9.4&locale=en