topic Sort failure in SAS Enterprise Guide

Sort failure

Rsadiq — Mon, 16 Oct 2017 19:46:11 GMT

Hello I am having an issue with a constraint I am placing in this query

        on a.PERSON_KEY = b.PERSON_KEY
219               where b.SRVC_FROM_DT between "01Mar2016"d and "31Aug2017"d
220               and b.Client_ky = 2;
ERROR: Sort execution failure.

It says the sort has failed when I added client_ky=2;

This is a new error to me

Thanks

Rida

Re: Sort failure

Reeza — Mon, 16 Oct 2017 19:48:24 GMT

Can you post more of the log?

Re: Sort failure

Rsadiq — Mon, 16 Oct 2017 19:51:58 GMT

Sure, see below

proc sql (outobs=1000) ;
    create table check_mbrs as
      select distinct
          a.INDV_ID
         ,b.SRVC_FROM_DT
         ,b.DIAG_1_CD
         ,b.DIAG_2_CD
         ,b.copay_amt
         ,b.cov_amt
         ,b.ded_amt
         ,b.dscnt_amt
         ,b.dup_chrg_amt
         ,b.net_pd_amt
         ,b.sbmt_chrg_amt
         ,b.mdcr_pd_amt
         ,b.mdcr_allw_amt
         ,b.Client_ky
      from ndc_hedis_fx c
                  inner join
              ecroext.ext_person_lu a
                on a.INDV_ID = c.INDV_ID
                   left join
                 ecroext.ext_mcf_filtered b
                  on a.PERSON_KEY = b.PERSON_KEY
       where b.SRVC_FROM_DT between "01Mar2016"d and "31Aug2017"d
       and b.Client_ky = 2;
quit ;

Re: Sort failure

Rsadiq — Mon, 16 Oct 2017 20:03:56 GMT

20 and b.Client_ky = 2;
ERROR: Sort execution failure.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
221          quit ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:22:20.20
      cpu time            22:07.59

Re: Sort failure

Reeza — Mon, 16 Oct 2017 20:05:14 GMT

That's not the full log, that's the code.

Re: Sort failure

Rsadiq — Mon, 16 Oct 2017 20:08:10 GMT

218                          on a.PERSON_KEY = b.PERSON_KEY
219               where b.SRVC_FROM_DT between "01Mar2016"d and "31Aug2017"d
220               and b.Client_ky = 2;
ERROR: Sort execution failure.

223          data diag1 (keep= INDV_ID diag_1_cd) diag2 (keep=INDV_ID DIAG_2_CD) ;
224            set check_mbrs ;
ERROR: File WORK.CHECK_MBRS.DATA does not exist.
225          run ;

WARNING: The variable INDV_ID in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable diag_1_cd in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable INDV_ID in the DROP, KEEP, or RENAME list has never been referenced.
WARNING: The variable DIAG_2_CD in the DROP, KEEP, or RENAME list has never been referenced.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.DIAG1 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
WARNING: The data set WORK.DIAG2 may be incomplete. When this step was stopped there were 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.04 seconds
      cpu time            0.00 seconds

Re: Sort failure

Reeza — Mon, 16 Oct 2017 20:10:17 GMT

Still not the full log. If you can't post it for some reason, contact SAS tech support instead.

Re: Sort failure

Rsadiq — Mon, 16 Oct 2017 20:12:31 GMT

Heres the full log below

OTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.
NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software 9.4 (TS1M3)
Licensed to OPTUM - ODM - OI GRID - PROD - NODE 2, Site 70017167.
NOTE: This session is executing on the AIX 7.1 (AIX 64) platform.

NOTE: Updated analytical products:

      SAS/STAT 14.1
      SAS/QC 14.1

NOTE: Additional host information:

IBM AIX AIX 64 1 7 00FA32894C00

You are running SAS 9. Some SAS 8 files will be automatically converted
by the V9 engine; others are incompatible. Please see
http://support.sas.com/rnd/migration/planning/platform/64bit.html

PROC MIGRATE will preserve current SAS file attributes and is
recommended for converting all your SAS libraries from any
SAS 8 release to SAS 9. For details and examples, please see
http://support.sas.com/rnd/migration/index.html

This message is contained in the SAS news file, and is presented upon
initialization. Edit the file "news" in the "misc/base" directory to
display site-specific news and information in the program log.
The command line option "-nonews" will prevent this display.

