topic Re: SQL Server pass through (merge, update, insert) in SAS Data Management

SQL Server pass through (merge, update, insert)

SASHuntsville — Mon, 27 Feb 2017 19:47:25 GMT

How can I both UPDATE the appropriate existing rows and INSERT new rows from a SAS dataset to a SQL Server table?

The following code works to update but does not insert NEW rows to the master table:

proc sql;

connect to odbc as modbc

(user=iuser password=XXXXXXXX dsn=dsname insertbuff=10000);

create table SSQL.TEMP as

SELECT DISTINCT

Var1,

Var2,

Var3

FROM SASLB.sasdataset

WHERE var3 IN('H','L');

execute (

merge into POINT_MASTER a

using TEMP b

on (a.Var1=b.Var1 and a.Var2=b.Var2)

when matched then update

set

a.Var1=b.Var1,

a.Var2=b.Var2,

a.Var3=b.Var3;

) by modbc;

execute (drop table TEMP) by modbc;

disconnect from modbc;

quit;

Re: SQL Server pass through (merge, update, insert)

LinusH — Tue, 28 Feb 2017 11:32:07 GMT

Could you please explain what "The following code works to update" means?

Some kind of log that shows whats going on would be nice.

Since this explicit pass through, the logic in SQL Server is of no concern for SAS. From a SAS POV you just need to verify that the table created from SAS in SQL Server looks like is supposed to.

Re: SQL Server pass through (merge, update, insert)

SASHuntsville — Tue, 28 Feb 2017 14:01:35 GMT

In SAS I can update values and insert new obs to a master SAS dataset in one merge. I'm trying to do the same in my EXECUTE statement for a SQL Server table. Updating and inserting rows in a SQL Server table from a SAS dataset is new to me.

SSQL.TEMP has 1000 rows and 3 columns

POINT_MASTER has 90000 rows and 50 columns

Works to UPDATE...

600 rows from SSQL.TEMP match Var1 and Var2 in POINT_MASTER, thus UPDATE Var3 in POINT_MASTER with Var3 values from SSQL.TEMP

400 rows from SSQL.TEMP do NOT match Var1 and Var2 in POINT_MASTER

How can I add/include/append these 400 new rows to POINT_MASTER within the EXECUTE statement? Does it have to be done seperate from the merge step?

Re: SQL Server pass through (merge, update, insert)

SASHuntsville — Tue, 28 Feb 2017 16:45:49 GMT

The following error started when I added lines 40, 41.

26         proc sql;
27         connect to odbc as myodbc (user=iuser password=XXXXXXXX dsn=idsname insertbuff = 10000);
28         create table SSQL.TEMP as
29         SELECT DISTINCT
30           VAR1,
31           VAR2,
32             VAR3
33            FROM SASLIB.sASDATASET
34            WHERE VAR2 IN('H','L') AND VAR1 IS NOT MISSING;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: SAS threaded sort was used.
NOTE: Table SSQL.TEMP created, with 215 rows and 3 columns.

35         execute (
36              merge into MASTER_TBL a
37              using TEMP b
38                   on (a.VAR1=b.VAR1
39                   and a.VAR2=b.VAR2)
40           when not matched by MASTER_TBL
41            then insert(VAR1, VAR2, VAR3) values(B.VAR1, B.VAR2, B.VAR3)
42           when matched then update
43                   set a.VAR3=b.VAR3;
44         ) by myodbc;
ERROR: CLI execute error: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'MASTER_TBL'.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
45         execute (drop table TEMP) by myodbc;
NOTE: Statement not executed due to NOEXEC option.
46         disconnect from myodbc;
NOTE: Statement not executed due to NOEXEC option.
47         quit;
NOTE: The SAS System stopped processing this step because of errors.

Re: SQL Server pass through (merge, update, insert)

JBailey — Tue, 28 Feb 2017 17:42:36 GMT

Hi @SASHuntsville,

This may work...

 execute (
            merge into MASTER_TBL as target
              using TEMP as source
                 on (target.VAR1=source.VAR1
                 and target.VAR2=source.VAR2)
              when matched then
                 update set target.VAR3=source.VAR3
              when not matched then
                 insert(VAR1, VAR2, VAR3) values (source.VAR1, source.VAR2, source.VAR3)
        ) by myodbc;

... then again, it may not work.

Best wishes,

Jeff

Re: SQL Server pass through (merge, update, insert)

SASHuntsville — Tue, 28 Feb 2017 21:15:13 GMT

I removed "by MASTER_TBL" from my WHEN NOT MATCHED line which is what you did in your example and it worked!

Thanks for the help.

Re: SQL Server pass through (merge, update, insert)

JBailey — Wed, 01 Mar 2017 13:08:54 GMT

Hi @SASHuntsville

My pleasure. I am happy to have played a role in solving your issue.

Best wishes,

Jeff