Segmentation violation Error in PROC SQL

[Ronein]

Hello

I run this code and get this error

ERROR: An exception has been encountered.
Please contact technical support and provide them with the following traceback information:

2 The SAS System 12:28 Saturday, August 30, 2025

The SAS task name is [SQL (2) ]
Segmentation Violation

Traceback of the Exception:

/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sas(+0x15cfde) [0x55fc22883fde]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sas(+0x4cb7b) [0x55fc22773b7b]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x144) [0x7fee8516a404]
/lib64/libpthread.so.0(+0xf630) [0x7fee863db630]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(__intel_ssse3_rep_memcpy+0x27a0) [0x7fee398afa30]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(sqxcseq+0x28e) [0x7fee39893b6e]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x1b088) [0x7fee3986d088]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x208db) [0x7fee398728db]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x1f9c0) [0x7fee398719c0]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x21410) [0x7fee39873410]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(sqxexec+0x56d) [0x7fee3989289d]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x39681) [0x7fee3988b681]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(sqlloop+0x405) [0x7fee3988ad35]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassql(sassql+0x27a) [0x7fee22ab531a]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sas(vvtentr+0x13d) [0x55fc2277371d]
/lib64/libpthread.so.0(+0x7ea5) [0x7fee863d3ea5]
/lib64/libc.so.6(clone+0x6d) [0x7fee859bfb0d]

proc sql;
create table t_Ind4_b as
select a.*,
b.UUID,
b.productID,
b.APPLICATIONDATE,
b.LoanRequestedAmount,
b.Degem as offer_Degem,
b.LoanMaxAmount as Offer_LoanMaxAmount,
b.LoanMaxDeductAmount as offer_LoanMaxDeductAmount,
b.MinNumPayments as offer_MinNumPayments,
b.MaxNumPayments  as offer_MaxNumPayments,
b.PrimeSpread  as offer_PrimeSpread
from t_Ind4_a(Where=(Approval_Reference_Ind = 1 and Agreement_Account_Id=665845890)) as a
left join DWH_OffersDeclinesDaily_b  as b
on a.Approval_Reference_Nbr=b.ConsentId
   and a.Numerator_DWH=b.First_Branch_Cust_IP
   and 0<=a.Haamada_date-b.APPLICATIONDATE<=1
   and a.degem=b.Degem
   and a.Planned_Pmnts_Nbr<=b.MaxNumPayments
   and a.Planned_Pmnts_Nbr>=b.MinNumPayments
   and  round(a.Mirvach_From_prime,0.01)-round(b.PrimeSpread,0.01)=0
;
quit;

1                                                          The SAS System                            12:28 Saturday, August 30, 2025

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='f_PART7_Engine_New.sas';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='M:\a.ריכוז תוכניות\CS\פאנל העמדות_מעודכן-יולי 2025\f_PART7_Engine_New.sas';
9          %LET _SASPROGRAMFILEHOST='HKSU102561A79';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
NOTE: The ACCESSIBLE_GRAPH option is pre-production for this release.
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         proc sql;
29         create table t_Ind4_b as
30         select a.*,
31         b.UUID,
32         b.productID,
33         b.APPLICATIONDATE,
34         b.LoanRequestedAmount,
35         b.Degem as offer_Degem,
36         b.LoanMaxAmount as Offer_LoanMaxAmount,
37         b.LoanMaxDeductAmount as offer_LoanMaxDeductAmount,
38         b.MinNumPayments as offer_MinNumPayments,
39         b.MaxNumPayments  as offer_MaxNumPayments,
40         b.PrimeSpread  as offer_PrimeSpread
41         from t_Ind4_a(Where=(Approval_Reference_Ind = 1 and Agreement_Account_Id=665845890)) as a
42         left join DWH_OffersDeclinesDaily_b  as b
43         on a.Approval_Reference_Nbr=b.ConsentId
44            and a.Numerator_DWH=b.First_Branch_Cust_IP
45            and 0<=a.Haamada_date-b.APPLICATIONDATE<=1
46            and a.degem=b.Degem
47            and a.Planned_Pmnts_Nbr<=b.MaxNumPayments
48            and a.Planned_Pmnts_Nbr>=b.MinNumPayments
49            and  round(a.Mirvach_From_prime,0.01)-round(b.PrimeSpread,0.01)=0
50         ;
NOTE: SAS threaded sort was used.
NOTE: Invalid (or missing) arguments to the ROUND function have caused the function to return a missing value.
 
ERROR:  An exception has been encountered.
Please contact technical support and provide them with the following traceback information:
 
2                                                          The SAS System                            12:28 Saturday, August 30, 2025

The SAS task name is [SQL (2) ]
Segmentation Violation
 
