You might try something like this (basically what I described in my previous post). The first part is just generating some sample data - NOTE that this assumes you could potentially add back zip to your CASE (file1) dataset. At the end of this first part, I'm creating a random number in the control dataset, using that number to sort, then assigning a sequential number to the controls based on that sort. Also, note that I'm saving permanent datasets to free up space in WORK. Change libname at the top as needed.
libname here "your directory";
* be careful - this removes the permanent case / control datasets - only
doing this here because these are simulated data ;
proc datasets lib=here memtype=data nolist nodetails;
delete cases controls;
run; quit;
data
here.cases (keep=ID zip dt decile)
controls (keep=ID zip)
;
length ID 6 zip $5 dt 4 decile 3;
format dt date9.;
do ID=1 to 50000;
zip=put(rand('integer',10000,15000),z5.);
dt='01Jan2017'd+rand('integer',0,364);
decile=rand('integer',1,10);
if ranuni(0)<0.1 then output here.cases;
else output controls;
end;
run;
proc sql undo_policy=none;
create table zip_dt_dec as
select distinct zip, dt, decile
from here.cases;
create table controls as
select distinct a.ID, b.decile
from
controls A
inner join
zip_dt_dec B
on a.zip=b.zip;
quit;
options formdlim=' ';
proc print data=zip_dt_dec (obs=5); run;
proc print data=here.cases (obs=5); run;
proc print data=controls (obs=5); run;
data controls;
set controls;
call streaminit(1614583);
sortID=rand('uniform')*10000;
run;
proc sort data=controls; by sortID; run;
data here.controls;
length controlnum 6;
set controls (drop=sortID);
controlnum=_N_;
run;
* dump everything from WORK ;
proc datasets lib=work memtype=data nolist nodetails kill; run; quit;
...then, this macro loops through the controls, attempting to match each new chunk of controls to the remaining cases for which 2 matches have not yet been found. Once it either reaches the end of the controls or finds 2 matches for every case, it exits.
%macro step_match(size=10000);
proc sql noprint;
select count(1) into :ncases_left trimmed from here.cases;
select count(1) into :ncontrols trimmed from here.controls;
quit;
data cases_left;
set here.cases;
length nfound 3;
nfound=0;
run;
proc sql;
create table frawc2n as
select 'frawc2n' as fmtname, 'N' as type, ID as start, monotonic() as label
from (select distinct ID from here.controls);
quit;
proc format cntlin=frawc2n; run;
* create a permanent copy of the controls dataset, except we will remove people from this one ;
data here.controls_left;
set here.controls;
run;
%do cnnum=1 %to &ncontrols %by &size;
data cntr2num_sub;
set here.controls_left (firstobs=&cnnum obs=%eval(&cnnum+&size-1));
length fmtname $8 type $1 start label 8;
retain fmtname 'fc2num' type 'N' label 0;
array T {&ncontrols} 3 _temporary_;
idnum=put(ID,frawc2n.)*1;
if T[idnum]=. then do;
T[idnum]=1;
start=ID;
label+1;
output;
end;
keep fmtname type start label;
run;
proc format cntlin=cntr2num_sub; run;
%put ::: reading controls from row &cnnum to row %eval(&cnnum+&size-1) ;
data
controls_used (keep=matched_control rename=(matched_control=ID))
matched (keep=ID dt decile matched_control)
;
set
here.controls_left (in=A firstobs=&cnnum obs=%eval(&cnnum+&size-1))
cases_left
;
array cntrlID {&size} 6 _temporary_;
array dec {&size} 3 _temporary_;
if A then do;
cntrlID[_N_]=ID;
dec[_N_]=decile;
end;
else do;
length matched_control 6;
array used {&size} $1 _temporary_;
do i=1 to &size;
if decile=dec[i] then do;
cloc=put(cntrlID[i],fc2num.)*1;
if used[cloc]='' then do;
nfound+1;
matched_control=cntrlID[i];
used[cloc]='x';
output matched;
output controls_used;
if nfound=2 then leave;
end;
end;
end;
end;
run;
proc append base=all_matched data=matched; run;
proc sql undo_policy=none noprint;
drop table matched;
create table cases_left as
select a.*
from
cases_left A
inner join /* 1:1 */
(select ID from cases_left except select ID from all_matched) B
on a.ID=b.ID
order by a.ID;
select count(1) into :ncases_left trimmed from cases_left;
quit;
%if &ncases_left=0 %then %goto stopmatch;
proc sql undo_policy=none;
create table here.controls_left as
select a.*
from
here.controls_left A
inner join /* M:1 */
(select ID from here.controls_left except select ID from controls_used) B
on a.ID=b.ID
order by a.controlnum;
quit;
%end;
%stopmatch:
%mend; *step_match();
options mprint;
%step_match(size=10000);
title "first 50 obs of matched data";
proc print data=all_matched (obs=50); run;
...it's creating a temporary dataset called "all_matched" - to save space in WORK, you might try changing all references to this dataset to something permanent, though obviously be careful if you run this multiple times to delete it first because otherwise the PROC APPEND step here is just going to keep adding stuff to data from the prior runs.
This was an interesting matching problem, not just because of the size but because of the extra complexity created by having controls potentially represented more than once in the CONTROLS dataset (d/t different deciles) but then having to remove all instances of a control if one instance was used. Will definitely be adding this to my own personal github for when dealing with large data. I have not tested this yet with a very large dataset. It shouldn't run out of memory, but it might for sure be slower than anything you'd do with hash tables (assuming a hash table could hold your 35M record control dataset).
