proc sql to get the desired records

[buddha_d]

I have the following data set and I want to get the desire data set

data one; 
input num name$ one$ two$ three$ four$ five $ ;
cards;
1 day1 NICE seeinn three four five
2 day1 one two three four3 five
3 day1 nice 1SEEInn2 GFL dlov dfdf 
4 day23 one two thd ruhld hfdlhf
5 dfjdh Nice lseeinn fkdjf djfkd kjklj
6 dhhk one two dkfd alkdf aldkf
7 day1 ONE seeinn thde khf fllj
;
run;

below. 

I want to get these record in my output :

2 day1 one two three four3 five
4 day23 one two thd ruhld hfdlhf
6 dhhk one two dkfd alkdf aldkf

I tried following code, but wasn't successful getting the records I want. 

proc sql;
	create table want as 
	select *
	from one
		where name not in ( select name from one where ((upcase(name)='DAY1' AND UPCASE(ONE)='NICE') OR (upcase(name)='DAY1' AND UPCASE(TWO) LIKE '%SEEINN%')));
QUIT;

THANKS IN ADVANCE

[unison]

Can you simplify the logic to:

proc sql;
create table want as
select * from one where one='one' and two='two'
;quit;

[buddha_d]

Thanks Unison for the reply. But that logic won't work for me. I created a dummy data set out of real data set. Yes, it would work for dummy data set that I provided. But that won't work. Sorry, but thanks anyway. 

[buddha_d]

I need to exclude the records with name (day1) along with field one (NICE) or name (day1) with field two (containing anything with seeinn), but contain all other records. I hope this helps with my criteria 

[unison · Accepted solution]

I suggest pulling out the criteria in a separate select statement to test:

proc sql;
select * from one 
where not(
	(upcase(name)='DAY1' AND UPCASE(ONE)='NICE') OR 
	(upcase(name)='DAY1' AND UPCASE(TWO) LIKE '%SEEINN%')
)
;quit;

Based on your current logic, it looks like there is a case when NAME is not DAY1 and two contains SEEINN.

[ed_sas_member]

Hi @buddha_d 

You can also use the set operator 'EXCEPT' to select rows:

proc sql;
	create table want as
	
	select *
	from one
	
	except
	
	select *
	from one
	where (upcase(name)='DAY1' and upcase(ONE)='NICE') or
	 	  (upcase(name)='DAY1' and upcase(TWO) like '%SEEINN%');
quit;

Best,

[buddha_d]

Thanks this works too