DATA Step, Macro, Functions and more

Mysterious In Function

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

Mysterious In Function

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.


Accepted Solutions
Solution
‎02-05-2018 10:13 AM
Super User
Posts: 6,543

Re: Mysterious In Function

Posted in reply to bigbigben

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;

View solution in original post


All Replies
Super User
Posts: 9,611

Re: Mysterious In Function

Posted in reply to bigbigben

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎02-05-2018 10:13 AM
Super User
Posts: 6,543

Re: Mysterious In Function

Posted in reply to bigbigben

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;

SAS Employee
Posts: 116

Re: Mysterious In Function

Posted in reply to bigbigben

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;
Esteemed Advisor
Posts: 5,403

Re: Mysterious In Function

Posted in reply to bigbigben

Keep everything well rounded with

 

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

 

PG
Super User
Posts: 9,611

Re: Mysterious In Function

Posted in reply to bigbigben

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Community Manager
Posts: 3,362

Re: Mysterious In Function

Posted in reply to KurtBremser

In addition to the good practice info from @KurtBremser (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;
Contributor
Posts: 37

Re: Mysterious In Function

Posted in reply to ChrisHemedinger

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

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 216 views
  • 8 likes
  • 6 in conversation