topic Re: Proc Append - add new data to a historic table in SAS Programming

Proc Append - add new data to a historic table

Symun23 — Thu, 09 Aug 2018 11:27:37 GMT

Hello,

So I have two tables, one of them is a historical table (Table 1) that I am storing all data in, the other is a table that updates with new data and deletes old data that is no longer needed (Table 2).

I am wanting to take the new data from Table 2 and add it on to Table 1, what is the easiest way to do this? I will be able to identify the new data as there is a field that identifies new data going in, so the data goes in in batches and I have the name of the new batches going in.

Is Proc Append the best thing in this situation?

Re: Proc Append - add new data to a historic table

Kurt_Bremser — Thu, 09 Aug 2018 11:35:17 GMT

proc append cannot delete data from the base dataset, it only adds new data to the (physical) end of the base dataset.

Please supply some example data (see my footnotes) to illustrate how you would want to handle the delete's.

Re: Proc Append - add new data to a historic table

Symun23 — Thu, 09 Aug 2018 11:37:00 GMT

Thats fine, I am not wanting to delete anything, just want to add the new data from Table 2 and add it on to the end of Table 1

Re: Proc Append - add new data to a historic table

Kurt_Bremser — Thu, 09 Aug 2018 11:48:09 GMT

@Symun23 wrote:
Thats fine, I am not wanting to delete anything, just want to add the new data from Table 2 and add it on to the end of Table 1

Then use proc append. Keep in mind that you need exclusive access to the base dataset while appending (no process may have the dataset opened).

Re: Proc Append - add new data to a historic table

SuryaKiran — Thu, 09 Aug 2018 12:58:05 GMT

Hi,

PROC APPEND might not work for you, because when you have extra variable in data=table2 that are not in base=table1 FORCE option concatenates and drops the extra variables. Missing values will be assigned to variables that are in base=table1 but not in data=table2. I suggest you using proc sql set operator OUTER UNION CORR or Data step SET statement.

When you are using PROC APPEND with FORCE keep in mind that dropping/truncating might occur.

Check PROC APPEND vs OUTER UNION vs SET statement results here:

data allvars;
  set sashelp.class (obs=5);
run;

data onevar (keep= name_ age);
  set sashelp.class (obs=5 rename=(name=name_));
run;
 
proc append base=onevar data=allvars force ;
run;

proc print data=onevar;
run;

proc sql;
create table allvars_ as
select * from allvars
outer union corr
select * from onevar;
quit;

data allvars;
set allvars onevar;
run;