DATA Step, Macro, Functions and more

Removing observations based on two variable after matching with other table

Accepted Solution Solved
Reply
Contributor
Posts: 60
Accepted Solution

Removing observations based on two variable after matching with other table

Hi,

 

I want to remove observation from A which are present in B;

 

 

data a;
input st_code Addr$ Name$ ID;
datalines;
06111 kota atul 552927
08888 delh gaur 552927
13089 pune pink 666666
;
run;


data b;
input st_code ID;
datalines;
06111 552927
13089 552927
13135 552927
;
run;


Accepted Solutions
Solution
‎06-13-2017 04:45 AM
PROC Star
Posts: 253

Re: Removing observations based on two variable after matching with other table

just another way

proc sql;
create table abc as 
select * from a tbl1
where not exists
               (select * from b tbl2
			   where tbl1.id =tbl2.id
               and tbl1.st_code = tbl2.st_code);
quit;
			 

View solution in original post


All Replies
PROC Star
Posts: 552

Re: Removing observations based on two variable after matching with other table

[ Edited ]

What variables do you want this selection to depend on? Both st_code and ID?

 

If so

 

data a;
input st_code Addr$ Name$ ID;
datalines;
06111 kota atul 552927
08888 delh gaur 552927
13089 pune pink 666666
;
run;

data b;
input st_code ID;
datalines;
06111 552927
13089 552927
13135 552927
;
run;

proc sort data = a;
	by ID st_code;
run;

proc sort data = b;
	by ID st_code;
run;

data a;
	merge a b(in=in_b);
	by ID st_code;
	if not in_b then output;
run;
Trusted Advisor
Posts: 1,131

Re: Removing observations based on two variable after matching with other table

Please sort a and b datasets on st_code and id and then merge

 

data want;
merge a(in=a) b(in=b);
by st_code id;
if b and not a;
run;
Thanks,
Jag
Contributor
Posts: 60

Re: Removing observations based on two variable after matching with other table

Is there is any other, I tried this one before but it is not removing all the observation from A, which are present in B (on basis of both variable st_code & ID).

 

I think its due to  I've multiple observation of st_code & multiple IDs 

PROC Star
Posts: 552

Re: Removing observations based on two variable after matching with other table

Please post what you want your final dataset to look like, because the way I see it, the only observation from A, which is also in B (based on both st_code and ID) is the first observation in A, which is removed when you run the code above.

Trusted Advisor
Posts: 1,131

Re: Removing observations based on two variable after matching with other table

proc sql is an alternative

 

data a;
input st_code Addr$ Name$ ID;
datalines;
06111 kota atul 552927
08888 delh gaur 552927
13089 pune pink 666666
;
run;

data b;
input st_code ID;
datalines;
06111 552927
13089 552927
13135 552927
;
run;

proc sql;
create table test as select distinct a.* from a right join b on a.st_code^=b.st_code and a.id^=b.id having a.id ne .;
quit;
Thanks,
Jag
Solution
‎06-13-2017 04:45 AM
PROC Star
Posts: 253

Re: Removing observations based on two variable after matching with other table

just another way

proc sql;
create table abc as 
select * from a tbl1
where not exists
               (select * from b tbl2
			   where tbl1.id =tbl2.id
               and tbl1.st_code = tbl2.st_code);
quit;
			 
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 170 views
  • 0 likes
  • 4 in conversation