Using SQL Pass Through to Join MS-SQL and SAS tables.

[Michael_W]

I am brand new to the whole SQL pass through experience, so please excuse me if I sound inexperienced on the topic.

I am trying to use SQL pass through to replicate a query that was create in MS Access. The query has approx 10 tables joined together; 9 of which are MS-SQL tables and one of which is a SAS table. I am able to create OLEDB connection to one of the SQL tables, read it, and create a SAS table from it. In addition, I believe I have successfully created a temp table in MS-SQL from my SAS table. However, for the life of me, I can't figure out how to join a permanent MS-SQL table to the temp SQL table that just created. In fact, I can't even read the temp table that I believe I have created.

This code appears to work:

libname x oledb init_string="Provider=SQLOLEDB.1; Integrated Security=SSPI ;Initial Catalog=PIMS;Data Source=DSS-SQLPRD04\PSQL01" connection=global;

data x.'#temp1'n;
set WORK.Prov_Benefit_Plan_Mapping;
run;

This code works as well:

option DQUOTE=ANSI;
option sastrace=',,,d' sastraceloc=saslog nostsuffix;

proc sql;

connect to oledb (init_string=" Provider=SQLOLEDB.1;Integrated Security=SSPI ;Initial Catalog=PIMS;Data Source=DSS-SQLPRD04\PSQL01" schema=dbo connection=global);

Create Table work.Test as select * from connection to oledb

(SELECT * FROM Prog);

quit;

However, I can't figure how to join the two:


option DQUOTE=ANSI;
option sastrace=',,,d' sastraceloc=saslog nostsuffix;

proc sql;

connect to oledb (init_string=" Provider=SQLOLEDB.1;Integrated Security=SSPI ;Initial Catalog=PIMS;Data Source=DSS-SQLPRD04\PSQL01" schema=dbo connection=global);

Create Table work.Test as select * from connection to oledb

(SELECT * FROM Prog
INNER JOIN temp1 ON Prog.PROGDESC = Temp1.Prov_BenefitPlan);

quit;

[barheat]

I am not going to pretend that I fully understand your specific problem. I can address the issue of joining SAS tables with tables on other databases (db2, ORACLE, rdb, etc).

The issue is that the remote database server does not recognise a SAS table if a join is being performed on the remote database. This means that all of the indexes in the remote database become useless for the efficiency of the join. For large databases this can mean joins taking exponentially longer times. My solution has been to join what I can within the remote database and then download the data into SAS. From there I perform the joins and filters using just SAS tables.

It seems wrong, but there are times I download 100,000+ records covering a wide time span to match up with a few hundred records in the SAS data table.

[LinusH]

Since it seems thay you have successfully loaded a MS Access table, and use explicit SQL pass-thru, this not really a SAS question?

An alternative approach would be to use the DBKEY= option together with SQL implicit pass-thru (that is using SQL that refers to your MS Access data using a libref). That could work well if your table Prov_Benefit_Plan_Mapping is quite small.

/Linus