Hello,
This is a general best practices question.
The basic question is: what is the best way to join additional columns into an existing table?
I've been using a JOIN in PROC SQL to add columns from another table into my existing table. To do this, I am querying from my existing table as a base then adding columns from a different table via a left join - which I know is not best practice! To give a concrete example, here is a simplified version of what I'm doing:
PROC SQL;
CREATE TABLE WORK.main_table AS
SELECT main.*, a.newcol1, a.newcol2
FROM WORK.main_table main
LEFT JOIN WORK.addl_data a
ON main.id = a.id;
QUIT;
As you might expect, I get the warning:
CREATE TABLE statement recursively references the target table
which I understand, the whole point of my query is to update the target table.
I should say for context that the table I'm "creating" is quite large, so I'm reluctant to create a whole new file just for the sake of sanitizing the code, and take up limited space on an aging server.
I'm also reluctant to use a DATA step MERGE if I can avoid it, as my impression is that these have some tricky and/or unexpected behaviors, plus I've been led to believe (perhaps incorrectly?) that generally PROC SQL is more efficient for handling large datasets than the DATA step.
So the question is: what is the best practice way to do this? Is there a way to use the ALTER TABLE function within PROC SQL for this purpose? Is the best option just to bite the proverbial bullet and merge via a DATA step?
Thanks!
