topic Re: Ensuring Proc Append does not re-append data that has already been appended in SAS Procedures

Ensuring Proc Append does not re-append data that has already been appended

Caisha — Fri, 01 Jul 2016 01:17:57 GMT

Hello,

I am looking to create a type 2 data log from type 1 data. Meaning, I have a SAS query that pulls the current open inventory, and I want to log those counts at the end of every week when I run the report so that I collect each week's inventory counts each time I run it so that I have a running / historical list.

As such, I'm using a proc append, but if someone were to run the proc append in error then it may re-append data that's already appended to my new 'base' table.

In my 'data =' step I have included a today() run date field.

Is there a way to check whether a date in the 'data' step is equal to the max date in the 'base' table and if so not to append? I want to ensure that I do not continue to append data for the same day if the query is run multiple times a day by accident.

Thank you!

Currently using:

Proc append base= CAIT2.SIU_OPEN_ASSIGNMENTS_HISTORICAL

data= GRIDWORK.QUERY_FOR_APPEND_TABLE_0000 force;

run;

NOTE: The DATA = table includes a field titled 'Run_Week' which is an INTNX statement for a date value in the 'data' table. Looking to do the following: If the 'Run_Week' field in the 'data=' table is > the max 'Run_Week' field in the 'base' table then append, else do not append.

Re: Ensuring Proc Append does not re-append data that has already been appended

Reeza — Fri, 01 Jul 2016 04:16:59 GMT

Either create a macro, where it firsts checks for the presence of the key variable and then proceeds to append or not as appropriate.

Or consider looking into UPDATE statement within a data step.

Re: Ensuring Proc Append does not re-append data that has already been appended

Kurt_Bremser — Fri, 01 Jul 2016 06:56:51 GMT

We usually solve this by doing a proc sort with nodupkey.

If you can't identify a sufficient set of key variables, use noduprec or do a proc sql with select distinct *

Re: Ensuring Proc Append does not re-append data that has already been appended

LinusH — Fri, 01 Jul 2016 07:59:08 GMT

Update is MUCH slower than append so from a performance perspective I wouldn't recommend that.
Sounds like you don't have control of your process if you think that data can be appended by mistake.
A macro could be one way of solving it. Another would be to create a unique index. If that's table is large consider use SPDE instead of Base engine.

Re: Ensuring Proc Append does not re-append data that has already been appended

Patrick — Mon, 04 Jul 2016 10:51:22 GMT

If the data volumes are high and performance is important (something greater 1M rows and processing within seconds) then I'd go for a control table where you store the last date loaded.

If above is not really of concern then you could go for some code as below.

data history_rec;
  do week=1 to 10;
    count=ceil(ranuni(1)*20);
    output;
  end;
run;

data new_rec;
    do week=6,8,11,12;
    count=ceil(ranuni(1)*20);
    output;
  end;
run;

proc sql noprint;
  select max(week) into :max_week
  from history_rec
  ;
quit;

proc append base=history_rec data=new_rec(where=(week>&max_week));
run;