Desktop productivity for business analysts and programmers

Where not in deleting all

Reply
Contributor
Posts: 42

Where not in deleting all

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 

Contributor
Posts: 21

Re: Where not in deleting all

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?

Super User
Super User
Posts: 8,278

Re: Where not in deleting all

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?

Contributor
Posts: 42

Re: Where not in deleting all

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

 

 

Respected Advisor
Posts: 3,271

Re: Where not in deleting all

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.

--
Paige Miller
Super User
Super User
Posts: 8,278

Re: Where not in deleting all

[ Edited ]

@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
  )
;
Super User
Posts: 10,849

Re: Where not in deleting all

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; 
Super User
Posts: 10,849

Re: Where not in deleting all

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; 
Ask a Question
Discussion stats
  • 7 replies
  • 89 views
  • 1 like
  • 5 in conversation