topic Re: sql minus and intersect in SAS Programming

sql minus and intersect

aishajennifer1 — Fri, 27 Dec 2019 15:43:08 GMT

I have two datasets:

dataset#1:

patientid	value
1	143
2	123
3	123
4 5	4212 321

dataset#2

patientid	height
2	44
3	12

I want to create a dataset NOT to include patient from dataset#2

patientid	value
1	143
4	4212
5	321

I tried using MINUS and intersect but no avail

Any help would be great!!

Re: sql minus and intersect

ed_sas_member — Fri, 27 Dec 2019 15:48:55 GMT

Hi @aishajennifer1

You can use a DATA step with a MERGE statement:

proc sort data=dataset1; by patientid;run;
proc sort data=dataset2; by patientid;run;

data want (keep= patientid value);
	merge dataset1 (in=x) dataset2 (in=y);
	by patientid;
	if not y;
run;

Re: sql minus and intersect

ed_sas_member — Fri, 27 Dec 2019 15:53:16 GMT

Hi @aishajennifer1

Another efficient way to do this is to use a hash. It avoids to sort the data according to the key, etc.

data want;
	if _N_ = 1 then do;
		declare hash h (dataset:'dataset2');
		h.definekey ('patientid');
		h.definedone ();
	end;
	set dataset1;
	if h.find() ne 0 then output;
run;

Re: sql minus and intersect

ballardw — Fri, 27 Dec 2019 16:08:03 GMT

@aishajennifer1 wrote:

I have two datasets:

dataset#1:

patientid value

1 143

2 123

3 123

4

5

4212

321

dataset#2

patientid height

2 44

3 12

I want to create a dataset NOT to include patient from dataset#2

patientid value

1 143

4 4212

5 321

I tried using MINUS and intersect but no avail

Any help would be great!!

Show the actual code attempted.

The SQL would likely involve EXCEPT not "minus" by selecting desired ID then joining back to the base data to get the other variables

Re: sql minus and intersect

Reeza — Fri, 27 Dec 2019 16:45:36 GMT

proc sql;
create table included as 
select * from table1
where ID in (select ID from table2);
quit;



proc sql;
create table excluded as
select * from table2
where ID not in (select ID from table2);
quit;

Here are two examples on how to include from a subset of IDs or exclude a subset of IDs.

@aishajennifer1 wrote:

I have two datasets:

dataset#1:

patientid value

1 143

2 123

3 123

4

5

4212

321

dataset#2

patientid height

2 44

3 12

I want to create a dataset NOT to include patient from dataset#2

patientid value

1 143

4 4212

5 321

I tried using MINUS and intersect but no avail

Any help would be great!!