topic Re: proc sql where not equal in SAS Procedures

proc sql where not equal

sasphd — Mon, 10 Jan 2022 19:58:16 GMT

hello

not= does not work in my program. what is the error? thanks

roc sql;
create table stat_data as
select * from Penality
where
specific_Industry_of_Parent not = 'mortgage lending' or specific_Industry_of_Parent not = "commodity trading; mining" or specific_Industry_of_Parent not= 'banking'
or specific_Industry_of_Parent not = "insurance" or specific_Industry_of_Parent not = "financial services" or specific_Industry_of_Parent not = "banking & securities"
or specific_Industry_of_Parent not = "investment advisory services";

run;

Re: proc sql where not equal

sasphd — Mon, 10 Jan 2022 20:03:31 GMT

there is no error in the log but the input table did not change

Re: proc sql where not equal

SASKiwi — Mon, 10 Jan 2022 20:11:37 GMT

Change "not =" to "ne".

Re: proc sql where not equal

Reeza — Mon, 10 Jan 2022 21:43:02 GMT

Use NOT IN instead of NOT =

And SQL requires a QUIT, not RUN.

I'd also recommend using either single or double quote consistently but that's not something I excel at either 🙂

Something like this is a lot cleaner IMO:

proc sql;
create table stat_data as
select * from Penality
where
specific_Industry_of_Parent not in ( 
'mortgage lending', 
 "commodity trading; mining", 
 'banking',
 "insurance",
 "financial services",
 "banking & securities", 
 "investment advisory services");
quit;

@sasphd wrote:

hello

not= does not work in my program. what is the error? thanks

roc sql;
create table stat_data as
select * from Penality
where
specific_Industry_of_Parent not = 'mortgage lending' or specific_Industry_of_Parent not = "commodity trading; mining" or specific_Industry_of_Parent not= 'banking'
or specific_Industry_of_Parent not = "insurance" or specific_Industry_of_Parent not = "financial services" or specific_Industry_of_Parent not = "banking & securities"
or specific_Industry_of_Parent not = "investment advisory services";

run;

Re: proc sql where not equal

ballardw — Thu, 13 Jan 2022 17:17:15 GMT

Since this seems to select as expected:

proc sql;
   create table junk as
   select * from sashelp.class
   where sex not = 'F'
   ;
quit;

perhaps the issue is with the spelling of your values. = would be case and space sensitive.

Run this and show the result:

proc sql;
   select distinct specific_industry_of_Parent
   from Penality
  ;
quit;

Any chance the data set is actually named "Penalty" and not "Penality"?