topic Re: Mysterious In Function in SAS Programming

Mysterious In Function

bigbigben — Sun, 28 Jan 2018 05:32:33 GMT

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.

Re: Mysterious In Function

Kurt_Bremser — Sun, 28 Jan 2018 07:15:20 GMT

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.

Re: Mysterious In Function

Astounding — Sun, 28 Jan 2018 13:26:04 GMT

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;

Re: Mysterious In Function

SASJedi — Sun, 28 Jan 2018 16:06:42 GMT

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;

Re: Mysterious In Function

PGStats — Mon, 29 Jan 2018 04:42:11 GMT

Keep everything well rounded with

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

Re: Mysterious In Function

Kurt_Bremser — Mon, 29 Jan 2018 07:58:59 GMT

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

Re: Mysterious In Function

ChrisHemedinger — Mon, 29 Jan 2018 13:05:15 GMT

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;

Re: Mysterious In Function

bigbigben — Mon, 05 Feb 2018 15:28:58 GMT

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