topic Re: How to update a column information in an Oracle table by using SAS? in SAS Programming

How to update a column information in an Oracle table by using SAS?

Yurie — Thu, 30 Jun 2016 20:38:05 GMT

Hello, everyone

I have more than 400 tables in my Oracle database, and I need to update a column's information in 5 tables (same column names). For example: I need to change all the type=A in the table to become type=S in the Oracle basebase table (I do not want to change the table's name. ) I can connect the Oracle database with SAS and I have the adm right to change the database.

Original table 1: Updated table 1:

id type id type

1 A 1 S

2 B 2 B

3 A 3 S

4 C 4 C

5 A 5 S

6 C 6 C

I need update the information directly to the Oracle database. What are the set-up shall I have and what steps shall I follow to update my Oracle database. I am very appreciate for any suggestions or hints.

Re: How to update a column information in an Oracle table by using SAS?

Reeza — Thu, 30 Jun 2016 21:14:20 GMT

1. Use SQL pass through to allow the server to do the work

2. Find the correct Oracle SQL code to update your table and use it in the SQL pass through code.

Re: How to update a column information in an Oracle table by using SAS?

Yurie — Fri, 01 Jul 2016 15:25:48 GMT

Good morning, Reeza

Thank you very much for the useful information. I have SQL Server 2014 Management Stutio. However, it looks like I cannot connect with Oracle database with SQL Server 2014 Management Studio. I searched online and it said SQL developer can be used to connect Oracle. I tried the following code in SAS, it works. However, I am not familiar with SQL. Any suggestions? Thanks a lot!

data new_table; /*Create a new table which has the correct types from old table*/
set old_table;
if type="A" then type="S";
else type=type;
run;

PROC SQL; /*use the SQL to update the Oracel old table*/
UPDATE old_table as a
SET type = (SELECT b.type
FROM new_table as b
WHERE a.id=b.id)
WHERE exists
(select * from new_table as b
WHERE a.id=b.id);
QUIT;

Re: How to update a column information in an Oracle table by using SAS?

carlosmirandad — Fri, 01 Jul 2016 21:45:57 GMT

It seems to me that you don't need to download the entire table to a SAS dataset, update it in SAS, and then upload the result back to the database. If the tables are large, this could be inefficent. It may be better to execute a SQL UPDATE command on the database itself. You can run that command either in your database client or from a SAS program with "SQL Pass_Through".

This is how you could do it from SAS:

*I will use an Oracle database.; 
*F655288 is the name of my schema;
*You can change this to your own database and schema;
libname f655288 oracle user=&mkv_user password=&mkv_pw path="@mkview" schema=f655288 connection=sharedread utilconn_transient=yes or_binary_double=no; 

*Creates a table in the database with the contents shown in your example;
data f655288.table1;
	infile datalines;
	input id type $ @@;
datalines;
1 A 2 B 3 A 4 C 5 A 6 C
;
run;

*Runs the updates in the database itself (without downloading the data to SAS);
proc sql;
connect to oracle as ora(user=&mkv_user. password=&mkviewpw. path="@mkview" buffsize=500 preserve_comments);
execute (
	update f655288.table1
	set type='S'
	where type='A'
) by ora;
disconnect from ora;
quit;

Finally, if you want to repeat this produre for multiple tables (e.g. table1, table2, table3, table4, and table5) then you could use a SAS MACRO to make it more efficient.

%macro update_table (tablename);

	proc sql;
	connect to oracle as ora(user=&mkv_user. password=&mkviewpw. path="@mkview" buffsize=500 preserve_comments);
	execute (
		update f655288.&tablename.
		set type='S'
		where type='A'
	) by ora;
	disconnect from ora;
	quit;

%mend;

%update_table(table1);
%update_table(table2);
%update_table(table3);
%update_table(table4);
%update_table(table5);

Let us know if this answers your question or if we missed anything.

Re: How to update a column information in an Oracle table by using SAS?

Yurie — Wed, 06 Jul 2016 15:05:29 GMT

I am so grateful for your and everyone's help here! It's so wonderful! This makes me fall in love with this community now! Blessings!