topic Re: Removing observations based on two variable after matching with other table in SAS Programming

Removing observations based on two variable after matching with other table

atul_desh — Mon, 12 Jun 2017 13:22:58 GMT

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;

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

PeterClemmensen — Mon, 12 Jun 2017 13:31:02 GMT

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;

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

Jagadishkatam — Mon, 12 Jun 2017 13:30:19 GMT

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;

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

atul_desh — Mon, 12 Jun 2017 13:40:41 GMT

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

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

PeterClemmensen — Mon, 12 Jun 2017 13:56:01 GMT

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.

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

Jagadishkatam — Mon, 12 Jun 2017 14:07:58 GMT

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;

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

kiranv_ — Mon, 12 Jun 2017 17:31:49 GMT

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;