topic Re: How to load a SAS dataset into a Oracle database in SAS Viya 4? in Moving to SAS Viya

How to load a SAS dataset into a Oracle database in SAS Viya 4?

saikrishna786 — Wed, 26 Apr 2023 14:34:10 GMT

Hi,

How to load a sas dataset into oracle database in SAS Viya 4?

Example#1:

I have a sas dataset "Test" with 5 variables, stored in caslibrary SASHELP.

I want to add "Test" dataset to Oracle database with table name OraTest.

Example#2:

I have a sas dataset "Test" with 5 variables, stored in caslibrary SASHELP.

I want to append Oracle database with table name OraTest with "Test" sas dataset. ("OraTest" has same variables as "Test" dataset)

Regards

Sai Krishna

How to load a SAS Dataset to Oracle database in SAS Viya 4.

saikrishna786 — Wed, 26 Apr 2023 14:32:03 GMT

Hi,

How to load a sas dataset into oracle database in SAS Viya 4?

Example#1:

I have a sas dataset "Test" with 5 variables, stored in caslibrary SASHELP.

I want to add "Test" dataset to Oracle database with table name OraTest.

Example#2:

I have a sas dataset "Test" with 5 variables, stored in caslibrary SASHELP.

I want to append Oracle database with table name OraTest with "Test" sas dataset. ("OraTest" has same variables as "Test" dataset)

Regards

Sai Krishna

Re: How to load a SAS dataset into a Oracle database in SAS Viya 4?

LinusH — Thu, 27 Apr 2023 07:04:20 GMT

It should be pretty much the same as in SAS 9.

Assuming that you already have your Oracle data base schema connected throug a libref, you could use a data step, proc sql, proc append and so forth.

Re: How to load a SAS dataset into a Oracle database in SAS Viya 4?

saikrishna786 — Thu, 27 Apr 2023 11:09:17 GMT

Hi, here is the sample code used:
caslib orcaslib desc='Oracle Caslib'
datasource=(srctype='oracle'
username='user1'
password='******'
path="//XX.XX.XX.XXX:XXXX/exadat" );

proc casutil incaslib="GALAXY" outcaslib="GALAXY" ;
save casdata="TEST1" casout="TEST1" replace;
quit;

I am able to fetch the data from oracle but I am not able to load the data into oracle database.

After referring to many articles, i see that we will be able to load either in the form of csv/ort and oracle will consume the csv and convert to a table in respective schema.

Could you send me a sample code if you have tested.

Re: How to load a SAS dataset into a Oracle database in SAS Viya 4?

AhmedAl_Attar — Thu, 27 Apr 2023 11:53:43 GMT

Hi @saikrishna786 ,

I would recommend, if you have not already, to check the SAS on-line docs

SAS Help Center: Oracle Data Connector

SAS Help Center: Where to Specify Data Connector Options

Then read this article Loading SAS data to Amazon Redshift…Don’t run it too quickly! - SAS Support Communities (While it's not directly related to Oracle, it has paragraph related to bulk loading into a Database)

I believe what you need to look into is something similar to this

Proc CASUTIL; 
save casdata="cars" incaslib="tdlib" casout="cars_CAS"
      dataSourceOptions=(schema="tdSchema");
quit;

Hope this helps

Re: How to load a SAS dataset into a Oracle database in SAS Viya 4?

saikrishna786 — Thu, 27 Apr 2023 12:28:31 GMT

Hi @AhmedAl_Attar .. sure will try and check if its working.

Thank you for the response