1- Formats can be slow for large numbers of values in my experience.
2- Keep in mind that a sorted table will be fast not only for maintenance for you as an admin, but also for all users.
3- Rewriting sorted tables is just a matter of copying the table, so if you have the space it should not take long considering the volume you have.
4- If you are I/O bound, using SPDE with binary compression instead of the V9 engine is a must as it will typically decreases table size by 90%. This engine also processes indexes much faster.
5- When doing such updates, I like to keep tabs of what happens in the log and usually use something like:
data NEWHISTORY (drop=_:);
if LASTOBS then put _REPLACED= _ADDED= _KEPT=;
merge HISTORY (in=OLD)
NEW (in=NEW)
end=LASTOBS;
by CUSTOMERNO CURRENT_FLAG;
if OLD and NEW then _REPLACED+1;
else if OLD then _KEPT +1;
else _ADDED +1;
run;
proc sort data=NEWHISTORY
out =HISTORY(index=( ... ))
presorted;
by CUSTOMERNO CURRENT_FLAG ;
run;
Add the deletion logic as you see fit for your data (like: if DEL then do; _DELETE+1; delete; end; )
The sort step is there just because SAS have been sitting on their hands and there is still no way to set the SORT VALIDATED flag in the data step. PROC SORT just copies the table here.
Index creation is fast on sorted tables.
... View more