NOTE: SAS initialization used:
      real time           0.23 seconds
      cpu time            0.02 seconds

NOTE: Libref SASDATA successfully assigned from logical server.
NOTE: Libref stpsamp successfully assigned from logical server.

NOTE: AUTOEXEC processing beginning; file is /hasasaa/files/shared/hasasaa_autoexec.sas.

WARNING: Libref OIATMPLT is not assigned.
NOTE: Libref OIATMPLT was successfully assigned as follows:
Engine: V9
Physical Name: /hasasaa/shared/hasasaa
Current ODS PATH list is:

1. SASUSER.TEMPLAT(UPDATE)
2. OIATMPLT.TEMPLAT(READ)
3. SASHELP.TMPLMST(READ)
thisprog -----> /hasasaa/reporting/production/outreach_load_monthly_prod/sasdata/zzz.sas

NOTE: DATA statement used (Total process time):
real time 0.01 seconds
2 The SAS System 13:15 Monday, October 16, 2017

cpu time 0.01 seconds

PROGRAM   ----> zzz.sas
PROJPATH ----> /hasasaa/reporting/production/outreach_load_monthly_prod/
PROJNAME ----> outreach_load_monthly_prod
JOBNAME   ----> zzz
ISPROJECT ----> production
NEWAUTOS ----> '/hasasaa/reporting/production/outreach_load_monthly_prod/macros'
DTSTAMP     ----> 20171016-13154103
IDPWDPATH = /hasasaa/idpwd
SYSUSER = rsadiq

NOTE: PROCEDURE PWENCODE used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

MPRINT(SCHEMA_CONNECT): ;
MPRINT(SCHEMA_CONNECT):   data _null_;
MPRINT(SCHEMA_CONNECT):   infile pwfile obs=1 length=l;
MPRINT(SCHEMA_CONNECT):   input @;
MPRINT(SCHEMA_CONNECT):   input @1 line $varying1024. l;
MPRINT(SCHEMA_CONNECT):   call symput('crmextro_pass',substr(line,1,l));
MPRINT(SCHEMA_CONNECT):   run;

NOTE: The infile PWFILE is:
      Filename=/hasasaa/idpwd/rsadiq/pass.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=16Oct2017:13:15:41,
      File Size (bytes)=41

NOTE: 1 record was read from the infile PWFILE.
      The minimum record length was 40.
      The maximum record length was 40.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

MLOGIC(SCHEMA_CONNECT): %IF condition %sysfunc(fileexist(&Idpwd./&sysuserid./autolib.sas)) is FALSE
MPRINT(SCHEMA_CONNECT):   libname crmtemp teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971"
server="udwprod" schema=crmtemp ;
NOTE: Libref CRMTEMP was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname ecroext teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971"
server="udwprod" schema=ecroext ;
NOTE: Libref ECROEXT was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname ng teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971" server="udwprod"
schema=ng ;
NOTE: Libref NG was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname symm8 teradata user="rsadiq" password="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971" server="udwprod"
3                                                          The SAS System                             13:15 Monday, October 16, 2017

schema=symm8 ;
NOTE: Libref SYMM8 was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
MPRINT(SCHEMA_CONNECT):   libname isdw sqlsvr dsn="isdw_prd_iwa" user="rsadiq" pwd="{SASENC}BA7B9D0621DAD7594C80A0DD4A540971"
schema=compas;
NOTE: Libref ISDW was successfully assigned as follows:
      Engine:        SQLSVR
      Physical Name: isdw_prd_iwa
MLOGIC(SCHEMA_CONNECT): %PUT CRM connection complete
CRM connection complete
MLOGIC(SCHEMA_CONNECT): %IF condition %sysfunc(fileexist(&Idpwd./&sysuserid./oim.txt)) is TRUE
MPRINT(SCHEMA_CONNECT):   data _null_ ;
MPRINT(SCHEMA_CONNECT):   infile "/hasasaa/idpwd/rsadiq/oim.txt" recfm=V truncover ;
MPRINT(SCHEMA_CONNECT):   input OIMID : $13. OIMPW : $35.;
MPRINT(SCHEMA_CONNECT):   call symputx ('OIMID',strip(left(OIMID)));
MPRINT(SCHEMA_CONNECT):   call symputx ('OIMPW',strip(left(OIMPW)));
MPRINT(SCHEMA_CONNECT):   run;

