Thank you, @Ksharp, for pointing this out.
Now I am puzzled. Is this a known bug in PROC SQL? Is it limited to the ROUND function? I haven't found a related problem note (such as https://support.sas.com/kb/69/271.html).
Here's a boiled down example using two very simple, identical datasets A and B:
data a b;
input x;
cards;
1
2
;
proc sql;
create table c1 as
select .
from a, b
where round(a.x)-round(b.x)=0;
create table c2 as
select .
from a, b
where round(a.x,1)-round(b.x,1)=0;
create table c3 as
select a.*
from a, b
where round(a.x,1)=round(b.x,1);
create table c4 as
select a.*
from a, b
where round(a.x,1)-round(b.x,1)=0;
quit;
Log of the PROC SQL step from Windows SAS 9.4M5:
8 proc sql;
9 create table c1 as
10 select .
11 from a, b
12 where round(a.x)-round(b.x)=0;
NOTE: Table WORK.C1 created, with 2 rows and 1 columns.
13 create table c2 as
14 select .
15 from a, b
16 where round(a.x,1)-round(b.x,1)=0;
NOTE: Table WORK.C2 created, with 0 rows and 1 columns.
17 create table c3 as
18 select a.*
19 from a, b
20 where round(a.x,1)=round(b.x,1);
NOTE: Table WORK.C3 created, with 2 rows and 1 columns.
21 create table c4 as
22 select a.*
23 from a, b
24 where round(a.x,1)-round(b.x,1)=0;
ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
The SAS task name is [SQL]
ERROR: Read Access Violation SQL
Exception occurred at (0A745C66)
Task Traceback
Address Frame (DBGHELP API Version 4.0 rev 5)
000000000B1E1E8C 000000000B3BF148 0001:0000000000000E8C tkeavl.dll
000000000AE391D4 000000000B3BF258 sassqx:tkvercn1+0x38194
000000000AE0F150 000000000B3BF420 sassqx:tkvercn1+0xE110
000000000AE18EAA 000000000B3BF550 sassqx:tkvercn1+0x17E6A
000000000AE17EA5 000000000B3BF7F0 sassqx:tkvercn1+0x16E65
000000000AE19B09 000000000B3BF850 sassqx:tkvercn1+0x18AC9
000000000AE3D429 000000000B3BF8B0 sassqx:tkvercn1+0x3C3E9
000000000AE356F5 000000000B3BF9D0 sassqx:tkvercn1+0x346B5
000000000AE34B6E 000000000B3BFB40 sassqx:tkvercn1+0x33B2E
000000000ADE13B2 000000000B3BFB48 sassql:tkvercn1+0x372
00000000038CA066 000000000B3BFBE8 sashost:Main+0x11BA6
00000000038D011D 000000000B3BFF50 sashost:Main+0x17C5D
00000000777E556D 000000000B3BFF58 kernel32:BaseThreadInitThunk+0xD
000000007794372D 000000000B3BFF88 ntdll:RtlUserThreadStart+0x1D
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.A.
NOTE: There were 2 observations read from the data set WORK.B.
WARNING: The data set WORK.C4 may be incomplete. When this step was stopped there were 0 observations and 1 variables.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.06 seconds
cpu time 0.06 seconds
25 quit;
Specifying 1 as the rounding unit in the second argument of the ROUND function should be redundant. However, in the WHERE condition of the form "difference = 0" it produces an incorrect result in table C2 (0 rows instead of 2) and a Read Access Violation error while creating table C4.
... View more
