BookmarkSubscribeRSS Feed
aishajennifer1
Calcite | Level 5

I have two datasets:

 

dataset#1:

patientidvalue
1143
2123
3123

4

5

4212

321

 

dataset#2

patientidheight
244
312

 

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

 

patientidvalue
1143
44212
5321

 

I tried using MINUS and intersect but no avail

 

Any help would be great!!

4 REPLIES 4
ed_sas_member
Meteorite | Level 14

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
Meteorite | Level 14

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
Super User

@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
Super User
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!!


 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 856 views
  • 0 likes
  • 4 in conversation