NOTE: The infile "/hasasaa/idpwd/rsadiq/oim.txt" is:
      Filename=/hasasaa/idpwd/rsadiq/oim.txt,
      Owner Name=rsadiq,Group Name=dce,
      Access Permission=-rw-r--r--,
      Last Modified=16Aug2017:07:59:38,
      File Size (bytes)=48

NOTE: 2 records were read from the infile "/hasasaa/idpwd/rsadiq/oim.txt".
      The minimum record length was 21.
      The maximum record length was 23.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

MPRINT(SCHEMA_CONNECT):   options obs=max nomprint nomlogic
NOTE: Libref STG_HSR was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: urnprr
NOTE: Libref STG_CARE was successfully assigned as follows:
      Engine:        ORACLE
      Physical Name: urnprr
STG_HSR connection complete

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/sasout" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/output" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/input" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/qa" exists
4 The SAS System 13:15 Monday, October 16, 2017

NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/refdata" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/sasdata" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/macros" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/code" exists
NOTE: The directory "/hasasaa/shared/hasasaa" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/testing" exists
NOTE: The directory "/hasasaa/reporting/production/outreach_load_monthly_prod/archive" exists
NOTE: The directory "/hasasaa/common/refdata" exists
NOTE: Libref SASOUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/sasout
NOTE: Libref OUTPUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/output
NOTE: Libref INPUT was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/input
NOTE: Libref QA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/qa
NOTE: Libref REFDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/refdata
NOTE: Libref SASDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/sasdata
NOTE: Libref MACROS was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/macros
NOTE: Libref CODE was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/code
NOTE: Libref SHARED refers to the same physical library as OIATMPLT.
NOTE: Libref SHARED was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/shared/hasasaa
NOTE: Libref TEST was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/testing
NOTE: Libref ARCHIVE was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/archive
NOTE: Libref COMREF was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/common/refdata
SASOUTPATH ----> /hasasaa/reporting/production/outreach_load_monthly_prod/sasout
OUTPUTPATH ----> /hasasaa/reporting/production/outreach_load_monthly_prod/output
INPUTPATH   ----> /hasasaa/reporting/production/outreach_load_monthly_prod/input
QAPATH      ----> /hasasaa/reporting/production/outreach_load_monthly_prod/qa
REFDATAPATH ----> /hasasaa/reporting/production/outreach_load_monthly_prod/refdata
SASDATAPATH ----> /hasasaa/reporting/production/outreach_load_monthly_prod/sasdata
MACROPATH   ----> /hasasaa/reporting/production/outreach_load_monthly_prod/macros
CODEPATH    ----> /hasasaa/reporting/production/outreach_load_monthly_prod/code
ARCHIVEPATH ----> /hasasaa/reporting/production/outreach_load_monthly_prod/archive
LOGFILE     ----> '/hasasaa/reporting/production/outreach_load_monthly_prod/sasout/zzz_20171016-13154103.log'
SHAREDPATH ----> /hasasaa/shared/hasasaa
TESTPATH    ----> /hasasaa/reporting/production/outreach_load_monthly_prod/testing
NASOUTPUTPATH ----> /hasasaa/oia/oiaprod/Data/output
5                                                          The SAS System                             13:15 Monday, October 16, 2017

