BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Babloo
Rhodochrosite | Level 12

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

13 REPLIES 13
Loko
Barite | Level 11

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Babloo
Rhodochrosite | Level 12

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;

CTorres
Quartz | Level 8

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

CTorres
Quartz | Level 8

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

The second one does work.

CTorres

MikeZdeb
Rhodochrosite | Level 12

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.

CTorres
Quartz | Level 8

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

Loko
Barite | Level 11

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

Both your codes work correctly .

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Babloo
Rhodochrosite | Level 12

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

Neetish
Calcite | Level 5

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.

Babloo
Rhodochrosite | Level 12

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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