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;
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;
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;
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;
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
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.
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.