sql minus and intersect

[aishajennifer1]

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!!

[ed_sas_member]

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;

[ed_sas_member]

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;

[ballardw]

---

@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

[Reeza]

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!!

---