Help using Base SAS procedures

How to export and overwrite a table that was appended on?

Reply
N/A
Posts: 1

How to export and overwrite a table that was appended on?

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.

Super Contributor
Posts: 578

Re: How to export and overwrite a table that was appended on?

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;

Super User
Posts: 7,809

Re: How to export and overwrite a table that was appended on?

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;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 7

Re: How to export and overwrite a table that was appended on?

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

Respected Advisor
Posts: 4,173

Re: How to export and overwrite a table that was appended on?

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

Ask a Question
Discussion stats
  • 4 replies
  • 290 views
  • 1 like
  • 5 in conversation