Hello NN.
Being DATA1 the large table and DATA2 a very small one, you could force an index iteration between the the two and perform the count, like this:
* create "phoney" index on DATA2;
data data3 (index=(IDX));
set data2;
IDX=0;
run;
* do counts;
data counts;
set data1;
keep ID COUNT;
IDX=0; COUNT=0; * init index and counter;
do until (IDX eq 2); * do until no more matches;
set data3 key=IDX; * match;
if not _ERROR_ and LEAVE_DATES le OUTDATE then do;
if INDATE le LEAVE_DATES then COUNT+1; * indate/outdate match;
end;
else IDX+1; * reset phoney index;
end;
output; * output total;
_ERROR_=0; * reset _ERROR_ flag;
run;
This is a technique that I've been using for some years (before hashing appeared into SAS) with great success for a big table X very small table match scenario with N to N matching relationship. Do not try to do this with a DATA2 with more then 1000 rows, performance will be disastrous.
Another way of doing the same, would be to load the smaller table (DATA2) into a hash iterator object and iterate through it for each observation of the large dataset (DATA1).
Here you could have a DATA2 table up to 1.5M without compromising performance.
Check the online doc for the hash iterator object here:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a002576871.htm
Cheers from Portugal.
Daniel Santos @
www.cgd.pt