How to use SAS data set brought to SQL Server temporary table and use with another temp table to join,update

Question

Using SAS Enterprise Guide Version 7.1 64 bit.

I cannot create base tables in SQL Server thus can only create temporary table for data processing.

I am also pulling data over from a SAS data set to a global temporary table as(the SAS data set is teradata table pushed to sas-(step2). I need to update holiday table.

I also cannot see how many records are updated in the log (if there is a way to get it, it will be helpful as that way i know the code is working) - I have the sastraceloc but that is not showing update counts in a table.

I finally need to update ##t1 (step4) table created in step 1 with fields from ##holidays table (step2), how do I code this? Am I using the execute, proc sql combination correctly?

Step #1

LIBNAME tmpdta ODBC DATAsrc=datasrcname INSERTBUFF=32767
USER='uid' PASSWORD="pwd" connection=shared;

LIBNAME loc '/c/folder/data';

PROC SQL exec noerrorstop;
CONNECT TO odbc as test(DSN=datasrcname USER='uid' PASSWORD="pwd" connection=shared;
connection=shared);
execute
(create table ##t1 
id int,
name varchar(50),
address varchar(100)
)by test;

-- end of 1

step #2

data tmpdt.'##holidays'n;
set loc.holidayexpns;
run;

step #3

proc sql;
connect using tmpdt;
execute
(
update u SET
fee=0,
month=5
FROM tmpdt.##holidays u
)by tmpdt;

step #4

PROC SQL exec noerrorstop;
CONNECT TO odbc as test(DSN=datasrcname USER='uid' PASSWORD="pwd" connection=shared;
connection=shared);
execute
(
update xyz
set a.fee=b.fee,
a.month=b.month
from tmpdt.##holidays h
join ##t1 xyz on 
h.id=xyz.id
)by test;

Richard Richard · Accepted Answer · 2020-01-23T15:34:59

The execute native-query will not know the SAS reference tmpdt., tmpdt will be known to your user login only if on the SQL Server side you have access to a catalog of like name. Did you mean to indicate tempdb ?

Per documentation

Any return code or message that is generated by the DBMS is available in the macro variables SQLXRC and SQLXMSG after the statement completes.

How to use SAS data set brought to SQL Server temporary table and use with another temp table to join,update

1 Answers