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;
Data tbl2;
SET tbl1(where=(group IN (835,703,705,853,855,100:199,300:399)));
Run;
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;
Data tbl2;
SET tbl1(where=(group IN (835,703,705,853,855,100:199,300:399)));
Run;
@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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.