BookmarkSubscribeRSS Feed
Caisha
Calcite | Level 5

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.

4 REPLIES 4
Reeza
Super User

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. 

LinusH
Tourmaline | Level 20
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.
Data never sleeps
Patrick
Opal | Level 21

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;

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
  • 1349 views
  • 0 likes
  • 5 in conversation