Writing from SAS DI to Oracle: defining oracle data type for columns

[strsljen]

Hi,

When SAS writes data to Oracle table, it uses only two data types by default:
- varchar(255) for character columns

- binary_double for numeric columns

I need better tuning for some of the columns.

For example, I need number type in Oracle table instead binary_double for specific column.

I tried to play with Format in DI job what writes back to Oracle the result, but I always end-up with binary_double, no matter what I do.

Is there a way to specify that explicitely in SAS DI and how?

Thanks!

[Patrick]

@strsljen

You're of course right that there is no 1:1 match between SAS data types and Oracle data types. If you let SAS implicitly create an Oracle table then it will not only use the SAS data type of numeric or character but also the length of the SAS character variable and the format on SAS numeric variables (i.e. date9.) to determine the "best" Oracle data type.

How SAS implicitly converts variables to Oracle is documented here: 

http://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=p06jk0u30uhuj5n18fqw9sxr25lk.htm&docs...

If you want to overwrite the default behavior for such data type conversion then use option DBTYPE.

http://go.documentation.sas.com/?docsetId=acreldb&docsetTarget=p1dqaq7ub1cm1pn1ow3x9qkmkxe8.htm&docs...

Having said all that: If interfacing with any database I'd consider it good practice to not have SAS implicitly create database tables but to create such tables upfront in the DB - either directly via DDL within the database or then via Create Table statements out of SAS BUT run via EXPLICIT PASS THROUGH SQL

Using DIS: That's then no more SAS metadata driven but a once off job using user written code. You still can implement such user written code as DIS job. Once you've created the Oracle tables you then register them in SAS Metadata so you can use the table objects in your other DIS jobs. Eventually also add this table metadata objects in your once off job to your user written code node as output so you get full data lineage withing DIS/SAS Metadata.

[strsljen]

Hi Patrick,

First of all, thank you for explanation and useful links.

Here is the situation: after SAS DI job I end up with result tables with 100+ attributes.
Attribute definition and number can (and will) vary in near future.

Having said that, manually writing DDL statemets would become major pain point and every change (and there will be changes) would take too much time and nerves.

So.. I prefer having DI doing all the work. Atm, I use no user written code, but SAS transformations.

Here, a small example:

option DBIDIRECTEXEC;

/*---- Map the columns ----*/ 
proc datasets lib = BUSDMSAN nolist nowarn memtype = (data view);
delete test04;
quit;

%put %str(NOTE: Mapping columns ...);
proc sql;
create table BUSDMSAN.test04 as
select
PERIOD_ID
from &SYSLAST
;
quit;

So, the question is, where to write overrides of attribute PERIOD_ID definition in order to become Oracle type NUMBER and not BINARY_DOUBLE.

Can you, please, point it on this example?

Thanks!

Best regards,

[Patrick · Accepted solution]

@strsljen

From the docu link I've posted already:

First thing you should be trying is to assign a numeric format of w.d to all of your numerical variables. If I interpret the docu right then this should allow SAS to determine the scale for Oracle data type NUMBER(p,s) and the access engine will create the Number type by default.

If you want to control data type conversion for a specific variable then that's about using dataset option DBTYPE=. That's something you need to define as data set option on the table metadata object.

Now.... On a conceptual level ETL processes and databases require stable table structures. There is of course always the odd exception but if you have a process with constantly changing table structures then you need to reassess if you're doing the right thing and if using DIS (the SAS ETL tool) is the right tool for the job and a database like Oracle the right place to store such data.

What are you doing with such a constantly changing table structure? Do you have a job which then also always updates SAS Metadata or you just don't register the table? And if you don't register the table then that means it can only be output of your ETL process but you can't use it anywhere further down the stream  in your ETL.

[strsljen]

Hi,

Thanks a lot. That really helped!

We are still a bit in a changing and tuning process. When model is rock solid, I will consider building table structure in Oracle and use SAS only to manipulate data within.

For the time being, defining data types in table metadata in SAS is exactly what I need.

Thanks!

Best regards.