DATA Step, Macro, Functions and more

How to select range of values with decimals?

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

How to select range of values with decimals?

I'm trying to select observations by using a range of values that contain decimals. It's important to use the exact numbers and not round.

This is what I tried:

 

data want; set have;

if variable in (800.0:801.9,803.0:804.9,850.0:854.1,950.1:950.3,995.55,959.01)  then output;run;

 

When I did proc print for an observation that had a value of 850.5 for my variable, it didn't show up.

I also tried using "-" instead of ":" and same thing. Short of writing out every single value, is there any way to make sure SAS will include every value in between each of those ranges?

 

Thanks,

Laura


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 7,050

Re: How to select range of values with decimals?

[ Edited ]
Posted in reply to newsasuser

The START:END shortcut syntax is only for integers.

Either type them out, or use a loop to generate a macro variable with the list.

Using a data step DO loop would be easier since you can list all of the groups in one DO statement.

data _null_;
  length list $5000 ;
  do x=800.0 to 801.9 by 0.1
      ,803.0 to 804.9 by 0.1
      ,850.0 to 854.1 by 0.1
      ,950.1 to 950.3 by 0.1
      ,995.55
      ,959.01
  ;
    list=catx(' ',list,put(x,6.2));
  end;
  call symputx('list',list);
run;

Then you can use the macro variable in your later code.

data want;
  set have;
  if variable in (&list);
run;

Note there is no need to use commas to separate the list of values given the IN() operator. Space delimiters are much easier to use with SAS code since you can pass them as macro parameter values without having to use macro quoting.

View solution in original post


All Replies
Trusted Advisor
Posts: 1,022

Re: How to select range of values with decimals?

Posted in reply to newsasuser

Of course you didn't get an 850.5.  There is no literal 850.5 in your value list.

SAS Super FREQ
Posts: 35

Re: How to select range of values with decimals?

[ Edited ]
Posted in reply to newsasuser

Hi, @newsasuser!

 

The IN operator is meant to be used in character comparisons, as you specify lists of discrete values. This is why you cannot specify numeric ranges. The easiest way to accomplish your goal is to use the >, <, <=, >= operators. 

 

For example, 

data want;
set have;
if variable >= 800.0 and variable <= 800.9 then output; run;

and so on. Let me know if that helps!

 

 

Solution
3 weeks ago
Super User
Super User
Posts: 7,050

Re: How to select range of values with decimals?

[ Edited ]
Posted in reply to newsasuser

The START:END shortcut syntax is only for integers.

Either type them out, or use a loop to generate a macro variable with the list.

Using a data step DO loop would be easier since you can list all of the groups in one DO statement.

data _null_;
  length list $5000 ;
  do x=800.0 to 801.9 by 0.1
      ,803.0 to 804.9 by 0.1
      ,850.0 to 854.1 by 0.1
      ,950.1 to 950.3 by 0.1
      ,995.55
      ,959.01
  ;
    list=catx(' ',list,put(x,6.2));
  end;
  call symputx('list',list);
run;

Then you can use the macro variable in your later code.

data want;
  set have;
  if variable in (&list);
run;

Note there is no need to use commas to separate the list of values given the IN() operator. Space delimiters are much easier to use with SAS code since you can pass them as macro parameter values without having to use macro quoting.

New Contributor
Posts: 3

Re: How to select range of values with decimals?

This worked perfectly, thanks!
New Contributor
Posts: 3

Re: How to select range of values with decimals?

After thinking it worked initially, I've realized that it isn't capturing all the ranges. It's only capturing 850-853.19.

This is the code I used (modified slightly because include hundredth decimal place):

 

data _null_;
length list $5000 ;
do x=800.0 to 801.9 by 0.01
,803.0 to 804.9 by 0.01
,850.0 to 854.1 by 0.01
,950.1 to 950.3 by 0.01
,959.01
,995.55
;
list=catx(' ',list,put(x,6.3));
end;
call symput('list',list);
run;

 

Any thoughts?

Thanks in advance

Super User
Super User
Posts: 7,050

Re: How to select range of values with decimals?

Posted in reply to newsasuser

5 thousand characters is not long enough for such a large list.

Change the length of the character variable, the maximum length is 32767.

Also why are you use 6.3 format instead of 6.2?

Super User
Posts: 11,343

Re: How to select range of values with decimals?

[ Edited ]
Posted in reply to newsasuser

Did you notice the ERROR in the log?

 

From the documentation:

The IN Operator in Numeric Comparisons

You can use a shorthand notation to specify a range of sequential integers to search.
 
I am afraid you are going to have to write something like:
 
if   (800 le variable le 801.9) or (803 le variable le 804.9) or () or ().
 
OR another option is to create a custom format
proc format library=work;
value mykeep
800  - 801.9 ='Keep'
803.0 - 804.9 ='Keep'
850.0 - 854.1 ='Keep'
950.1 - 950.3 ='Keep'
995.55 ='Keep'
959.01='Keep'
other='Do not keep'
;
run;
and use:
If put(variable,Mykeep.)='Keep' then output;
 
The format approach does allow using the options that will keep the endpoints or not and could also allow use of High and LOW ranges values.
Super User
Posts: 5,509

Re: How to select range of values with decimals?

Posted in reply to newsasuser

If your variable never extends beyond 2 decimal places, you could fudge the result easily enough:

 

data want;

set have;

if 100 * variable in (80000:80190, 80300:80490, 85000:85410, 95010:95030, 99555, 95901)  then output;

run;

Trusted Advisor
Posts: 1,022

Re: How to select range of values with decimals?

Posted in reply to Astounding

As per @Astounding,s nifty suggestion.   if the decimals do go beyond two digits, you can use the FLOOR and CEIL functions with his technique:

 

 

data have;

  do variable=801.00,801.1,801.15,801.155,801.90006;

    output;

  end;

run;

data want;

  set have;

  if floor(100 * variable) in

    (80000:80190, 80300:80490, 85000:85410,

 

     95010:95030, 99555      , 95901)

  and ceil(100 * variable) in

    (80000:80190, 80300:80490, 85000:85410,

     95010:95030, 99555      , 95901)

  then output;

run;

Contributor
Posts: 54

Re: How to select range of values with decimals?

Posted in reply to newsasuser
If your criteria is not long you can
use case statement with proc sql :

Case when variable=800 then "1"
when variable between 801.9 and 803 then "1"
.
.
Else "0"
End
☑ This topic is solved.

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

Discussion stats
  • 10 replies
  • 152 views
  • 3 likes
  • 7 in conversation