BookmarkSubscribeRSS Feed
hk2013
Fluorite | Level 6
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 

7 REPLIES 7
Tommy1
Quartz | Level 8

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?

Tom
Super User Tom
Super User

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?

hk2013
Fluorite | Level 6

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

 

 

PaigeMiller
Diamond | Level 26

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
Tom
Super User Tom
Super User

@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
  )
;
Ksharp
Super User
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; 
Ksharp
Super User
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; 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 917 views
  • 1 like
  • 5 in conversation