- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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!!
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So you just want to MERGE two datasets?
data want;
merge tablea tableb;
by post_date amount;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
MERGE is not a valid statement in SAS PROC SQL.
What does this do in Teradata?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
So you just want to MERGE two datasets?
data want;
merge tablea tableb;
by post_date amount;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content