NASINPUTPATH ----> /hasasaa/oia/oiaprod/Data/input
COMMONREFPATH ----> /hasasaa/common/refdata
COMMONREFPATH=/hasasaa/common/refdata
GLOBAL ARCHIVEPATH /hasasaa/reporting/production/outreach_load_monthly_prod/archive
GLOBAL CODEPATH /hasasaa/reporting/production/outreach_load_monthly_prod/code
GLOBAL COMMONCODEPATH /hasasaa/common/code
GLOBAL COMMONMACROPATH /hasasaa/common/macros
GLOBAL COMMONREFPATH /hasasaa/common/refdata
GLOBAL CRMEXTRO_PASS {SASENC}BA7B9D0621DAD7594C80A0DD4A540971
GLOBAL CRMEXTRO_PATH
GLOBAL CRMEXTRO_USER rsadiq
GLOBAL CRMRO_PASS
GLOBAL CRMRO_PATH
GLOBAL CRMRO_USER
GLOBAL DTSTAMP 20171016-13154103
GLOBAL INPUTPATH /hasasaa/reporting/production/outreach_load_monthly_prod/input
GLOBAL ISPROJECT production
GLOBAL JOBNAME zzz
GLOBAL LOGFILE '/hasasaa/reporting/production/outreach_load_monthly_prod/sasout/zzz_20171016-13154103.log'
GLOBAL LOGOUT
GLOBAL MACROPATH /hasasaa/reporting/production/outreach_load_monthly_prod/macros
GLOBAL NASINPUTPATH /hasasaa/oia/oiaprod/Data/input
GLOBAL NASOUTPUTPATH /hasasaa/oia/oiaprod/Data/output
GLOBAL NEWAUTOS '/hasasaa/reporting/production/outreach_load_monthly_prod/macros'
GLOBAL OIMID UHG_001200214
GLOBAL OIMPW Cooly6*u
GLOBAL OUTPUTPATH /hasasaa/reporting/production/outreach_load_monthly_prod/output
GLOBAL PROGRAM zzz.sas
GLOBAL PROJNAME outreach_load_monthly_prod
GLOBAL PROJPATH /hasasaa/reporting/production/outreach_load_monthly_prod/
GLOBAL QAPATH /hasasaa/reporting/production/outreach_load_monthly_prod/qa
GLOBAL QTR
GLOBAL REFDATAPATH /hasasaa/reporting/production/outreach_load_monthly_prod/refdata
GLOBAL SASDATAPATH /hasasaa/reporting/production/outreach_load_monthly_prod/sasdata
GLOBAL SASOUTPATH /hasasaa/reporting/production/outreach_load_monthly_prod/sasout
GLOBAL SHAREDPATH /hasasaa/shared/hasasaa
GLOBAL SYSDBMSG
GLOBAL SYSDBRC 0
GLOBAL TEMP_TBL TEMP_mmcginn
GLOBAL TESTPATH /hasasaa/reporting/production/outreach_load_monthly_prod/testing
GLOBAL THISPROG /hasasaa/reporting/production/outreach_load_monthly_prod/sasdata/zzz.sas
GLOBAL UDW_SERVER udwprod
GLOBAL _PWENCODE {SASENC}BA7B9D0621DAD7594C80A0DD4A540971
AUTOMATIC AFDSID 0
AUTOMATIC AFDSNAME
AUTOMATIC AFLIB
AUTOMATIC AFSTR1
AUTOMATIC AFSTR2
AUTOMATIC FSPBDV
AUTOMATIC SYSADDRBITS 64
AUTOMATIC SYSBUFFR
AUTOMATIC SYSCC 0
AUTOMATIC SYSCHARWIDTH 1
AUTOMATIC SYSCMD
AUTOMATIC SYSDATASTEPPHASE
AUTOMATIC SYSDATE 16OCT17
AUTOMATIC SYSDATE9 16OCT2017
AUTOMATIC SYSDAY Monday
6 The SAS System 13:15 Monday, October 16, 2017

