topic finding overlapping patients in 2 datasets in New SAS User

finding overlapping patients in 2 datasets

lmyers2 — Fri, 16 Apr 2021 20:07:11 GMT

Hello,

I'm trying to get a count of the distinct patients existing in two datasets. If I use proc sql and the patient numbers are the same in both datasets, they overwrite each other. Is there a proc sql query that scans 2 datasets and produces a number (in this case 3)? Below are sample data including an example of output I'm looking for.

Current data 1

MRN

Current data 2

MRN

MRN_both

Best

Laura

Re: finding overlapping patients in 2 datasets

mkeintz — Fri, 16 Apr 2021 20:22:53 GMT

@lmyers2 wrote:(emphasis mine)

Hello,

I'm trying to get a count of the distinct patients existing in two datasets. If I use proc sql and the patient numbers are the same in both datasets, they overwrite each other. Is there a proc sql query that scans 2 datasets and produces a number (in this case 3)? Below are sample data including an example of output I'm looking for.

Current data 1

MRN

1

2

3

10

Current data 2

MRN

1

2

3

4

5

MRN_both

1

2

3

Best

Laura

Why is the fact that they "overwrite each other" a problem. This means that you will get 1 observation per common MRN, which in turns means you will have only 3 observations - the number you want

proc sql noprint;
  create table _null_ as  select a.mrn from 
    data1 as a 
    join 
    data2 as b 
    on a.mrn=b.mrn;
quit;
%put &=sqlobs;

This creates table _NULL_ which is not an actual physical data set, but it does cause SQL to behave as if it were. So there will be a count of qualifying matches in macrovar SQLOBS. If you can an actual table, change _NULL_ to a dataset or table name.

Re: finding overlapping patients in 2 datasets

Reeza — Fri, 16 Apr 2021 21:12:05 GMT

You could look at PROC COMPARE but it's a bit of an overkill.

You could also do a SQL query.

proc sql;
create table want_sql as
select distinct t2.ID from current_data1 as t1 where t1.id in (select distinct t2.ID from current_data2 t2);
quit;

You could also do a data step merge.

data want_data;
merge current_data1 (in=t1) and current_data2 (in=t2);
by ID;
if t1 and t2;
run;

The Data step approach will not work if you have duplicate IDs in either tables but the SQL one will. You could de-dup either solution after though.

Re: finding overlapping patients in 2 datasets

Ksharp — Sat, 17 Apr 2021 10:46:00 GMT

data data1;
input MRN;
cards;
1
2
3
10
;
 

data data2;
input MRN;
cards;
1
2
3
4
5
;

proc sql;
create table want as
select mrn from data1
intersect
select mrn from data2;
quit;