Updating an Oracle table with data from a SAS data set using SAS code

Question

Hello all,

I am rather new to SAS and I have run into a problem that I think probably has a better solution than what I've found so far.

I need to update a Oracle db table that has around 1 million rows with data from a SAS data set that has about 10,000 records.

I used an update statement within proc sql, but it takes hours to update the Oracle table. Right now, I am loading the data from the SAS data set into a temporary table in the Oracle db and doing a proc sql pass through execute statement to update the main table from the temporary table. This takes only a couple of minutes at most.

However, this is rather cumbersome to program and and I need to update the Oracle table from multiple functions within my SAS code.

Is there an analog to JDBC batch update in SAS (I uses to do Java programming before getting involved in SAS)? Something that is faster than using an update statement in proc sql, but easier to code than temp table + update using pass through?

Thanks!

Answer 1

Are you using SAS/Access to connect your SAS sessions to Oracle?

In my situation, I use SAS/Connect JDBC.

SAS/Connect is a very simple but effective strategy for interfacing the SAS substrate system to JEE. Essentially sas/connect is yet another telnet implementation by sas to execute sas -dmr.

I draw the sas data out using sas/connect jdbc into my jsp and then push the data into oracle or sql server using java programming techniques we are all familiar with.

Read my ancient paper on using sas/connect to connect sas to JEE: http://www.nesug.org/proceedings/nesug04/ap/ap02.pdf. BTW do not try to contact me with the contacts listed on the paper - they are ancient.

In response to your further statement:

I thought you wanted a way to use JDBC to insert the data into Oracle? My paper shows you how to embed a whole block of SAS macro or SQL or any text in a JSP and then submit that block of text to be run through SAS/Connect.

String datasetname = request.getParameter("datasetname");
String where = request.getParameter("where");

<t:text id="macHello">
%macro hello(datasetname);
 data &datasetname;
 /* code to create your data */
 run;
%mend;

%hello(<%=datasetname%>);
</t:text>

sasConnect.submit(macHello);

<t:text id="SQLgetRecs">
 SELECT *
 FROM <%=datasetname%>
 WHERE <%=where%>
</t:text>

ResultSet mydata =
sasConnJDBC.executeQuery(SQLgetRecs);

Then do whatever you need to do with Java, either by interweaving insertion in Oracle per iteration of Resultset or iterate resultset to produce a text block of SQL insert VALUES which you then submit to Oracle JDBC.

It would just be a single JSP, provided you know how to work a JSP and willing to understand how the text-block tag library I wrote works. You see, I use this technique to allow a JSP run SAS macros that have been running in production batch mode for ages, without any change to the macros. Not only so, the tag lib allows me to embed java and jsp variable resolution into the macros or sas/sql blocks.

I wrote this block-text tag lib because I used to do such operations in Perl (prior to 2003), where Perl (and other scripting languages) allows you to assign a variable to a continuous block of text within the code of the script.

Instructions on using the tag lib:

http://h2g2java.blessedgeek.com/2009/07/jsp-text-custom-tag.html

http://h2g2java.blessedgeek.com/2009/07/referencing-text-jsp-custom-tag-defined.html