AUTOMATIC SYSDEVIC
AUTOMATIC SYSDMG 0
AUTOMATIC SYSDSN         _NULL_
AUTOMATIC SYSENCODING latin1
AUTOMATIC SYSENDIAN BIG
AUTOMATIC SYSENV BACK
AUTOMATIC SYSERR 0
AUTOMATIC SYSERRORTEXT
AUTOMATIC SYSFILRC 0
AUTOMATIC SYSHOSTINFOLONG IBM AIX AIX 64 1 7 00FA32894C00
AUTOMATIC SYSHOSTNAME apspp0537
AUTOMATIC SYSINDEX 17
AUTOMATIC SYSINFO 0
AUTOMATIC SYSJOBID 16974308
AUTOMATIC SYSLAST _NULL_
AUTOMATIC SYSLCKRC 0
AUTOMATIC SYSLIBRC 0
AUTOMATIC SYSLOGAPPLNAME
AUTOMATIC SYSMACRONAME DO_PROJECT
AUTOMATIC SYSMAXLONG 9007199254740992
AUTOMATIC SYSMENV
AUTOMATIC SYSMSG
AUTOMATIC SYSNCPU 4
AUTOMATIC SYSNOBS 0
AUTOMATIC SYSODSESCAPECHAR
AUTOMATIC SYSODSGRAPHICS 0
AUTOMATIC SYSODSPATH SASUSER.TEMPLAT(UPDATE) OIATMPLT.TEMPLAT(READ) SASHELP.TMPLMST(READ)
AUTOMATIC SYSPARM
AUTOMATIC SYSPRINTTOLOG
AUTOMATIC SYSPRINTTOLIST
AUTOMATIC SYSPROCESSID 41DB2D2A1F29E06E4018000000000000
AUTOMATIC SYSPROCESSMODE SAS Batch Mode
AUTOMATIC SYSPROCESSNAME Program /hasasaa/reporting/production/outreach_load_monthly_prod/sasdata/zzz.sas
AUTOMATIC SYSPROCNAME
AUTOMATIC SYSRC 0
AUTOMATIC SYSSCP AIX 64
AUTOMATIC SYSSCPL AIX
AUTOMATIC SYSSITE 70017167
AUTOMATIC SYSSIZEOFLONG 8
AUTOMATIC SYSSIZEOFPTR 8
AUTOMATIC SYSSIZEOFUNICODE 4
AUTOMATIC SYSSTARTID
AUTOMATIC SYSSTARTNAME
AUTOMATIC SYSTCPIPHOSTNAME apspp0537
AUTOMATIC SYSTIME 13:15
AUTOMATIC SYSTIMEZONE
AUTOMATIC SYSTIMEZONEIDENT
AUTOMATIC SYSTIMEZONEOFFSET -18000
AUTOMATIC SYSUSERID rsadiq
AUTOMATIC SYSVER 9.4
AUTOMATIC SYSVLONG 9.04.01M3P062415
AUTOMATIC SYSVLONG4 9.04.01M3P06242015
AUTOMATIC SYSWARNINGTEXT Libref OIATMPLT is not assigned.
MACRO CALLED: addautos v1.1
currautos = ('/hasasaa/reporting/production/outreach_load_monthly_prod/macros' '/hasasaa/shared/hasasaa' '/hasasaa/files/shared'
'/hasasaa/shared/files'                     '/hasasaa/shared/sas' '!SASROOT/sasautos')

NOTE: AUTOEXEC processing completed.
7 The SAS System 13:15 Monday, October 16, 2017

1          libname sasdata "/hasasaa/reporting/production/outreach_load_monthly_prod/sasdata" ;
NOTE: Libref SASDATA was successfully assigned as follows:
      Engine:        V9
      Physical Name: /hasasaa/reporting/production/outreach_load_monthly_prod/sasdata
2
3
4          libname crmtemp teradata user="&crmextro_user." password="&crmextro_pass." server="&udw_server." schema=crmtemp ;
NOTE: Libref CRMTEMP was successfully assigned as follows:
      Engine:        TERADATA
      Physical Name: udwprod
5
6
7             data ndc_hedis_fx_raw (keep=INDV_ID EE_ID STATE MBR_PGM_ID PSI CMTY_SCR CURRENT_USER EVSRULEDESC) ;
8               set sasdata.propensity_21092017_tmp ;
9                where INDV_ID not in (.,0) and PSI >= .55 and CMTY_SCR >= 2 and EVSRULECODE ^= '' and EVSRULECODE not in ('2019',
10         '2010',
11         '2011',
12         '2018',
13         '2009',
14         '2013',
15         '2014',
16         '2000',
17         '2005',
18         '2004',
19         '2001',
20         '1999',
21         '2006',
22         '2008',
23         '2007',
24         '2016',
25         '2015',
26         '2017',
27         '2002',
28         '2003') and CURRENT_USER = 'AYBWELLNESS' ;
29           ;

NOTE: There were 1627 observations read from the data set SASDATA.PROPENSITY_21092017_TMP.
      WHERE INDV_ID not in (., 0) and (PSI>=0.55) and (CMTY_SCR>=2) and EVSRULECODE not in (' ', '1999', '2000', '2001', '2002',
      '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010', '2011', '2013', '2014', '2015', '2016', '2017', '2018',
      '2019') and (CURRENT_USER='AYBWELLNESS');
NOTE: The data set WORK.NDC_HEDIS_FX_RAW has 1627 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           5.15 seconds
      cpu time            0.77 seconds

30            proc sort data=ndc_hedis_fx_raw out=ndc_hedis_fx (keep=INDV_ID) nodupkey ;
31             by INDV_ID ;
32            run ;

