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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

So you just want to MERGE two datasets?

 

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

View solution in original post

7 REPLIES 7
JoseSAS79
Calcite | Level 5

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
Opal | Level 21

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
Calcite | Level 5
Thanks Patrick - do you mind elaborating how to do it with two proc sql statements (update and insert)?
Patrick
Opal | Level 21

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
Super User Tom
Super User

So you just want to MERGE two datasets?

 

data want;
   merge tablea tableb;
   by post_date amount;
run;
JoseSAS79
Calcite | Level 5
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!

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1495 views
  • 0 likes
  • 4 in conversation