topic Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through in SAS Programming

Create a temp table in SQL Server from SAS work table via SAS explicit pass through

David_Billa — Mon, 24 May 2021 17:59:12 GMT

I would like to how to create a temp table in SQL Server by reading data from SAS work table via SAS explicit pass through

May I know what is the isue with INTO and FROM in code below?

Proc sql noprint;
    Connect to SQLSVR (DATAsrc=&DATA. AUTHDOMAIN="XXXXX." dbMax_text=32767);
    Execute( 
        Select * 
             1 as Version
            ,1 as delivery
            ,'' as data
            ,'' as Location
            ,'MASTER' as tableName
            ,Count(*) as numberOfRecords                          
            into: #temp%trim(&unique_num.)                   

        From &_INPUT.
            Group by Version,data,location;
    ) by SQLSVR;
    Disconnect from SQLSVR;
Quit;

Error:

ERROR: CLI execute error: [SAS][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near ':'. : [SAS][ODBC 
       SQL Server Wire Protocol driver][Microsoft SQL Server]Executing SQL directly; no cursor.

Any help to resolve the error?

Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through

Sajid01 — Mon, 24 May 2021 18:58:18 GMT

Please have a look here. https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0he4t6yjfmkhpn16qrf0cdhllu6.htm#p1670uazqoglsvn1iywvcd6pv8d4
The example should solve your issue.

Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through

SASKiwi — Mon, 24 May 2021 20:29:39 GMT

If &_INPUT refers to a SAS WORK table then your program won't work. Any SQL running inside an EXECUTE statement runs entirely on SQL Server and it knows nothing about SAS tables. Creating SQL Server temporary tables is best done using a SAS LIBNAME using the SQLSRVR engine. This post shows one way:

https://communities.sas.com/t5/SAS-Procedures/PROC-SQL-to-populate-temp-table-with-SAS-dataset/td-p/171226

This doc describes the general approach: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/n0irpkyp22l7vzn1il9lx6f4wmx9.htm

Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through

David_Billa — Tue, 25 May 2021 01:56:06 GMT

How to create a temp table in SQL Server using explicit pass through ?

Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through

David_Billa — Tue, 25 May 2021 05:03:17 GMT

@SASKiwi @Sajid01 Can't we create a temp table in SQL Server from SAS work table via SAS explicit pass through instead of using libname?

Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through

SASKiwi — Tue, 25 May 2021 06:46:45 GMT

@David_Billa - Not possible unless your SAS program generates a bunch of SQL insert statements, one for each row you want to add - it's not a sensible solution. Using a LIBNAME statement and a PROC APPEND or SAS SQL is far preferrable.

Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through

David_Billa — Tue, 25 May 2021 07:08:06 GMT

Any examples to do it using SAS SQL as you mentioned?

Re: Create a temp table in SQL Server from SAS work table via SAS explicit pass through

SASKiwi — Tue, 25 May 2021 07:18:56 GMT

@David_Billa - Here is the link I tried to provide before.