topic Re: proc sql to get the desired records in SAS Programming

proc sql to get the desired records

buddha_d — Sun, 02 Feb 2020 04:56:13 GMT

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

Re: proc sql to get the desired records

unison — Sun, 02 Feb 2020 05:00:28 GMT

Can you simplify the logic to:

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

Re: proc sql to get the desired records

buddha_d — Sun, 02 Feb 2020 05:13:08 GMT

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.

Re: proc sql to get the desired records

buddha_d — Sun, 02 Feb 2020 05:17:37 GMT

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

Re: proc sql to get the desired records

unison — Sun, 02 Feb 2020 06:08:56 GMT

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.

Re: proc sql to get the desired records

ed_sas_member — Sun, 02 Feb 2020 08:41:15 GMT

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,

Re: proc sql to get the desired records

buddha_d — Tue, 04 Feb 2020 02:18:08 GMT

Thanks this works too