BookmarkSubscribeRSS Feed
Sas_Geek
Calcite | Level 5

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;

3 REPLIES 3
Vince28_Statcan
Quartz | Level 8

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

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 774 views
  • 0 likes
  • 4 in conversation