topic Re: Where clause in SAS Programming

Where clause

Babloo — Thu, 04 Dec 2014 13:52:22 GMT

Please suggest why this where clause in data step not working as i expect.

data want (Where = (name ne 'balaji') );

input name $ age;

datalines ;

ramkuma 26

chandra 27

balaji 33

;

run;

I need output as

ramkuma 26

chandra 27

Re: Where clause

Loko — Thu, 04 Dec 2014 14:08:57 GMT

Hello,

What do you mean it does not work as expected?

Please also check this page where there is a comparison betwen where expresiion and subsetting if:

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a001000521.htm

Re: Where clause

RW9 — Thu, 04 Dec 2014 14:13:41 GMT

Your putting the where clause in the wrong place. Where statements like that can be put on the set part of a datastep. In your case as there is not a set clause you would need to do:

data want;

input....;

where name ne 'balaji';

datalines;

...;

Alternatively you could also do:

data want;

input ...;

if name ne 'balaji' then output;

datalines...

Re: Where clause

CTorres — Thu, 04 Dec 2014 14:15:31 GMT

I submited your code and it worked ok for me: I got two observations:

1    data want (Where = (name ne 'balaji') );
2      input name $ age;
3      datalines ;

NOTE: The data set WORK.WANT has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.04 seconds

7 ;
8 run;

CTorres

Re: Where clause

Babloo — Thu, 04 Dec 2014 14:21:52 GMT

Thanks for your response.

I tried in both ways, but it is not working (EG 5.1). See my code below.

data want;

input name $ age;

/* where name ne 'balaji';*/

if name ne 'balaji' then output;

datalines ;

ramkuma 26

chandra 27

balaji 33

;

run;

Re: Where clause

CTorres — Thu, 04 Dec 2014 14:29:10 GMT

Your first code does not work : ERROR: No input data sets available for WHERE Statement.

The second one does work.

CTorres

Re: Where clause

Loko — Thu, 04 Dec 2014 14:31:28 GMT

It would help if you describe what is written in the log.

Both your codes work correctly .

Re: Where clause

CTorres — Thu, 04 Dec 2014 14:33:01 GMT

I am using SAS Base and I don't see any problem in your code. Please show the EG log.

Re: Where clause

RW9 — Thu, 04 Dec 2014 14:34:18 GMT

Yes, apologies, never seen a where on a data line before. It should work correctly. I agree, post the log.

Re: Where clause

Babloo — Thu, 04 Dec 2014 15:06:06 GMT

Please see the log below.

data want;

18 input name $ age;

19 /* where name ne 'balaji';*/

20 if name ne 'balaji' then output;

21 datalines ;

NOTE: Compression was disabled for data set WORK.WANT because compression overhead would increase the size of the data set.

NOTE: The data set WORK.WANT has 3 observations and 2 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

25 ;

26 run;

Re: Where clause

Neetish — Thu, 04 Dec 2014 15:12:32 GMT

Yes your second statement must work.....logically you are saying to SAS that if name is not equal to Balaji then show output. Since out of three observation those name not equal to Balaji are shown...and there is nothing wrong with second syntax.

Re: Where clause

Babloo — Thu, 04 Dec 2014 15:19:31 GMT

I've inputted following values under the name variable.

ramkuma

chandra

balaji

But I got output of all the three observations instead of two. I ran the program as mentioned in my previous post. Can somebody send me the screenshot along with code after running in EG t?

Re: Where clause

RW9 — Thu, 04 Dec 2014 15:24:06 GMT

Sorry, there must be something different in you data. I have just run this and it works fine, i.e. only 2 records read. Post your exact code if still not working:

data want;

input name $ age;

if name ne 'balaji' then output;

datalines ;

ramkuma 26

chandra 27

balaji 33

;

run;

Re: Where clause

MikeZdeb — Thu, 04 Dec 2014 21:21:13 GMT

Hi ... re "Your putting the where clause in the wrong place." ... that WHERE clause is fine in that location and does work since there is an output data set ...

data want (where = (name ne 'balaji'));

input name $ age;

datalines;

ramkuma 26

chandra 27

balaji 33

;

LOG ...

data want (Where = (name ne 'balaji') );

4 input name $ age;

5 datalines ;

NOTE: The data set WORK.WANT has 2 observations and 2 variables.

NOTE: DATA statement used (Total process time):

also, re "Where statements like that can be put on the set part of a data step. In your case as there is not a set clause you would need to do:" ... that won't work given there's no data set yet ...

data want;

input name $ age;

where name ne 'balaji';

datalines;

ramkuma 26

chandra 27

balaji 33

;

LOG ...

22 data want;

23 input name $ age;

24 where name ne 'balaji';

ERROR: No input data sets available for WHERE statement.

25 datalines;

NOTE: The SAS System stopped processing this step because of errors.