topic Re: PROC SQL to populate temp table with SAS dataset in SAS Procedures

PROC SQL to populate temp table with SAS dataset

stuart753 — Tue, 06 Jan 2015 20:37:58 GMT

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

Re: PROC SQL to populate temp table with SAS dataset

Patrick — Tue, 06 Jan 2015 21:12:46 GMT

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.

Re: PROC SQL to populate temp table with SAS dataset

Reeza — Tue, 06 Jan 2015 21:32:11 GMT

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.

Re: PROC SQL to populate temp table with SAS dataset

stuart753 — Wed, 07 Jan 2015 03:28:20 GMT

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;

Re: PROC SQL to populate temp table with SAS dataset

Patrick — Wed, 07 Jan 2015 03:54:25 GMT

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.

Re: PROC SQL to populate temp table with SAS dataset

stuart753 — Wed, 07 Jan 2015 13:30:40 GMT

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

Re: PROC SQL to populate temp table with SAS dataset

Reeza — Wed, 07 Jan 2015 17:12:13 GMT

That's correct.

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

Re: PROC SQL to populate temp table with SAS dataset

stuart753 — Wed, 07 Jan 2015 22:11:46 GMT

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

Re: PROC SQL to populate temp table with SAS dataset

Patrick — Wed, 07 Jan 2015 22:35:59 GMT

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