topic Re: Where statement for a specific values in SAS Programming

Where statement for a specific values

lior — Sun, 30 Nov 2014 08:45:56 GMT

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

Re: Where statement for a specific values

stat_sas — Sun, 30 Nov 2014 13:17:10 GMT

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;

Re: Where statement for a specific values

Haikuo — Sun, 30 Nov 2014 16:27:15 GMT

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;

Re: Where statement for a specific values

lior — Sun, 30 Nov 2014 23:20:23 GMT

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

Re: Where statement for a specific values

lior — Sun, 30 Nov 2014 23:20:59 GMT

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

Re: Where statement for a specific values

Haikuo — Sun, 30 Nov 2014 23:27:19 GMT

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'

Re: Where statement for a specific values

lior — Sun, 30 Nov 2014 23:56:53 GMT

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.

Re: Where statement for a specific values

Haikuo — Mon, 01 Dec 2014 00:11:26 GMT

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'

Re: Where statement for a specific values

lior — Mon, 01 Dec 2014 09:03:37 GMT

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