Using a SAS dataset and SQL table in PROC SQL

If you are reading this it is because you have written a PROC SQL statement where you reference both a dataset that you created in an earlier step and a table that resides on an RDBMS (such as SQL Server, MySQL, Teradata, etc . .  .).

Well before you go any further I have to tell you that so far it is not possible to combine the results of a SAS data set and an DBMS table within one PROC-SQL-CREATE-TABLE-AS statement.

For example with the following code I would get an ODBC error saying that "object LIBRARY.SASDATASET1 is not an object in RDBMS ..."

PROC SQL;

CONNECT TO ODBC AS DSNNAME(DATASRC=DSN USER=USERNAME PASSWORD=PASSWORD);

CREATE TABLE LIBRARY.SASDATASET1 AS

SELECT * FROM CONNECTION TO DSNNAME

(SELECT ID, NAME, SSN FROM SQLDB.TABLE_EMP_ID);

CREATE TABLE LIBRARY.SASDATASET2 AS

SELECT * FROM CONNECTION TO DSNNAME

(SELECT ID, JOB_TITLE, SALARY

 FROM SQLDB.TABLE_EMP_SALARY SAL,

      LIBRARY.SASDATASET1 SDS

 WHERE SAL.ID = SDS.ID)

 DISCONNECT FROM DSNNAME;

 QUIT;

What you will have to do instead is make a copy of the SASDATASET1 to your SQL database (Teradata, MySQL, SQLServer, etc…) and then rewrite the second create table statement with your join pointing not to the SAS dataset but rather the table that was created from your first dataset.

PROC SQL;

CONNECT TO ODBC AS DSNNAME(DATASRC=DSN USER=USERNAME PASSWORD=PASSWORD);

CREATE TABLE LIBRARY.SASDATASET1 AS

SELECT * FROM CONNECTION TO DSNNAME

(SELECT ID, NAME, SSN FROM SQLDB.TABLE_EMP_ID);

CREATE TABLE LIBRARY.SASDATASET2 AS

SELECT * FROM CONNECTION TO DSNNAME

(SELECT ID, JOB_TITLE, SALARY

 FROM SQLDB.TABLE_EMP_SALARY SAL,

      LIBRARY.SASDATASET1 SDS

 WHERE SAL.ID = SDS.ID)

 DISCONNECT FROM DSNNAME;

 QUIT;

 PROC SQL;

CONNECT TO ODBC AS DSNNAME(DATASRC=DSN USER=USERNAME PASSWORD=PASSWORD);

CREATE TABLE LIBRARY.SASDATASET1 AS

SELECT * FROM CONNECTION TO DSNNAME

(SELECT ID, NAME, SSN FROM SQLDB.TABLE_EMP_ID);

QUIT;

PROC COPY;

IN=LIBRARY OUT=ODBCLIB MTYPE=(DATA);

SELECT SASDATASET1;

QUIT;

PROC SQL;

CREATE TABLE LIBRARY.SASDATASET2 AS

SELECT * FROM CONNECTION TO DSNNAME

(SELECT ID, JOB_TITLE, SALARY

 FROM SQLDB.TABLE_EMP_SALARY SAL,

      SQLDB.SASDATASET1 SDS

 WHERE SAL.ID = SDS.ID)

 DISCONNECT FROM DSNNAME;

 QUIT;

Questions?  Please post a comment.