ERROR: :Error occurred while reading from temporary sort file.

[Alexxxxxxx]

Dear all,

I am trying to run an %MACRO function by using below code,

%MACRO Explo(year=);
	PROC SQL;
		CREATE TABLE Patmeasu.Exp3_p5y_&year. AS
		SELECT DISTINCT
		citing1.docdb_family_id_citing,
		citing2.docdb_family_id_citing as docdb_family_id_citing_p5
		FROM
		Patmeasu.Exp1_citing1_&year. as citing1
		JOIN Patmeasu.Exp1_citing2_&year. AS citing2 ON citing1.psn_name_citing = citing2.psn_name_citing
		where citing1.docdb_family_id_citing ^= citing2.docdb_family_id_citing
		group by citing1.appln_filing_date
		having citing2.appln_filing_date between intnx('year', min(citing1.appln_filing_date), -5, 'S') and citing1.appln_filing_date
		;
	QUIT;
%MEND Explo;
%Explo(year=2015)
%Explo(year=2016)

the only difference here is year. The problem is this code can produce the table when year=2015, however, the result is "ERROR: :Error occurred while reading from temporary sort file." when year=2016 (please see detail below).

Do you know what is the reason? Could you please give me some adivce about this? Many thanks in advance.

64   %MACRO Explo(year=);
65       PROC SQL;
66           CREATE TABLE Patmeasu.Exp3_p5y_&year. AS
67           SELECT DISTINCT
68           /*citing1.appln_filing_date,*/
69           citing1.docdb_family_id_citing,
70           /*citing2.appln_filing_date as appln_filing_date_p5,*/
71           citing2.docdb_family_id_citing as docdb_family_id_citing_p5
72           FROM
73           Patmeasu.Exp1_citing1_&year. as citing1
74           JOIN Patmeasu.Exp1_citing2_&year. AS citing2 ON citing1.psn_name_citing = citing2.psn_name_citing
75           where citing1.docdb_family_id_citing ^= citing2.docdb_family_id_citing
76           group by citing1.appln_filing_date
77           having citing2.appln_filing_date between intnx('year', min(citing1.appln_filing_date), -5, 'S') and
77 ! citing1.appln_filing_date 
78           ;
79       QUIT;
90   %MEND Explo;
91   %Explo(year=2015)
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table PATMEASU.EXP3_P5Y_2015 created, with 1573560725 rows and 2 columns.

NOTE: PROCEDURE SQL used (Total process time):
      real time           1:49:48.18
      cpu time            34:14.93

92   %Explo(year=2016)
NOTE: The query requires remerging summary statistics back with the original data.
ERROR: :Error occurred while reading from temporary sort file.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:16:36.57
      cpu time            27:30.78

[SASKiwi]

Did you look at this note in your log?

NOTE: Table PATMEASU.EXP3_P5Y_2015 created, with 1573560725 rows and 2 columns.

Your query has created a table with 1.57 billion rows before most likely running out of space! How many rows does this query normally produce? This is symptomatic of incomplete or incorrect table joining logic.