BookmarkSubscribeRSS Feed
colemaly
Calcite | Level 5

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.

4 REPLIES 4
DBailey
Lapis Lazuli | Level 10

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;

Kurt_Bremser
Super User

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;

CatCol
Fluorite | Level 6

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

Patrick
Opal | Level 21

What suggests looks to me like what you need (at least based on your description).

In regards of table locking after the first execution in SAS EG: Setting below option should make this "go away".

Capture.PNG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1188 views
  • 1 like
  • 5 in conversation