Using MERGE INTO in Proc SQL

[JoseSAS79]

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!!

[Kurt_Bremser]

MERGE is not a valid statement in SAS PROC SQL.

What does this do in Teradata?

[JoseSAS79]

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

[Patrick]

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.

[JoseSAS79]

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

[Patrick]

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.

[Tom · Accepted solution]

So you just want to MERGE two datasets?

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

[JoseSAS79]

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!