Mysterious In Function

[bigbigben]

I tried using in function to find observation values matching any value of a given series; however, SAS does not always give me the correct answer and the mistakes seem to happen when the give numbers are in decimals.

Here is a piece of sample code:

%let nodes=-0.1 0 0.1 0.2;

%let x_min=-0.2;
%let x_max=0.2;
%let x_step=0.01;

data xxx(keep=x nodes_flag);
    nstep=round((&x_max-&x_min)/&x_step);
	do iter=0 to nstep;
	nodes_flag=0;
	x=&x_min+&x_step*iter;
	if x in (&nodes) then nodes_flag=1;
	output;
	end;
run;

data nodes;
  set xxx;
  if nodes_flag=1;
  run;
  proc print data=nodes;
  run;

The dataset "nodes" is suppose to capture the x values if it is any of (-0.1 0 0.1 0.2); however, below is what SAS gives me:

nodes_flag x

1    -0.1
1    0
1    0.2

It missed 0.1.

If I change the set values to -0.1 0 0.1, SAS gives me:

1    -0.1
1    0

It still missed 0.1.

When I change the set values to be 0.1 0.05 0, SAS can only select 0.

Can some one explain how the function works and what I did wrong?

Many thanks.

[Kurt_Bremser]

You're doing math with fractions, which can (and will) cause artifacts because of the limitations of the 8-byte real format SAS uses for numbers. Apply the round() function before comparing.

[Astounding · Accepted solution]

The safest way to eliminate the issue is to deal with integers the whole time.  For example:

%let x = -10 0 10 20;

%let x_min = -20;

%let x_max = 20;

%let x_step = 1;

Then just before the OUTPUT statement:

x = x / 100;

[SASJedi]

SAS numeric values are all double-precision floating point data types, which guarantee only an approximate representation of decimal data. The issues with representing decimal values in floating point are common to all computing platforms.  For a deep dive, check Numerical Accuracy in SAS Software. So, to resolve your problem keeping the values as-is, you need a different way of representing numeric values.

Fortunately for you, Base SAS comes with the DS2 language, which is capable of handling DECIMAL fixed-point numerics values. Decimal values provide exact representation of the decimal values.  This DS2 code will provide the results you seek:

%let nodes=-0.1 0 0.1 0.2;
%let x_min=-0.2;
%let x_max=0.2;
%let x_step=0.01;

proc ds2;
data xxx/overwrite=yes;
   dcl decimal(5,2) x;
   dcl int nodes_flag; 
   method run();
      dcl int nstep iter;
      nstep=round((&x_max-&x_min)/&x_step);
   	do iter=0 to nstep;
            nodes_flag=0;
            x=&x_min+&x_step*iter;
            if x in (&nodes) then nodes_flag=1;
            output;
   	end;
   end;
enddata;
run;
quit;

data nodes;
   set xxx;
   if nodes_flag=1;
run;
proc print data=nodes;
run;

[PGStats]

Keep everything well rounded with

x=round(&x_min+&x_step*iter, &x_step);

[Kurt_Bremser]

Here a slightly optimized and visually structured version of your code, and expanded with the round() function:

%let nodes=-0.1 0 0.1 0.2;

%let x_min=-0.2;
%let x_max=0.2;
%let x_step=0.01;

data nodes (
  keep=x nodes_flag
  where=(nodes_flag = 1)
);
  nstep = round((&x_max. - &x_min.) / &x_step.);
	do iter = 0 to nstep;
	  nodes_flag = 0;
	  x = round(&x_min. + &x_step. * iter, .01);
	  if x in (&nodes.) then nodes_flag = 1;
	  output;
	end;
run;

proc print data=nodes noobs;
run;

Result:

nodes_
 flag        x

   1      -0.1
   1       0.0
   1       0.1
   1       0.2

[ChrisHemedinger]

In addition to the good practice info from @Kurt_Bremser (about rounding) and DECIMAL precision tidbits by @SASJedi, I'll offer this.  The IN function is one of those lightly documented functions that many users try to squeeze more uses from than maybe they should.  It's a natural temptation for those familiar with the IN clause in SQL -- but that works differently.

Instead, you might be able to get what you want, precision aside, by comparing the formatted values you seek.  Here's your original code adjusted to use of the WHICHN function (which is documented thoroughly).  Note that I added commas to your delimited list of nodes to generate the proper syntax.

%let nodes=-0.1, 0, 0.1, 0.2;

%let x_min=-0.2;
%let x_max=0.2;
%let x_step=0.01;

data xxx(keep=x nodes_flag);
    nstep=round((&x_max-&x_min)/&x_step);
	do iter=0 to nstep;
	nodes_flag=0;
	x=&x_min+&x_step*iter;
	if whichn(put(x,best12.), &nodes) then nodes_flag=1;
	output;
	end;
run;

data nodes;
  set xxx;
  if nodes_flag=1;
  run;
  proc print data=nodes;
  run;

[bigbigben]

Thank you all. All these solutions taught me something about SAS. I really appreciate all the help.