store SAS table data to oracle database

Jonison — Tue, 28 Jul 2020 17:49:55 GMT

Hello, experts.

I have established a model in enterprise guide, and the model results are stored in SAS table. IT create a table in Oracle space with same structure of model results (col names, length etc are same).

would you please provide some suggestions on how to load the data from SAS table to Oracle table? (truncate existing records in Oracle data table first).

Many thanks.

Re: store SAS table data to oracle database

SASKiwi — Tue, 28 Jul 2020 20:52:04 GMT

I find deleting rows via SQL passthru is efficient:

proc sql;
   connect to Oracle ( <Oracle connection details> ); 
   execute (delete from MyOracleDatabase.MyOracleSchema.MyOracleTable
           ) by Oracle;
   disconnect from Oracle;
quit;

I also find PROC DATASETS APPEND is good for loading data, but you normally need to tweak the INSERTBUFF = option to get good loading performance:

libname MyOracle Oracle <Oracle connection details> insertbuff = 10000;
proc datasets library = MyOracle nolist;
  append base = MyOracleTable data = MySASLib.MySASTable;
run;
quit;

topic Re: store SAS table data to oracle database in SAS Enterprise Guide

store SAS table data to oracle database

Re: store SAS table data to oracle database