topic Re: Update a column in SQL server table from SAS dataset in SAS Programming

Update a column in SQL server table from SAS dataset

VSht — Tue, 11 May 2021 04:41:20 GMT

Hi,

I've got a table in SQL server and a table in SAS, with the same structure and a unique row identifier. I need to update a column in SQL table with the values from SAS table. Can it be done from SAS? I do not have ODBC or Access, but use PC Files for the SAS-SQL connection. Any help will be greatly appreciated.

Re: Update a column in SQL server table from SAS dataset

SASKiwi — Tue, 11 May 2021 04:51:45 GMT

Can you post a code example of how you are accessing SQL Server via the SAS PC Files Server?

If you are able to load an SQL Server temporary table from SAS then you could run an SQL Server script to update from that.

If that isn't an option you could write your SAS data to a delimited flat file and then have an SQL Server script upload that.

Re: Update a column in SQL server table from SAS dataset

VSht — Tue, 11 May 2021 05:06:12 GMT

When I overwrite SQL table with SAS table I do as in the code below.

In my current task though, SQL people do not want SQL table to be overwritten. Therefore, I can only update values in one column. The table in SQL is a control table which tells SAS which processes to be executed. It has a column - SAS_Run_Flag=0. When SAS finishes without error, SAS_Run_Flag changes to 1 in SAS temporary table. Then SAS_Run_Flag in SQL has to be updated with values from SAS temp table. Hope it makes sense.

Code for SQL table over-write:

LIBNAME DSAS01 pcfiles server=localhost port=9621 dsn=SAS_dSAS01 user=SASConnector password="";
PROC SQL;
CONNECT to pcfiles as myconn (server=localhost port=9621 dsn=SAS_dSAS01_&dsn_dst user=SASConnector password="");

SELECT * from connection to myconn
(
IF OBJECT_ID(%str(%')&OutFile%str(%'), 'U') IS NOT NULL
DROP TABLE &OutFile;
);
QUIT;
*-------------| Write the updated table |-----------------;
DATA dsas01.&OutFile;
SET &OutFile;
RUN;

Re: Update a column in SQL server table from SAS dataset

VSht — Sun, 16 May 2021 23:09:37 GMT

libname sqlpcf pcfiles dsn="sqlpcf" user="....." password="....";

proc sql;

connect using sqlpcf;

/* Microsoft SQL Server T-SQL syntax goes inside the () characters */

execute (

EXEC dbo.uspGetEmployeeManagers 6

) by sqlpcf;

quit;