NOTE: There were 1627 observations read from the data set WORK.NDC_HEDIS_FX_RAW.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.NDC_HEDIS_FX has 1627 observations and 1 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

8                                                          The SAS System                             13:15 Monday, October 16, 2017

33
34            data file_pwr (drop=INDV_ID) ;
35              set ndc_hedis_fx_raw ;
36            run ;

NOTE: There were 1627 observations read from the data set WORK.NDC_HEDIS_FX_RAW.
NOTE: The data set WORK.FILE_PWR has 1627 observations and 7 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

37
38
39                   proc export data=file_pwr
39       ! outfile="/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm.csv" dbms=csv
39       ! replace;
40                   run ;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
41          /**********************************************************************
42          *   PRODUCT:   SAS
43          *   VERSION:   9.4
44          *   CREATOR:   External File Interface
45          *   DATE:      16OCT17
46          *   DESC:      Generated SAS Datastep Code
47          *   TEMPLATE SOURCE: (None Specified.)
48          ***********************************************************************/
49             data _null_;
50             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
51             %let _EFIREC_ = 0;     /* clear export record count macro variable */
52             file '/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm.csv' delimiter=','
52       ! DSD DROPOVER lrecl=32767;
53             if _n_ = 1 then        /* write column names or labels */
54              do;
55                put
56                   "EE_ID"
57                ','
58                   "STATE"
59                ','
60                   "MBR_PGM_ID"
61                ','
62                   "EVSRULEDESC"
63                ','
64                   "PSI"
65                ','
66                   "CMTY_SCR"
67                ','
68                   "CURRENT_USER"
69                ;
70              end;
71            set FILE_PWR   end=EFIEOD;
72                format EE_ID $60. ;
73                format STATE $2. ;
74                format MBR_PGM_ID 16. ;
75                format EVSRULEDESC $varying500. ;
9                                                          The SAS System                             13:15 Monday, October 16, 2017

76                format PSI best12. ;
77                format CMTY_SCR 12.4 ;
78                format CURRENT_USER $50. ;
79              do;
80                EFIOUT + 1;
81                put EE_ID $ @;
82                put STATE $ @;
83                put MBR_PGM_ID @;
84                put EVSRULEDESC $ @;
85                put PSI @;
86                put CMTY_SCR @;
87                put CURRENT_USER $ ;
88                ;
89              end;
90             if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
91             if EFIEOD then call symputx('_EFIREC_',EFIOUT);
92             run;

NOTE: The file '/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm.csv' is:
      Filename=/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm.csv,
      Owner Name=rsadiq,Group Name=hasasaa,
      Access Permission=-rw-rw-r--,
      Last Modified=16Oct2017:13:15:53

NOTE: 1628 records were written to the file
      '/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm.csv'.
      The minimum record length was 60.
      The maximum record length was 175.
NOTE: There were 1627 observations read from the data set WORK.FILE_PWR.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

1627 records created in /hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm.csv from
FILE_PWR.

NOTE: "/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm.csv" file was successfully
      created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.80 seconds
      cpu time            0.06 seconds

93
94
95                    data ndc_hedis_fx_raw_1 (keep=INDV_ID EE_ID STATE MBR_PGM_ID PSI CMTY_SCR CURRENT_USER EVSRULEDESC) ;
96              set sasdata.propensity_21092017_tmp ;
97               where INDV_ID not in (.,0) and PSI >= .55 and CMTY_SCR >= 2 and EVSRULECODE ^= '' and EVSRULECODE not in ('2019',
98         '2010',
99         '2011',
100        '2018',
101        '2009',
102        '2013',
103        '2014',
104        '2000',
105        '2005',
10                                                         The SAS System                             13:15 Monday, October 16, 2017

106        '2004',
107        '2001',
108        '1999',
109        '2006',
110        '2008',
111        '2007',
112        '2016',
113        '2015',
114        '2017',
115        '2002',
116        '2003') and CURRENT_USER = 'AYBWELLNESS' ;
117          ;

NOTE: There were 1627 observations read from the data set SASDATA.PROPENSITY_21092017_TMP.
      WHERE INDV_ID not in (., 0) and (PSI>=0.55) and (CMTY_SCR>=2) and EVSRULECODE not in (' ', '1999', '2000', '2001', '2002',
      '2003', '2004', '2005', '2006', '2007', '2008', '2009', '2010', '2011', '2013', '2014', '2015', '2016', '2017', '2018',
      '2019') and (CURRENT_USER='AYBWELLNESS');
