DATA Step, Macro, Functions and more

Where statement for a specific values

Accepted Solution Solved
Reply
Contributor
Posts: 38
Accepted Solution

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.typequarter
11802130/06/2012
11802230/06/2012
11802130/09/2012
11802230/09/2012
11802131/12/2012
11802231/12/2012
11802131/03/2013
11802231/03/2013
12137130/06/2012
12137130/09/2012
12137131/12/2012
12137131/03/2013
11326230/06/2012
11326230/09/2012
11326231/12/2012
11326231/03/2013

Accepted Solutions
Solution
‎11-30-2014 11:27 AM
Respected Advisor
Posts: 3,124

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,204

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
Respected Advisor
Posts: 3,124

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...


Respected Advisor
Posts: 3,124

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.

Respected Advisor
Posts: 3,124

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 538 views
  • 3 likes
  • 3 in conversation