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
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.
Can you simplify the logic to:
proc sql;
create table want as
select * from one where one='one' and two='two'
;quit;
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.
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
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.
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,
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.