Updating external DBMS tables with SAS data using PROC SQL passthrough -- is this possible?

Question

I have a MS Access database. I am required to process and update tables in this database with SAS. The tables can be very large. Some of the processing requires the use of pure SAS functions. With a SAS/ACCESS LIBREF to MS Access, you cannot update or alter tables with PROC SQL. Therefore, I see two approaches:

1. Using LIBREF: Copy the entire table into SAS. Delete the original table in Access. Process the table in SAS. Write the table back to Access.
2. Using PROC SQL passthrough: Read the rows I need into SAS. Process these rows using SAS. Update the rows in Access.

My question: is number 2 even possible? With SQL functions (rather than SAS), the process outlined in number 2 is many orders of magnitude faster than number 1. I understand that this difference comes from the data being processed entirely by the DBMS, but number 2 would still have to be faster, right?

Answer 1

What I'd suggest is option 2, then use LIBREF to upload those rows to a temporary table, then use passthrough to update the bigger table. It may be possible to update the rows directly via libref, but I'm not sufficiently familiar with SAS connection to Access to answer that part.

Answer 2

When using the LIBNAME statement to connect to an Access database, you need to add the option 'scan_text=no' in order to enable updating that databse. You can then use MODIFY to change existing records or APPEND to add new records. Below is some training code I wrote a while ago, this connects to Excel but the principle is the same for Access.

/* Create libname to Excel */
libname xl 'N:\SAS\Code\sas_training\sample_data\range.xls' scan_text=no; /* important to use this option */

/* Create table with updated values */
data xl_update;
input month a b c d;
cards;
200909 1 2 3 4
200910 5 6 7 8
200911 10 11 12 13
run;

/* Extract latest month from existing data */
proc sql noprint;
select max(month) into :maxmth
from xl.testdata;
quit;

/* Update existing values */
data xl.testdata;
modify xl.testdata xl_update (where=(month<=&maxmth.));
by month;
run;

/* Append new rows to exisiting data */
proc append base=xl.testdata 
            data=work.xl_update (where=(month>&maxmth.));
run;

libname xl clear;

Answer 3

For future readers: I asked this question because SAS does not support a PROC SQL UPDATE statement on a MS Access library. To update an Access table, you must create a new table in the database and use a pass-through statement. My first instinct was to use an UPDATE statement assigning with a WHERE clause to update a subset of records. While I was researching this, I read some bad information that said Jet SQL did not support updating a subset of records. This is incorrect. Using an INNER JOIN, you are able to update a subset. Example code below:

/* Access database is "C:/database.mdb".
   Original table in database is "original". 
   Processed table is "work.updates". */

libname mdbfile "C:/database.mdb";

proc sql;
create table mdbfile.updates as select * from work.updates;
connect to access (path="C:/database.mdb");
execute (
  update original as a inner join updates as b on a.id = b.id
  set a.variable1 = b.variable1
  where condition = 1
) by access;
execute (drop table updates) by access;
disconnect from access;
quit;