DATA Step, Macro, Functions and more

Where clause

Accepted Solution Solved
Reply
Super Contributor
Posts: 436
Accepted Solution

Where clause

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


Accepted Solutions
Solution
‎12-04-2014 10:24 AM
Super User
Super User
Posts: 7,970

Re: Where clause

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;

View solution in original post


All Replies
Super Contributor
Posts: 308

Re: Where clause

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

Super User
Super User
Posts: 7,970

Re: Where clause

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

Super Contributor
Posts: 436

Re: Where clause

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;

Regular Contributor
Posts: 180

Re: Where clause

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

Regular Contributor
Posts: 180

Re: Where clause

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

The second one does work.

CTorres

Valued Guide
Posts: 765

Re: Where clause

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.

Regular Contributor
Posts: 180

Re: Where clause

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

Super Contributor
Posts: 308

Re: Where clause

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

Both your codes work correctly .

Super User
Super User
Posts: 7,970

Re: Where clause

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

Super Contributor
Posts: 436

Re: Where clause

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;

Solution
‎12-04-2014 10:24 AM
Super User
Super User
Posts: 7,970

Re: Where clause

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;

N/A
Posts: 1

Re: Where clause

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.

Super Contributor
Posts: 436

Re: Where clause

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?

🔒 This topic is solved and locked.

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

Discussion stats
  • 13 replies
  • 483 views
  • 0 likes
  • 6 in conversation