@KiranMaddi
You've started a really interesting discussion here and I'm spending unreasonable time with it because I'm actually also learning something.
I believe you're now at the point of "it depends".
If you don't want to go for an "in-place" approach using MODIFY then the one thing you want to concentrate on is to keep I/O to a minimum. This means you want to minimize read/write operations and make best use of your actual environment.
There are also a few data set option which you can set when creating a ds which can improve I/O.
http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#n0a1u9b2buxl5yn1nv12...
How far you need to take things will depend on how big your current performance problem actually is (are some quick-wins sufficient or do you need to squeeze out the last drop?).
Both the UPDATE and MERGE statement fully recreate the master ds and as you have Updates and Inserts I would go for Merge.
If both the Master and Transaction ds are indexed then the BY statement could use the index and the ds wouldn't need to be physically sorted that way. That's what @Ksharp was talking about using UPDATE with an index.
I've done some testing with your volumes and the results don't support such an approach (the merge took around 6 minutes on my machine). So for merging have your data sets physically sorted in the way you refer to them in the By statement when merging (that took me 40 seconds in my environment which is more or less the time required to write the master ds).
Now: If you've got control how the master DS is sorted then just have it sorted ONCE (once off job). A Merge with by group processing maintains the sort order and you don't need to run a sort every single time.
On my laptop using the V9 engine with compression turned performance was as good or even better than most other approaches (the merge took around 40 seconds for a compressed DS, only the MODIFY with an index and uncompressed data was significantly faster with 20 seconds).
On my laptop the main time spent is for "write to disc" - initial creation of the master ds took already around 40 seconds.
Now these timings could be very different in your environment and your records and you will have to run some tests to come up with the right strategy (if required, the quick win will be to no more sort your master DS every single time).
Assuming that your master DS is quite huge even when compressed, I still would go for the SPDE engine to keep file size in a reasonable area. Using the SPDE engine could also be beneficial for data retrieval (eg. multithreading with indexes and where clauses) and I'm sure that also your OPS team prefers to have reasonable file sizes for backup and recovery processes.
If you're happy to have your master ds sorted the way you need it for a Merge with By group processing then using the hash approach as proposed by @Ksharp won't be faster as it still requires a read/write operation. Only consider the hash approach if your master ds is sorted differently AND you can be sure that you can fit the full transaction dataset always into memory.
Below a code version using MERGE which assumes that the Master DS is already sorted as expected by the By statement. This is basically your initial code without the sort. The merge took around 40 seconds in my environment.
I'm expecting your real data to have a much wider PDV and writing the full data could take up much more time. So here using the SPDE engine and/or a MODIFY statement or at least using the "fastest disk available" could make a big difference in end-to-end run-times.
The ds option "sortedby=date id" is not necessary for the merge BUT it can be beneficial to to have this attribute set on a ds for data retrieval as it can allow the compiler to eventually take a better execution path (especially for SQL queries, or where clauses with the SPDE engine).
There had been the suggestion to use the "PRESORTED" option with Proc Sort so that sorting only happens when it is required. For Proc Sort to determine if sorting is required it still would need to scan (read) the source ds. I'm not sure but eventually with attribute "sortedby=date id" set, Proc Sort wouldn't scan the ds anymore. If that's true then you could have in your code a Proc Sort for the Master ds - just make sure that you use option "PRESORTED" and set attibute "sortedby" on the output ds (out= in proc sort; with same ds name than in data=).
options fullstimer;
/* create directories in WORK */
/*data _null_;*/
/* Base_Dir="%sysfunc(pathname(work))";*/
/* rc1=dcreate('data', cats(Base_Dir,'\'));*/
/* rc2=dcreate('index', cats(Base_Dir,'\'));*/
/*run;*/
/* define libname using SPDE engine */
/* partsize of 4GB is for uncompressed. */
/* Try to end up with around 4 partitions per "Data folder" and choose Partsize accordingly */
/*libname spdetest spde ("%sysfunc(pathname(work))")*/
/* datapath=("%sysfunc(pathname(work))\data" "D:\temp\SPDE\data")*/
/* indexpath=("%sysfunc(pathname(work))\index")*/
/* partsize=4G*/
/* ;*/
libname spdetest (work);
/* create master data set */
/*data spdetest.master(compress=yes);*/
data spdetest.master(compress=yes sortedby=date id);
length other_var $10 big_var $1000;
retain other_var 'MASTER' big_var 'big';
do date= '01jul2016'd to '31jul2016'd, '01aug2016'd, '01sep2016'd;
do id=1,2,3,5, 100 to 10**6;
output;
end;
end;
format date date9.;
run;
/* create transaction data set */
data trans;
length other_var $10;
retain other_var 'TRANS';
do id=4,2, 50000 to 2000 by -1;
do date='01sep2016'd, '01oct2016'd;
output;
end;
end;
format date date9.;
run;
proc sort data=trans;
by date id;
run;
/* merge master with trans */
data spdetest.master(compress=yes sortedby=date id);
merge spdetest.master trans ;
by date id;
run;
data retrieval;
set spdetest.master;
where other_var='TRANS';
run;
proc contents data=spdetest.master;
run;quit;
Click image to register for webinar
