proc sql where not equal

[sasphd]

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;

[sasphd]

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

[SASKiwi]

Change "not =" to "ne". 

[Reeza · Accepted solution]

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;

---

[ballardw]

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"?