Hi,
Can anyone suggest reasons why my PROC SQL WHERE statement isn't recognising all variable values?
I'm using Base SAS v9.4.
I'm trying to create a new table from an old table using PROC SQL. I have a WHERE statement that requires the variable SLK_B to equal 1.38 in order to be included in the new table.
While I can see that this value is definitely present in the variable SLK_B in the old table (both visually and by doing a PROC FREQ), it does not seem to be being picked up when creating the new table. Some values work in the WHERE statement and return the accurate number of rows, and others (like 1.38) don't. I have checked the dataset and variable names are accurate.
SLK_B is a numeric variable, length = 8, format = BEST12., informat = 12.
My PROC SQL is as follows and returns 0 observations despite me being able to see that the value is present:
proc sql;
create table new as
select *
from old
where slk_b = 1.38;
quit;
Thanks for your time!
This is probably due to rounding error. Your values are not exactly equal to 1.38. try something like
where slk_b between 1.3799 and 1.3801
or
where round(slk_b*100) = 138
This is probably due to rounding error. Your values are not exactly equal to 1.38. try something like
where slk_b between 1.3799 and 1.3801
or
where round(slk_b*100) = 138
@Count wrote:
I had assumed because of how I had created the variables that it could only possible be two decimal places, ...
Hi @Count,
Numeric representation error is often the root cause of such problems.
Example:
data test;
v=1.38; /* (1) see explanations below */
w=6.9; /* (2) */
x=w/5; /* (3) */
r=round(w/5, 1e-9); /* (4) */
put x best32.-l; /* (5) */
put x 32.30; /* (6) */
if x ne 1.38 then put "Surprised?";
put (x v r) (/ =hex16.); /* (7) */
d=x-v; /* (8) */
put / d=; /* (9) */
run;
Result in the log (SAS 9.4M5 on Windows 7):
1.38 1.380000000000000000000000000000 Surprised? x=3FF6147AE147AE15 v=3FF6147AE147AE14 r=3FF6147AE147AE14 d=2.220446E-16
Thank you @FreelanceReinh . I feel a bit sick that I was ignorant of this! For now, I've used the ROUND function to two decimals throughout my data sets and now I'm off to read more on the issue!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.