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

Reply
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 ]

 @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&docsetV...

Ask a Question
Discussion stats
  • 1 reply
  • 92 views
  • 0 likes
  • 2 in conversation