06-30-2016 09:17 PM
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.
Proc append base= CAIT2.SIU_OPEN_ASSIGNMENTS_HISTORICAL
data= GRIDWORK.QUERY_FOR_APPEND_TABLE_0000 force;
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.
07-01-2016 12:16 AM
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.
07-01-2016 02:56 AM
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 *
07-01-2016 03:59 AM
07-04-2016 06:51 AM
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;