BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASHuntsville
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
SASHuntsville
Fluorite | Level 6

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.

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20

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.

Data never sleeps
SASHuntsville
Fluorite | Level 6

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?

 

 

SASHuntsville
Fluorite | Level 6

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.

JBailey
Barite | Level 11

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

 

SASHuntsville
Fluorite | Level 6

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.

JBailey
Barite | Level 11

Hi @SASHuntsville

 

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

 

Best wishes,

Jeff

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 6262 views
  • 2 likes
  • 3 in conversation