ERROR: Sort execution failure.

[wheddingsjr]

Hi all

I wrote this code and get the above error message. Am I doing something wrong?

PROC SQL;
CREATE TABLE WORK.EOL_Cohort_FY19 AS
SELECT DISTINCT '' AS Disease LABEL= 'Disease' FORMAT= $60. LENGTH= 60
	, b.Final_DC AS Disease_Center LABEL= 'Disease Center'
	, DOB AS DOB LABEL= 'DOB' FORMAT= mmddyy10.
	, DOD AS DOD LABEL= 'DOD' FORMAT= mmddyy10.
	, a.EditedSubscriberID
	, FirstName AS First_Name LABEL= 'First Name'
	, LastName AS Last_Name LABEL= 'Last Name'
	, 'BCBSMA' AS Payer LABEL= 'Payer' format $5.
	, '' AS Primary_Physician LABEL= 'Primary Physician' FORMAT= $30. LENGTH= 30
    , t.PatientRaceDSC as Race_Ethnicity LABEL= 'Race/Ethnicity'
	,CASE WHEN PatientRaceDSC = 'Asian' THEN 'Asian'
		  WHEN PatientRaceDSC = 'Black or African American' THEN 'Black or African American'
		  WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
		  WHEN PatientRaceDSC LIKE ('White%') THEN 'White or Caucasian'
	 	  WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
		  WHEN PatientRaceDSC IS NULL THEN 'Unknown'
		  ELSE 'Other' END AS Race
	, R.PermanentStateDSC AS Residence LABEL= 'Residence'
	, R.GenderDSc AS Sex LABEL= 'Sex'
	, R.Age LABEL= 'Age'
	, R.DFCIMRN as MRN LABEL= 'MRN'
	, '' as BMT
	, '' as Edited_Subscriber_ID
FROM WORK.ID_MRN_FY19 a
	left join PatExt.PatientExtension r
	   on a.MRN=input(r.DFCIMRN,15.)
    left join WORK.DiseaseCtr_FY19 b
       on b.MRN=a.MRN
	left join Patient s
		on a.MRN = s.MRN
	left join Pat.race t
		on t.PatientID = s.PatientID;
	quit;

[wheddingsjr]

By the way, when I comment out the PatientRaceDSC and the case statements, as well as the last two joins it runs perfectly which leads me to believe that it has something to do with the last two joins. Am I correct in presuming that?

[LinusH]

Sounds like a memory issue.

Check this thread:

https://communities.sas.com/t5/SAS-Procedures/ERROR-Sort-execution-failure-in-PROC-SQL/td-p/262581

[Kurt_Bremser]

How are the relationships between those tables with regards to the key variables?

These multiple lookups might be doable with hash objects in a data step, where the necessary sorting is done in memory.

Please tell us a little about those tables (numbers of obs, numbers of distinct keys) and the variables you want to pull from them (sizes).