Traceback of the Exception:
 
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sas(+0x15cfde) [0x55fc22883fde]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sas(+0x4cb7b) [0x55fc22773b7b]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/tkmk.so(bkt_signal_handler+0x144) [0x7fee8516a404]
/lib64/libpthread.so.0(+0xf630) [0x7fee863db630]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(__intel_ssse3_rep_memcpy+0x27a0) [0x7fee398afa30]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(sqxcseq+0x28e) [0x7fee39893b6e]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x1b088) [0x7fee3986d088]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x208db) [0x7fee398728db]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x1f9c0) [0x7fee398719c0]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x21410) [0x7fee39873410]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(sqxexec+0x56d) [0x7fee3989289d]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(+0x39681) [0x7fee3988b681]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassqx(sqlloop+0x405) [0x7fee3988ad35]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sassql(sassql+0x27a) [0x7fee22ab531a]
/usr/local/SAS/SASHome/SASFoundation/9.4/sasexe/sas(vvtentr+0x13d) [0x55fc2277371d]
/lib64/libpthread.so.0(+0x7ea5) [0x7fee863d3ea5]
/lib64/libc.so.6(clone+0x6d) [0x7fee859bfb0d]

NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 1 observations read from the data set WORK.T_IND4_A.
      WHERE (Approval_Reference_Ind=1) and (Agreement_Account_Id=665845890);
NOTE: There were 9362446 observations read from the data set WORK.DWH_OFFERSDECLINESDAILY_B.
WARNING: The data set WORK.T_IND4_B may be incomplete.  When this step was stopped there were 0 observations and 32 variables.
WARNING: Data set WORK.T_IND4_B was not replaced because this step was stopped.
NOTE: PROCEDURE SQL used (Total process time):
      real time           16.22 seconds
      user cpu time       15.42 seconds
      system cpu time     8.82 seconds
      memory              3962487.20k
      OS Memory           3990500.00k
      Timestamp           09/01/2025 10:20:20 AM
      Step Count                        1399  Switch Count  8
      Page Faults                       0
      Page Reclaims                     2167726
      Page Swaps                        0
      Voluntary Context Switches        131922
      Involuntary Context Switches      411
      Block Input Operations            0
      Block Output Operations           0
      
51         quit;
52         
53         %LET _CLIENTTASKLABEL=;
54         %LET _CLIENTPROCESSFLOWNAME=;
55         %LET _CLIENTPROJECTPATH=;
56         %LET _CLIENTPROJECTPATHHOST=;
57         %LET _CLIENTPROJECTNAME=;
58         %LET _SASPROGRAMFILE=;
59         %LET _SASPROGRAMFILEHOST=;
60         
61         ;*';*";*/;quit;run;
62         ODS _ALL_ CLOSE;
63         
3                                                          The SAS System                            12:28 Saturday, August 30, 2025

64         
65         QUIT; RUN;
66         

[Kurt_Bremser]

What it says. SAS technical support needs to handle this.

Unless there‘s a many-to-many relationship with regards to the keys, consider doing this in a DATA step.

[Ronein]

It is many to many

[Ksharp · Accepted solution]

There must be an error hidden in your code.

According to your LOG:

NOTE: Invalid (or missing) arguments to the ROUND function have caused the function to return a missing value.

I guess you should change this

round(a.Mirvach_From_prime,0.01)-round(b.PrimeSpread,0.01)=0

into

round(a.Mirvach_From_prime,0.01) = round(b.PrimeSpread,0.01)

Because yours is unbalance expression that would lead to an error  in general.

[Ronein]

You are right!! I just don't understand what do you mean unbalanced? Why is it bad code? The part you mentioned

[Ksharp]

I will give you an example:

1    proc sql;
2    select *
3     from sashelp.class as a,sashelp.class as b
4      where round(a.weight,0.01)-round(b.weight,0.01)=0;
NOTE: Writing HTML Body file: sashtml.htm
NOTE: No rows were selected.
5    quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.83 seconds
      cpu time            0.35 seconds

If using your code, you would get nothing.

6    proc sql;
7    select *
8     from sashelp.class as a,sashelp.class as b
9      where round(a.weight,0.01)=round(b.weight,0.01);
10   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.03 seconds
      cpu time            0.01 seconds

If using my code , you would get 17 obs.

[FreelanceReinh]

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.

[Ksharp]

Freelance,
Sorry , I have no idea about this either.
The only ERROR looks like in OP code is that unbalanced expression, so I suggested OP to fixed it by my code.

[LinusH]

My experience of sending this type of errors to SAS support isn't great, they can rarely point out what causes the error. So I have usually solved it by restructuring the query.
How large are the tables? (in saswork by looking into you program)