BookmarkSubscribeRSS Feed
gsmith
Obsidian | Level 7

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?

 

 

 

 

 

 

1 REPLY 1
Patrick
Opal | Level 21

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 986 views
  • 0 likes
  • 2 in conversation