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;
... View more