topic Re: Where not in deleting all in SAS Enterprise Guide

Where not in deleting all

hk2013 — Tue, 17 Jul 2018 17:48:03 GMT

proc sql; 
create table want as 
select *  
from have
where MEM_ID not in (select MEM_ID from have1);
run;

i have MEM_ID and item_id in both tables the sas code deletes all MEM_ID however i want to match both MEM_ID and item_id not just MEM_ID as the code i have is delecting data i dont want to delete

Re: Where not in deleting all

Tommy1 — Tue, 17 Jul 2018 19:23:48 GMT

It is a little confusing what you are trying to do. Are you trying to delete all the observation where MEM_id is not equal to item_id?

Re: Where not in deleting all

Tom — Tue, 17 Jul 2018 20:38:25 GMT

If you are getting an empty result set then that means that all of the MEM_ID values in HAVE are also in HAVE1.

Basically HAVE is a subset of HAVE1, at least in terms of MEM_ID values.

Perhaps you meant to do the test in the other way? To find the records that are in HAVE1 but not in HAVE?

Perhaps your MEM_ID values are all missing? Or some other constant ? Or truncated so that they match artificially?

Re: Where not in deleting all

hk2013 — Tue, 17 Jul 2018 21:24:03 GMT

Have1 is the data i dont want in my 'Have' data set but deleting by just MEM_ID it is deleting MEM_ID data thats not in Have1

example MEM_ID 1 and item_id A are in HAVE1 and also in HAVE so i want to delete it however MEM_ID 1 has item_id B and C which i want to keep but because of my code they are being deleted based on mem_ID

Re: Where not in deleting all

PaigeMiller — Tue, 17 Jul 2018 21:33:02 GMT

Show us an example of your data, and an example of what you want to see in the results.

Since we're trying to help you, could you please help us out as well? Please type in actual sentences, with a period at the end, and a capitalized letter to start the next sentence, and you know, follow ALL the rules of proper writing. Thanks.

Re: Where not in deleting all

Ksharp — Wed, 18 Jul 2018 14:02:45 GMT

proc sql; 
create table want as 
select *  
from have
where catx(' ',MEM_ID,item_id) not in (select catx(' ',MEM_ID,item_id) from have1);
run;

Re: Where not in deleting all

Ksharp — Wed, 18 Jul 2018 14:06:00 GMT

proc sql; 
create table want as 
select *  
from have
where catx(' ',MEM_ID,item_id) not in (select catx(' ',MEM_ID,item_id) from have1);
run;

Re: Where not in deleting all

Tom — Wed, 18 Jul 2018 14:15:52 GMT

@hk2013 wrote:

Have1 is the data i dont want in my 'Have' data set but deleting by just MEM_ID it is deleting MEM_ID data thats not in Have1

example MEM_ID 1 and item_id A are in HAVE1 and also in HAVE so i want to delete it however MEM_ID 1 has item_id B and C which i want to keep but because of my code they are being deleted based on mem_ID

That is a totally different question.

You would probably find it much easier to use normal SAS instead of trying to get SQL to do what you want.

data want ;
  merge have1 (in=in1) have2(in=in2 keep=mem_id item_id);
  by mem_id item_id ;
  if in1 and not in2;
run;

In SQL it might be more appropriate to use EXIST

proc sql ;
create table want as 
select * from have1 
where not exist
  (select 1 
    from have2
    where have1.mem_id=have2.mem_id 
      and have1.item_id = have2.item_id
  )
;