PROC SQL to populate temp table with SAS dataset

[stuart753]

Hi,

I want to copy the contents of a SAS dataset into a SQL Server temporary table that I'm creating via a connection.  From my research, I've been able to figure out how to create the temporary table in SQL Server and insert data using the VALUES option.  Below is the code I have that does that successfully:

001 proc sql;

002 /*TAKEN FROM: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002677192.htm*/

003    connect to odbc as dport (dsn=SS_CNX connection=shared);

004    execute (create table #AAATEMP (CustID char(9))) by dport;

005    execute (INSERT INTO #AAATEMP(CustID)

006      VALUES

007      ('20090213A'),

008      ('20090214B'),

009      ('20090215D');

010       ) by dport;

011  quit;

Since I have about 50,000 CustIDs to insert, I would like to just insert the contents of a SAS dataset that resides in my work directory.  Instead of the VALUES option, I'm shooting for a way to replace lines 006-009 above with a SELECT option:

006      SELECT custid FROM work.cust_list;) by dport;

The above approach gives the following error: "CLI execute error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'work.ssn_list'."

From other research, it looked like the following may be another solution to replace lines 005-010 above, however this did not work:

005  data dport.'#AAATEMP'n;

006     set work.cust_list;

007  run;

With this alternative approach, I get an  invalid object name error for the #AAATEMP table.  (I have verified that the #AAATEMP table does exist).

Does anyone have any ideas for inserting the contents of a SAS dataset directly into a SQL Server temp table?

Thanks,

Stuart

[Patrick]

In your research have you also found this link? SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

Referring to above link: I believe you could simply define a libname for temporary tables and then use PROC APPEND to load your table from work into SQL server.

[Reeza]

You're getting the error because your using Explicit SQL Pass through, which passes SQL commands directly to the SQL Server. The SQL server won't have access to your work table.

If you want to operate on both your SQL DB and data in your SAS workspace you'll need to use implicit pass through or connect to it via a libname.

Another option is to store the values in a macro variable and insert them into the DB that way, if the list is smaller than 64K characters.

[stuart753 · Accepted solution]

Reeza & Patrick, thank you both for your help.

From the additional information you provided, I now have a working solution (copied below).  Reeza, it seems as though my solution actually uses both an implicit connection and I'm connecting via a libname.  Can you confirm if I'm doing both? - and if it's necessary to do both, or is there another more efficient solution.

libname  idport   odbc  dsn=SS_DataPort   Connection=Shared;

proc sql;

     CREATE TABLE idport.'#AAATEMP'n (CustID CHAR(9));

    INSERT INTO idport.'#AAATEMP'n  SELECT CustID from work.cust_list;

     SELECT * from idport.'#AAATEMP'n;

Quit;

[Patrick]

You connect via a libname and then use implicit SQL (SQL formulated in the SAS flavor). Have line "options sastrace=',,,d' sastraceloc=saslog nostsuffix;" before the Proc SQL and you can see in the SAS log what that SAS/Access engine does with this code and what gets translated into data base specific code.

Are you sure you're creating a temporary table? According to the documentation (link I've posted) you should also have "dbmstemp=yes" as part of your libname statement.

[stuart753]

Patrick,

That options statement was very helpful as it details all the steps being carried out.  Thank you for that tip.  I believe the table is getting deleted after the 'quit' statement executes.  Prior to that, I can see the temp table in SQL Server.  After the quit statement executes, the temp table is gone.  I added the dbmstemp=YES option to my libname statement in case I use this for other data sources that require it.

When you reference implicit SQL as 'SQL formulated in the SAS flavor', does that mean if I want to use TSQL (SQL Server flavor) I need to use an explicit connection, otherwise with the implicit connection I'm limited to the SAS flavor of SQL?  This may have been outlined in the documentation, however I'm not sure that I completely understood all of the documentation.

I ask because one option I may want for myself in the future is to use SQL Server 2012 window functions - and if I do, I'm thinking I'm going to be limited to using an explicit connection, can you confirm?   (I believe I'm limited to an explicit connection because I did use a window function in the code above that uses an implicit connection and it failed.  Details in the log said the OVER clause was not recognized.)

Again, I appreciate all your insight!

Stuart

[Reeza]

When you reference implicit SQL as 'SQL formulated in the SAS flavor', does that mean if I want to use TSQL (SQL Server flavor) I need to use an explicit connection, otherwise with the implicit connection I'm limited to the SAS flavor of SQL?  

That's correct.

To use Window functions you'll need to use explicit pass through.

[stuart753]

Thank you Reeza for confirming.  I appreciate your help with educating me on this topic!

[Patrick]

From the documentation:

SQL Pass-Through Facility

The SQL pass-through facility uses SAS/ACCESS to connect to a DBMS and to send statements directly to the DBMS for execution. As an alternative to the SAS/ACCESS LIBNAME statement, this facility lets you use the SQL syntax of your DBMS. It supports any SQL that is not ANSI-standard that your DBMS supports.

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition