I guess there are two questions here:
1: Which approach will give you the best performance.
2: How to set-up DIS to generate the desired code.
If spde is the right choice depends on how you intend to use this big table later on. spde is certainly preferable if you've got later on queries with where clauses.
I also consider spde a good choice for really big tables because you don't create these huge files that can create challenges for backup & recovery.
Assuming your delta load is update/insert only without delete:
Ideally have both your master and transaction table physically sorted by primary key.
I'm not sure which options exactly you need to select in the DIS transformation but the code it generates should look similar to below sample. When I used DIS in the past I normally just tried the options that appeared to be right and then checked in the generated code if it created what I wanted it to be - or else changed "something" and checked again until I got what I wanted.
libname spdewrk spde "%sysfunc(pathname(work))";
data spdewrk.master;
set sashelp.class;
run;
data spdewrk.transactions;
set sashelp.class;
if name='Alfred' then age=15;
if name in ('Henry','James','Jane','Janet','Mary','Philip','Robert','Ronald','Thomas') then delete;
output;
if name='Barbara' then
do;
name='New';
output;
end;
run;
/* sorts not needed if you can be sure your tables are already sorted */
proc sort data=spdewrk.master presorted;
by name;
run;
proc sort data=spdewrk.transactions;
by name;
run;
/* upsert master with transaction table */
data spdewrk.master;
modify spdewrk.master spdewrk.transactions updatemode=nomissingcheck;
by name;
if _iorc_=0 then replace;
else output;
run;
If there are also deletes:
That's something SAS is not that great at. In-place deletes in SAS tables are only logical which leads to the tables carrying more and more "dead wheight" . You only get rid of the logically deleted records if you re-create the table.
And last but not least: Define chunks for your master spde table that are greater than the default.
... View more
