DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 32
Accepted Solution

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

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.  


Accepted Solutions
Solution
‎07-06-2016 10:57 AM
Contributor
Posts: 34

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

[ Edited ]

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.

 

 

View solution in original post


All Replies
Super User
Posts: 19,851

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

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.

Contributor
Posts: 32

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

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;

Solution
‎07-06-2016 10:57 AM
Contributor
Posts: 34

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

[ Edited ]

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.

 

 

Contributor
Posts: 32

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

Posted in reply to carlosmirandad

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!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1066 views
  • 3 likes
  • 3 in conversation