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

Alexxxxxxx — Wed, 30 Aug 2023 02:53:39 GMT

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

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

SASKiwi — Wed, 30 Aug 2023 03:19:46 GMT

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.

topic ERROR: :Error occurred while reading from temporary sort file. in SAS Programming

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

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