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
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?
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?
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
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.
@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
)
;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.