NOTE: The data set WORK.NDC_HEDIS_FX_RAW_1 has 1627 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           2.03 seconds
      cpu time            0.75 seconds

118           proc sort data=ndc_hedis_fx_raw out=ndc_hedis_fx (keep=INDV_ID) nodupkey ;
119            by INDV_ID ;
120           run ;

121
122           data file_pwr (drop=INDV_ID) ;
123             set ndc_hedis_fx_raw ;
124           run ;

125
126
127                  proc export data=file_pwr
127      ! outfile="/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm_1.csv" dbms=csv
127      ! replace;
128                  run ;

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
129         /**********************************************************************
130         *   PRODUCT:   SAS
11                                                         The SAS System                             13:15 Monday, October 16, 2017

131         *   VERSION:   9.4
132         *   CREATOR:   External File Interface
133         *   DATE:      16OCT17
134         *   DESC:      Generated SAS Datastep Code
135         *   TEMPLATE SOURCE: (None Specified.)
136         ***********************************************************************/
137            data _null_;
138            %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
139            %let _EFIREC_ = 0;     /* clear export record count macro variable */
140            file '/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm_1.csv'
140      ! delimiter=',' DSD DROPOVER lrecl=32767;
141            if _n_ = 1 then        /* write column names or labels */
142             do;
143               put
144                  "EE_ID"
145               ','
146                  "STATE"
147               ','
148                  "MBR_PGM_ID"
149               ','
150                  "EVSRULEDESC"
151               ','
152                  "PSI"
153               ','
154                  "CMTY_SCR"
155               ','
156                  "CURRENT_USER"
157               ;
158             end;
159           set FILE_PWR   end=EFIEOD;
160               format EE_ID $60. ;
161               format STATE $2. ;
162               format MBR_PGM_ID 16. ;
163               format EVSRULEDESC $varying500. ;
164               format PSI best12. ;
165               format CMTY_SCR 12.4 ;
166               format CURRENT_USER $50. ;
167             do;
168               EFIOUT + 1;
169               put EE_ID $ @;
170               put STATE $ @;
171               put MBR_PGM_ID @;
172               put EVSRULEDESC $ @;
173               put PSI @;
174               put CMTY_SCR @;
175               put CURRENT_USER $ ;
176               ;
177             end;
178            if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
179            if EFIEOD then call symputx('_EFIREC_',EFIOUT);
180            run;

NOTE: The file '/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm_1.csv' is:
      Filename=/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm_1.csv,
      Owner Name=rsadiq,Group Name=hasasaa,
      Access Permission=-rw-rw-r--,
      Last Modified=16Oct2017:13:15:56

12 The SAS System 13:15 Monday, October 16, 2017

NOTE: 1628 records were written to the file
      '/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm_1.csv'.
      The minimum record length was 60.
      The maximum record length was 175.
NOTE: There were 1627 observations read from the data set WORK.FILE_PWR.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

1627 records created in /hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm_1.csv from
FILE_PWR.

NOTE: "/hasasaa/reporting/production/outreach_load_monthly_prod//output/pwr_referral_should_be_hrcm_1.csv" file was successfully
      created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.14 seconds
      cpu time            0.03 seconds

181
182
183         /* proc sql;
184            connect to Teradata (user="&crmextro_user" pass="&crmextro_pass" server="&udw_server" mode=teradata);
185            execute(delete from crmtemp.temp_mmcginn all) by teradata;
186                   insert into crmtemp.temp_mmcginn (N1_D18)
187                 select INDV_ID
188                 from ndc_hedis_fx ;
189            execute(CALL CRMEXEC.PROC_ESTIMATE_CRMTEMP_STATS ('CRMTEMP_TB', 'TEMP_mmcginn')) by teradata;
190         disconnect from teradata;
191          quit; */
192
193
194
195          proc sql (outobs=1000) ;
196            create table check_mbrs as
197              select distinct
198                  a.INDV_ID
199                 ,b.SRVC_FROM_DT
200                 ,b.DIAG_1_CD
201                 ,b.DIAG_2_CD
202                 ,b.copay_amt
203                 ,b.cov_amt
204                 ,b.ded_amt
205                 ,b.dscnt_amt
206                 ,b.dup_chrg_amt
207                 ,b.net_pd_amt
208                 ,b.sbmt_chrg_amt
209                 ,b.mdcr_pd_amt
210                 ,b.mdcr_allw_amt
211                 ,b.Client_ky
212              from ndc_hedis_fx c
213                          inner join
214                      ecroext.ext_person_lu a
215                        on a.INDV_ID = c.INDV_ID
216                           left join
217                         ecroext.ext_mcf_filtered b
13                                                         The SAS System                             13:15 Monday, October 16, 2017

