topic Re: How to use the 'Not in' operator in proc step where they are two datasets? in SAS Programming

How to use the 'Not in' operator in proc step where they are two datasets?

Shradha1 — Thu, 24 Jun 2021 13:50:17 GMT

I want to select only those observations from a table (Final_lIst) which don't belong in any of the two other tables (supp_data and ntb_data)

Will this code work?

proc sql;
create table Final_list as

select * from final_list

where customer_number

not in
(select distinct customer_number from supp_data and ntb_data ) ;
quit;

Re: How to use the 'Not in' operator in proc step where they are two datasets?

Reeza — Thu, 24 Jun 2021 13:54:34 GMT

Unfortunately not, but this may ... if it doesn't post the log.

proc sql;
create table Final_list as

select * from final_list

where customer_number

not in
(select distinct customer_number from supp_data union select distinct customer_number from ntb_data  ) ;
quit;

Re: How to use the 'Not in' operator in proc step where they are two datasets?

Tom — Thu, 24 Jun 2021 14:06:10 GMT

You have the AND in the wrong place.

where customer_number not in (select distinct customer_number from supp_data)
  and customer_number not in (select distinct customer_number from ntb_data)