BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yurie
Fluorite | Level 6

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.  

1 ACCEPTED SOLUTION

Accepted Solutions
carlosmirandad
Obsidian | Level 7

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

4 REPLIES 4
Reeza
Super User

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.

Yurie
Fluorite | Level 6

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;

carlosmirandad
Obsidian | Level 7

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.

 

 

Yurie
Fluorite | Level 6

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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