223          data diag1 (keep= INDV_ID diag_1_cd) diag2 (keep=INDV_ID DIAG_2_CD) ;
224            set check_mbrs ;
ERROR: File WORK.CHECK_MBRS.DATA does not exist.
225          run ;

226
227          data diag1 (keep=DIAG_CD) ;
228            set diag1 ;
229             DIAG_CD = DIAG_1_CD ;
230          run ;

NOTE: The data set WORK.DIAG1 has 0 observations and 1 variables.
WARNING: Data set WORK.DIAG1 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

231
232          data diag2 (keep=DIAG_CD) ;
233            set diag2 ;
234             DIAG_CD = DIAG_2_CD ;
235          run ;

NOTE: The data set WORK.DIAG2 has 0 observations and 1 variables.
WARNING: Data set WORK.DIAG2 was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds
14                                                         The SAS System                             13:15 Monday, October 16, 2017

236
237          data diag ;
238            set diag1 diag2 ;
239          run ;

NOTE: The data set WORK.DIAG has 0 observations and 0 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

240
241          proc sql ;
242            create table tmp_diag_cnt as
243              select distinct
244                   DIAG_CD
245                 ,count(*)      as REC_COUNT
246              from diag
247              group by DIAG_CD ;
ERROR: Table WORK.DIAG doesn't have any columns. PROC SQL requires each of its tables to have at least 1 column.
ERROR: The following columns were not found in the contributing tables: DIAG_CD.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
248          quit ;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

249

250 proc sort data=tmp_diag_cnt ;
ERROR: File WORK.TMP_DIAG_CNT.DATA does not exist.
251 by descending REC_COUNT ;
ERROR: No data set open to look up variables.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time

Re: Sort failure

ballardw — Mon, 16 Oct 2017 20:28:22 GMT

WAG: Is b.Client_ky actually supposed to be b.Client_key?

And sometimes filtering may want to be done a bit earlier in the process to reduce intermediate result sizes:

You have

 ecroext.ext_mcf_filtered b

maybe you want to try

(select * from  ecroext.ext_mcf_filtered where Client_ky=2) as b
(or client_key=2 )

to filter the size of the B alias set earlier.

Re: Sort failure

Kurt_Bremser — Mon, 16 Oct 2017 20:32:34 GMT

For your given dataset sizes, the SQL step tries to do too much in one sweep; dissect the step into at least two smaller steps, and use data/sort instead of SQL.

You are running out of space in WORK.

Re: Sort failure

_s_ — Mon, 16 Oct 2017 21:04:00 GMT

Try changing this section of the SELECT statement ,

...

                left join
                 ecroext.ext_mcf_filtered b
                  on a.PERSON_KEY = b.PERSON_KEY
       where b.SRVC_FROM_DT between "01Mar2016"d and "31Aug2017"d
       and b.Client_ky = 2;
quit ; ,

...

                left join
                 (select * from ecroext.ext_mcf_filtered where SRVC_FROM_DT between "01Mar2016"d and "31Aug2017"d
      and Client_ky = 2) b
                  on a.PERSON_KEY = b.PERSON_KEY
       ;
quit ;

The SAS SQL compiler ideally promotes a WHERE condition when equivalent as I suggest, Sometimes one must intervene and improve the query prior to compilation. If ecroext.ext_mcf_filtered contains a large number of column variables, you might also move the SELECT arguments down to the subquery and add the PERSON_KEY. (Remember to remove the column variable aliases in the subquery).

Re: Sort failure

Kurt_Bremser — Tue, 17 Oct 2017 05:47:00 GMT

PS This Note:

NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.

points to at least another running SAS session, which might have filled your WORK location already before you attempt your SQL.