I would like to append Table A and Table B together, and use that newly appended table to overwrite Table A. The purpose of this is to have Table A be updated weekly with the data in Table B within an EG flow, so Table A is up to date when the flow is run. We need Table A to be current, as this is the table that will be appended to and used as the base file.
We tried exporting the appended table as a step in the process, however it only allows us to overwrite Table A once. When running the flow additional times, Table A is locked and we cannot append any new Table B's after that.
How can we export the appended table and overwrite Table A each time this flow needs to be run? We also need to make sure that when debugging the flow and needing to run it multiple times in one session, we do not perpetually append onto Table A, and would like to put a check in place to see if Table B has already been appended once, and only once.
Sounds like you're just inserting Table B into Table A? That can be done in an EG workflow with either the append task or a code node:
proc sql;
insert into TableB
select * from TableA;
quit;
You most probably have the appended table open as a result of the export. The best option to prevent this is to close the server connection after each run.
In order to prevent duplicates, one option is to include data that identifies each run (date, name of table to append, ...) in the data and filter that out while appending:
%let table_a=baselib.tablea;
%let table_b=applib.tableb_4711;
data &table_a;
set
&table_a (where=(indicator ne "&table_b"))
&table_b (in=in_b)
;
if in_b then indicator = "&table_b";
run;
I think you can get what you need with just a proc append and if you no longer need table B you can just delete all records from table B:
proc append base=Table_A data=Table_B force;
run;
data Table_B;
set Table_B;
stop;
run;
CTorres
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.