Solved
Contributor
Posts: 38

Where statement for a specific values

Hello,

what I'm, trying to do in this file is to eliminate only the rows in which company_no= 11802 and type=2 (colored in pink).

its look like a simple "where statement" should work but I havn't succeed  to formulate the right statement yet . Any idea?

Thanks, Lior

 company_no. type quarter 11802 1 30/06/2012 11802 2 30/06/2012 11802 1 30/09/2012 11802 2 30/09/2012 11802 1 31/12/2012 11802 2 31/12/2012 11802 1 31/03/2013 11802 2 31/03/2013 12137 1 30/06/2012 12137 1 30/09/2012 12137 1 31/12/2012 12137 1 31/03/2013 11326 2 30/06/2012 11326 2 30/09/2012 11326 2 31/12/2012 11326 2 31/03/2013

Accepted Solutions
Solution
‎11-30-2014 11:27 AM
Posts: 3,167

Re: Where statement for a specific values

Proc SQL is the way to go. Step by step, First, I would think how to extract these obs, then just do the opposite by using NOT.

proc sql;

create table want as

select * from have where NOT (company_no=11802 and type=2);

quit;

All Replies
Posts: 1,270

Re: Where statement for a specific values

proc sql;

create table want(drop=flag) as

select *,case when company_no=11802 and type=2 then 1 else 0 end as flag from have

where calculated flag=0;

quit;

Contributor
Posts: 38

Re: Where statement for a specific values

Hi,

thanks for the answer. the statement makes sense but when I run this statement I'm getting file "want" which is identical to the file "have". meaning I havn't succeed to eliminate the rows where

company_no=11802 and type=2.

I don't know what I'm doing wrong...

Solution
‎11-30-2014 11:27 AM
Posts: 3,167

Re: Where statement for a specific values

Proc SQL is the way to go. Step by step, First, I would think how to extract these obs, then just do the opposite by using NOT.

proc sql;

create table want as

select * from have where NOT (company_no=11802 and type=2);

quit;

Contributor
Posts: 38

Re: Where statement for a specific values

Hi,

thanks for the answer. the statement makes sense but when I run this statement I'm getting file "want" which is identical to the file "have". meaning I havn't succeed to eliminate the rows where

company_no=11802 and type=2.

I don't know what I'm doing wrong...

Posts: 3,167

Re: Where statement for a specific values

One possibility is that the variable type. If your company_no AND/OR type is char, then you need to put quotes around the value:

company_no='11802' and type='2'

Contributor
Posts: 38

Re: Where statement for a specific values

its indeed char, and I already put quotes around the value.

but the results are the same .

by the way when revewing the log, everything is O.K (there is no error messege in the log) I just keep getting an adentical file.

Posts: 3,167

Re: Where statement for a specific values

if both variables are chars, make sure there aren't invisible 'things' (such as blank, tab etc) in front of the visible content, try :

left(company_no)='11802' and left(type)='2'

or compress(company_no)='11802' and compress(type)='2'

Contributor
Posts: 38

Re: Where statement for a specific values

Apparently I havn't wrote the values '11802 and '2' in the right way (its should to be '0011802' and '02)'...

after I correct it the statement work fine.

Thanks you very much,

Lior

🔒 This topic is solved and locked.