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.
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.
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 *
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;
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.
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.