Using SAS to send information to an Oracle Table

[alepage]

Hello, 

usually, I connecting to an existing Oracle table to import the Data into a SAS Dataset.

Now, I need to create an Oracle table (specific Database, specific Schema) then send into it, the content of a SAS dataset into it.

However, I could create the Oracle table with it's structure using Toad, then using SAS to send the data into it.

Or I can use SAS to create the Oracle Table with it's stucture then send the data into it.

As I never did this task, Could it be possible to know which one is the best approach and could it be possible to provide code example.  You can use the sashelp.class as data source for example.

Regards,

[Tom]

Should not matter.

You can let SAS define the table/variables.

libname myora oracle .... ;
proc append base=myora.dbtable data=sastable;
run;

Or do it your self

libname myora oracle .... ;
proc sql;
  connect using myora;
  execute by myora
( create table ...... oracle code here .... )
  ;
quit;

proc append base=myora.dbtable data=sastable;
run;

[Patrick]

Using Proc Append like in @Tom's first sample will not only load the DB table but also create it if it doesn't exist already.

I normally prefer to create database tables explicitly using database specific syntax (explicit pass-through SQL) - which is what @Tom's 2nd sample code does.  

If you're using implicit SQL (=let SAS translate it to the DB SQL syntax) then using the following options will show in the SAS log what SQL SAS actually sends to the DB.

options sastrace=',,;d' sastraceloc=saslog nostsuffix;

Should you ever have issues with code that SAS sends to the DB (syntax or performance) then read in the docu how to "configure" option sastrace="...." to get more debug info into your SAS log.

[alepage]

Hello,

This append proc works very well

libname myora oracle .... ;
proc append base=myora.dbtable data=sastable;
run;

But into my SAS dataset, the startdate, enddate, locationlatitude, locationlongitude, are in varchar2 format while the two first should be date, and the two last num(10,6).

Where do we make the conversion into the SAS dataset or in the Oracle Table

and how

Please provide a sas snippet

[Tom]

---

@alepage wrote:

Hello,

This append proc works very well

libname myora oracle .... ;
proc append base=myora.dbtable data=sastable;
run;

But into my SAS dataset, the startdate, enddate, locationlatitude, locationlongitude, are in varchar2 format while the two first should be date, and the two last num(10,6).

Where do we make the conversion into the SAS dataset or in the Oracle Table

and how

Please provide a sas snippet

 That does not seem likely if the variables where numeric in SAS they should be numeric in Oracle.  They might be floating point instead of fixed decimal place numbers just because SAS only has floating point numbers.  And if the date variables really are numeric with date values (number of days) and have a date type display format attached (like DATE9.) then SAS should have created a date variable in Oracle.  Although last time I used Oracle it only supported DATETYPE variables (number of seconds) and not DATE variables (number of days).

Try using the DBTYPE= dataset option to tell SAS what type of Oracle variable should be created for the variable coming from SAS.  When going the other way (reading from Oracle) you can use the DBSASTYPE= dataset option to tell SAS what type of SAS variable to create from the Oracle variable.

[alepage]

One last Question.

As I have mentioned previously, I have little experience with data transfert from SAS dataset to an Oracle DB.

This script below works very well

proc append base=oracle.temp data=SurveyResponse;
run;

But when I run this script twice, we see duplicate records into the Oracle table.

How do we update an existing Oracle table.

Because When we execute this append command the first time it create the oracle table and transfer the content of the SAS dataset into it.

So which command do we use to transfer only the new information (delta load) into the Oracle table.

Thanks for you help which is very appreciated.

[Patrick]

As the name indicates Proc Append appends (inserts) data to a table. If you run it twice you've got duplicates.

One of the reasons I prefer to create tables in databases explicitly via code is to also add Primary Key constraints and potentially indexes. If you've got a primary key constraint defined on the table then Oracle will reject the records (and return an error) in case you're trying to insert a record twice.

As for updating a table:

Using Proc SQL how would you do this if your target would be a SAS table? It's pretty much the same with a target table in a database.

One of the additional things you need to consider when interfacing with a database is data movement.

For example for a SQL update you need to write SQL that compares your source data with your target data and only updates rows in target where there is a change. If your source and target table are not in the same location (like both in Oracle) then such a comparison could cause a lot of data movement over the network with a huge impact on performance.

The way such loads are normally done: 

1. Load your source SAS table into an empty DB staging table (potentially a temporary table).
   1. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0irpkyp22l7vzn1il9lx6f4wmx9.htm 
2. Write explicit pass-through SQL (=SQL in the DB flavor that SAS just send to the DB for execution) that does the update or insert.
   1. With databases like Oracle there is also a MERGE statement that allows you to combine Insert and Update in a single SQL.
      https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/MERGE.html#GUID-5692CCB7-24D9-4C...