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

[David_Billa]

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?

[Sajid01]

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

[SASKiwi]

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/...

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

[David_Billa]

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

[David_Billa]

@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?

[SASKiwi]

@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.

[David_Billa]

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

[SASKiwi · Accepted solution]

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