topic Re: Limit the number of merges in SAS Procedures

Limit the number of merges

Sas_Geek — Wed, 17 Jul 2013 13:21:38 GMT

I have a two datasets - cases and controls. Assume that the dataset 'cases' is a small one with a few hundred patient IDs, while 'controls' is huge dataset with millions of patient IDs. I want to merge cases with controls on say, age variable, such that when a case is merged with 10 controls I want the merging process to stop for that case and move to the next. Is it possible with proc sql or merge (data step)? What would be the modifications in the following code:

proc sql;

create table matches as

select a.caseID, a.age, b.controlID, b.age

from cases as a, controls as b

where a.age = b.age;

quit;

data matches;

merge cases (in=a) controls (in=b);

by age;

if a and b;

run;

Re: Limit the number of merges

Reeza — Wed, 17 Jul 2013 14:44:58 GMT

I recommend the match macros from the Mayo Clinic:

http://www.mayo.edu/research/departments-divisions/department-health-sciences-research/division-biomedical-statistics-informatics/software/locally-written-sas-macros

Re: Limit the number of merges

Vince28_Statcan — Wed, 17 Jul 2013 15:16:42 GMT

Ok sorry if you received e-mail spam, I fixed something in the post.

data have;

input age 2. something $4.;

datalines;

23 aded

23 aaaa

23 add

23 agb

23 aef

24 aa

24 tt

24 ll

;

run;

data have2;

input age 2. somethingelse $2.;

datalines;

23 A

24 B

run;

data temp;

merge have have2;

by age;

if first.age=1 then byobscounter=1;

if byobscounter LE 2 then output;

byobscounter=byobscounter+1;

retain byobscounter;

drop byobscounter;

run;

You won't see a huge gain in efficiency since all the records in the huge table will still be read in the data vector but at least they won't be output and thus won't need to be written on the disk at the end of the merge.

Vincent

Re: Limit the number of merges

Ksharp — Thu, 18 Jul 2013 01:08:00 GMT

Almost like Vincent.

data controls ;
     input age 2. something $4.;
     datalines;
23 aded
23 aaaa
23 add
23 agb
23 aef
23 add
23 agb
23 aef
23 add
23 agb
23 aef
23 add
23 agb
23 aef
24 aa
24 tt
24 ll
25 aa
25 rr
25 hh
;
run;

 

data cases ;
     input age 2. somethingelse $2.;
     datalines;
23 A
24 B
run;

data matches;
merge cases (in=a) controls (in=b);
by age;
if age ne lag(age) then n=0;
n+1;
if a and b and n le 10 then output;
run;

Ksharp