topic Re: using IN operator in SAS Programming

using IN operator

Ronein — Tue, 24 Dec 2019 05:41:26 GMT

Hello

I want to choose only rows with following condition:

group=835

OR group=703

OR group=705

OR group=853

OR group=855

OR group between 100 and 199

OR group between 300 and 399

Is there option to write it in IN operator?

In this way (using IN operator) I get wrong results

group IN(835,703,705,853,855,100-199,300-399)

data tbl1;
input ID group;
cards;
1 835
2 835
3 703 
4 703
5 100
6 101
7 199
8 200
9 300
10 500
11 399
;
run;

Data tbl2;
SET tbl1(where=(group IN(835,703,705,853,855,100-199,300-399)));
Run;

Re: using IN operator

Patrick — Fri, 27 Dec 2019 00:53:58 GMT

The valid character to define a range is a colon and not a dash.

If you run the code you've posted you'll see in the SAS log that it resolves to below.

WHERE group in (-399, -199, 100, 300, 703, 705, 835, 853, 855);

...which explains the unexpected result. It appears SAS doesn't require a comma in the list but just treats each value separately.

If you use the colon for defining the ranges.

set tbl1(where=(group in(835,703,705,853,855,100:199,300:399)));

...then you get in the SAS log the following resolution which should return what you expect.

      WHERE group in (703, 705, 835, 853, 855) or ((group=INT(group)) and ((group>=100 and group<=199) or (group>=300 and 
      group<=399)));

Alternatively you could use a format for selection.

proc format;
  value validVals(default=1)
    835,703,705,853,855 = '1'
    100-199,300-399 ='1'
    other='0'
  ;
run;

data tbl2;
  set tbl1(where=(put(group,validVals.)='1'));
run;

Re: using IN operator

Ksharp — Tue, 24 Dec 2019 11:49:51 GMT


Data tbl2;
SET tbl1(where=(group IN (835,703,705,853,855,100:199,300:399)));
Run;

Re: using IN operator

Tom — Tue, 24 Dec 2019 15:12:01 GMT

If you write 100-199 that will evaluate to the value -99. Are the values of GROUP only integers? If so you can use colon to make a range of integers, like this 100:199.

Re: using IN operator

ballardw — Tue, 24 Dec 2019 17:27:41 GMT

@Ronein may also need to know that the : is only for a range of INTEGER values.

If you expect the IN to catch a value like 101.3 then you need to explicitly list it.

Which may make the format approach more appealing if you have lots of decimal values.