topic Re: Using MERGE INTO in Proc SQL in SAS Procedures

Using MERGE INTO in Proc SQL

JoseSAS79 — Sat, 25 Sep 2021 08:29:47 GMT

Hi All!

I am trying to update a merge into code that updates a table that sits in Teradata using information from another table that is also in teradata. I have now brought both these two tables into my SAS server environment within a folder , and for simplicity lets call the libname output. I want to update the below code to do the same merge steps using the tables that are now in the output folder. However when I remove the teradata connections and try to do the merge wrapped in proc sql it doesn't work.

My current code is something like this:

proc sql;

connect to teradata as tera (server ='XXXX', Authdomain='XXXX')

execute(

MERGE INTO TableA as tbl1 using TableB as tbl2

on (tbl1.post_date = tbl2.pos_date

tbl1.amount = table2.amount)

WHEN NOT MATCHED THEN INSERT

( tbl2.pos_date

table2.amount

table2.order_num

table2.invoice_num)

)by tera;

quit;

In order to reference to the new libraries within the SAS server I have updated the code to :

proc sql;

MERGE INTO Output.TableA as tbl1 using Output.TableB as tbl2

on (tbl1.post_date = tbl2.pos_date

tbl1.amount = table2.amount)

WHEN NOT MATCHED THEN INSERT

( tbl2.pos_date

table2.amount

table2.order_num

table2.invoice_num)

quit;

However this MERGE INTO statement doesn't seem to work when its not being done within the teradata connection. Could you please let me know if there is something wrong with my new code where I have removed the tera data connections and updated the lib names? If MERGE INTO can not be done in a PROC SQL , can you please tell me an alternative method to do the steps pls?

Thanks in advance!!

Re: Using MERGE INTO in Proc SQL

Kurt_Bremser — Sat, 25 Sep 2021 08:36:46 GMT

MERGE is not a valid statement in SAS PROC SQL.

What does this do in Teradata?

Re: Using MERGE INTO in Proc SQL

Patrick — Sat, 25 Sep 2021 08:50:39 GMT

SAS SQL doesn't have a Merge statement. You need either two SQLs, one Update and one Insert or you could consider to use a SAS Data step Modify statement.

Re: Using MERGE INTO in Proc SQL

JoseSAS79 — Sat, 25 Sep 2021 09:01:15 GMT

The merge step is saying check if post date and amounts match in both tables. If they do match then the rows is already present in Table A. If they don't match then add the rows for columns pos_date, amount

order_num and invoice_num from Table A into Table B

Re: Using MERGE INTO in Proc SQL

JoseSAS79 — Sat, 25 Sep 2021 09:02:44 GMT

Thanks Patrick - do you mind elaborating how to do it with two proc sql statements (update and insert)?

Re: Using MERGE INTO in Proc SQL

Patrick — Sat, 25 Sep 2021 09:09:19 GMT

For me to provide actual code you would need to provide sample data first via SAS Data step code that creates such data.

May-be the discussion here is already all you need.

Re: Using MERGE INTO in Proc SQL

Tom — Sat, 25 Sep 2021 16:16:52 GMT

So you just want to MERGE two datasets?

data want;
   merge tablea tableb;
   by post_date amount;
run;

Re: Using MERGE INTO in Proc SQL

JoseSAS79 — Sun, 26 Sep 2021 07:52:01 GMT

Oh didn't realise it was a simple merge step! I tried it with a sample from both tables and it worked. Thanks for that!