Desktop productivity for business analysts and programmers

Memory Size Issue - How do I resolve this without doing configuration? (I'm on a server)

Reply
Frequent Contributor
Posts: 135

Memory Size Issue - How do I resolve this without doing configuration? (I'm on a server)

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='M290BH';

4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';

5 %LET _CLIENTPROJECTPATH='';

6 %LET _CLIENTPROJECTNAME='';

7 %LET _SASPROGRAMFILE=;

8

9 ODS _ALL_ CLOSE;

10 OPTIONS DEV=ACTIVEX;

11 GOPTIONS XPIXELS=0 YPIXELS=0;

12 FILENAME EGSR TEMP;

13 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR

14 STYLE=HtmlBlue

15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")

16 NOGTITLE

17 NOGFOOTNOTE

18 GPATH=&sasworklocation

19 ENCODING=UTF8

20 options(rolap="on")

21 ;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

22

23 GOPTIONS ACCESSIBLE;

24

25 %GLOBAL FileNameUserID;

26 %GLOBAL FileNameDate;

27 %GLOBAL FileNameEmail;

28

29 %LET FileNameUserID =fs115055;

30 %LET FileNameDate = 201801;

31 %LET FileNameEmail = M290BH;

32

33 %LET TEST = &cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameDate.;

34 %LET NAME = M290;

35

36 %PUT &TEST;

/opt/t7iw/sasdata/users/fs115055/201801

37

38 LIBNAME &NAME "&cOrgNameDataAnalytics./users/&FileNameUserID./&FileNameDate.";

NOTE: Libref M290 was successfully assigned as follows:

Engine: V9

Physical Name: /opt/t7iw/sasdata/users/fs115055/201801

40 **** RUN DATE: 01/08/2018;

41

42 ** UPDATE GLOBAL DATES HERE;

43 %LET F_DOS1='2018-01-01'; *** BEG DOS FOR MMS;

44 %LET L_DOS1='2018-01-31'; *** END DOS FOR MMS;

45 %LET F_DOS2='2018-01-01'; *** BEG DOS FOR CLAIMS;

46 %LET L_DOS2='2018-01-31'; *** END DOS AND PROCESS END DT FOR CLAIMS;

47

48

49 ** MMS PULL 1;

50

51 proc sql ;

52 CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");

53 CREATE TABLE &NAME..MMS AS

54 SELECT * FROM CONNECTION TO tera

2 The SAS System 13:13 Friday, February 23, 2018

55 (SELECT

56 MMS_AUTHRZD_MBR.SPNSR_BRNCH_OF_SRVC_CD,

57 MMS_AUTHRZD_MBR.SPNSR_BRNCH_OF_SRVC_DESC,

58 MMS_AUTHRZTN.AUTHRZTN_RFRNC_NUM,

59 MMS_AUTHRZTN.AUTHRZTN_1_NUM,

60 MMS_AUTHRZTN.AUTHRZTN_2_NUM,

61 MMS_AUTHRZTN.FIRST_CLOSED_DT,

62 MMS_AUTHRZD_MBR.PCM_DMIS_ID,

63 MMS_AUTHRZD_MBR.SPNSR_STUS_CD,

64 MMS_AUTHRZD_MBR.BENE_ROLE_CD,

65 MMS_AUTHRZTN.PRMRY_DGNS_CD,

66 MMS_AUTHRZTN.PRMRY_DGNS_DESC,

67 MMS_AUTHRZD_MBR.BIRTH_DT,

68 MMS_AUTHRZD_MBR.SPNSR_SSN,

69 A_ZAA_AUTHMEMBERZIP.EMSM_CD,

70 DMIS_REPORT.PARNT_DMIS_ID,

71 MMS_MTF_MATCH_DTL_HSTRY.MTF_NAME

72

73 FROM

74 MMS_MTF_MATCH_DTL_HSTRY RIGHT OUTER JOIN MMS_AUTHRZTN ON (MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID=

74 ! MMS_MTF_MATCH_DTL_HSTRY.AUTHRZTN_UNIQ_ID)

75 INNER JOIN MMS_AUTHRZD_MBR ON (MMS_AUTHRZD_MBR.AUTHRZTN_UNIQ_ID=MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID)

76 LEFT OUTER JOIN ZIP_AREA_ASGNMT A_ZAA_AUTHMEMBERZIP ON (A_ZAA_AUTHMEMBERZIP.ZIP_CD=MMS_AUTHRZD_MBR.ZIP AND

77 CURRENT_DATE BETWEEN A_ZAA_AUTHMEMBERZIP.EFCTV_DT and A_ZAA_AUTHMEMBERZIP.TRMNTN_DT)

78 LEFT OUTER JOIN DMIS_REPORT ON (MMS_AUTHRZD_MBR.PCM_DMIS_ID=DMIS_REPORT.DMIS_ID)

79

80 WHERE

81 MMS_AUTHRZTN.FIRST_CLOSED_DT

82 BETWEEN &F_DOS1 AND &L_DOS1

83 AND MMS_AUTHRZTN.AUTHRZTN_CLOSED_FLAG = 'Y'

84 AND MMS_AUTHRZTN.CASE_TYPE_CD IN ('OPBH')

85 AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_TYPE_ID = 'A'

86 );

NOTE: Table M290.MMS created, with 1629337250 rows and 16 columns.

87 %PUT &SQLXMSG &SQLXRC;

0

88

89

90 ** MMS PULL 2;

91

NOTE: PROCEDURE SQL used (Total process time):

real time 59:36.22

cpu time 48:22.77

 

92 proc sql ;

93 CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");

94 CREATE TABLE &NAME..MMSP2 AS

95 SELECT * FROM CONNECTION TO tera

96 (SELECT

97 MMS_AUTHRZTN.AUTHRZTN_RFRNC_NUM,

98 MMS_AUTHRZTN.AUTHRZTN_1_NUM,

99 MMS_AUTHRZTN.AUTHRZTN_2_NUM,

100 MMS_AUTHRZD_MBR.SPNSR_SSN,

101 MMS_AUTHRZD_MBR.BIRTH_DT,

102 MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_FROM_DT,

103 MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_TO_DT,

3 The SAS System 13:13 Friday, February 23, 2018

104 MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_UNIQ_ID,

105 MMS_AUTHRZTN.PRMRY_DGNS_CD,

106 MMS_AUTHRZD_SRVC.AUTHRZD_STRTG_PRCDR_CD,

107 MMS_AUTHRZD_SRVC.AUTHRZD_ENDG_PRCDR_CD,

108 MMS_AUTHRZTN.FIRST_CLOSED_DT,

109 MMS_AUTHRZD_SRVC.AUTHRZD_QTY,

110 MMS_AUTHRZTN.AUTHRZTN_PRFL_CD,

111 MMS_AUTHRZD_PRVDR.PRMRY_SPCLTY_NAME

112 FROM

113 MMS_AUTHRZD_PRVDR RIGHT OUTER JOIN

114 MMS_AUTHRZD_PRVDR_SRVC_ASCTN ON

115 (MMS_AUTHRZD_PRVDR.AUTHRZTN_UNIQ_ID=

116 MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZTN_UNIQ_ID

117 AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_UNIQ_ID=

118 MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_PRVDR_UNIQ_ID

119 AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_ROLE_ID=

120 MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_PRVDR_ROLE_ID

121 AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_TYPE_ID=

122 MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_PRVDR_TYPE_ID)

123 RIGHT OUTER JOIN MMS_AUTHRZD_SRVC ON

124 (MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_UNIQ_ID=

125 MMS_AUTHRZD_PRVDR_SRVC_ASCTN.AUTHRZD_SRVC_UNIQ_ID)

126 INNER JOIN MMS_AUTHRZTN ON

127 (MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID=

128 MMS_AUTHRZD_SRVC.AUTHRZTN_UNIQ_ID)

129 INNER JOIN MMS_AUTHRZD_MBR ON

130 (MMS_AUTHRZD_MBR.AUTHRZTN_UNIQ_ID=

131 MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID)

132 WHERE

133 MMS_AUTHRZTN.FIRST_CLOSED_DT

134 BETWEEN &F_DOS1 AND &L_DOS1

135 AND MMS_AUTHRZTN.AUTHRZTN_CLOSED_FLAG = 'Y'

136 AND MMS_AUTHRZTN.CASE_TYPE_CD IN ('OPBH')

137 AND MMS_AUTHRZD_PRVDR.AUTHRZD_PRVDR_TYPE_ID = 'A'

138 );

NOTE: Table M290.MMSP2 created, with 18835 rows and 15 columns.

139 %PUT &SQLXMSG &SQLXRC;

0

140

141

142 *THERE WERE ? IN THE PCM DMIS, YET WERE SUBMITTED BY AN MTF;

143 *SHOULD WE KEEP THESE OR ELIMINATE THEM?;

144 *DECISION: KEEP THEM PER MARY AND LAWAYNA - 3/26/13;

145 *THUS, NO RESTRICTION ON THE PCM DMIS IDENTIFIER FOR;

146 *MTF AND CIV LINKED ON THE PULL ABOVE;

147

148 *CLM DATA PULL CLAIMS MART FOR Q200 CDRL;

149

NOTE: PROCEDURE SQL used (Total process time):

real time 0.79 seconds

cpu time 0.05 seconds

 

150 proc sql ;

151 CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");

152 CREATE TABLE &NAME..CLM AS

153 SELECT * FROM CONNECTION TO tera

4 The SAS System 13:13 Friday, February 23, 2018

154 (SELECT

155 CLM_HDR_DMNSN.CLM_BASE_NUM,

156 CLM_LINE_FACT.CHCS_ORDR_NUM,

157 DGNS_DMNSN.DGNS_1_CD,

158 PLAN_ADMINR_DMNSN.MCS_SYS_IND,

159 PRVDR_DMNSN.RNDRG_SPCLTY_CD,

160 CLM_HDR_DMNSN.SPNSR_SSN,

161 CLM_HDR_DMNSN.PTNT_BRTH_DT,

162 CLNDR_TIME_DMNSN.CLNDR_DATE,

163 PRCDR_DMNSN.DRVD_PRCDR_CD,

164 DGNS_DMNSN.ICD_VRSN_IND

165 FROM

166 DGNS_DMNSN INNER JOIN CLM_LINE_FACT ON

167 (DGNS_DMNSN.DGNS_DMNSN_ID=

168 CLM_LINE_FACT.DGNS_DMNSN_ID)

169 INNER JOIN CLM_HDR_DMNSN ON

170 (CLM_LINE_FACT.CLM_HDR_ID=

171 CLM_HDR_DMNSN.CLM_HDR_ID)

172 INNER JOIN CLNDR_TIME_DMNSN ON

173 (CLM_LINE_FACT.SRVC_DT_ID=

174 CLNDR_TIME_DMNSN.CLNDR_TIME_ID)

175 INNER JOIN PLAN_ADMINR_DMNSN ON

176 (CLM_LINE_FACT.PLAN_ADMINR_ID=

177 PLAN_ADMINR_DMNSN.PLAN_ADMINR_ID)

178 INNER JOIN PRCDR_DMNSN ON

179 (CLM_LINE_FACT.PRCDR_DMNSN_ID=

180 PRCDR_DMNSN.PRCDR_DMNSN_ID)

181 INNER JOIN PRVDR_DMNSN ON

182 (CLM_LINE_FACT.PRVDR_DMNSN_ID=

183 PRVDR_DMNSN.PRVDR_DMNSN_ID)

184 INNER JOIN CLNDR_TIME_DMNSN A_CLNDR_PRCS ON

185 (A_CLNDR_PRCS.CLNDR_TIME_ID=CLM_LINE_FACT.CLM_PRCS_DT_ID)

186 WHERE

187 CLNDR_TIME_DMNSN.CLNDR_DATE

188 BETWEEN &F_DOS2 AND &L_DOS2

189 AND CLM_LINE_FACT.LINE_ALOWD_AMT > 0

190 AND (PRCDR_DMNSN.DRVD_PRCDR_CD IN

191 ('90791', '90792', '90785', '90832', '90833',

192 '90834', '90836', '90837', '90838', '90839',

193 '90840', '90846', '90847', '90849', '90853', '90863')

194 OR PRCDR_DMNSN.DRVD_PRCDR_CD IN

195 ('99201', '99202', '99203', '99204', '99205',

196 '99211', '99212', '99213', '99214', '99215',

197 '99241', '99242', '99243', '99244', '99245',

198 '99341', '99342', '99343', '99344', '99345',

199 '99346', '99347', '99348', '99349', '99350')

200 AND PRVDR_DMNSN.RNDRG_SPCLTY_CD IN

201 ('26', '27', '77', '62', '91'))

202 AND A_CLNDR_PRCS.CLNDR_DATE <= &L_DOS2

203 );

NOTE: Table M290.CLM created, with 10122 rows and 10 columns.

204 %PUT &SQLXMSG &SQLXRC;

0

205

206 /*BELOW KEEP ONLY FIRST 4 DIGITS DMIS IN AUTH NUMBERS;

207 *AUTHS SUBMITTED BY MTF;

208 *DELETE CLINIC/CENTER : SUBSTANCE ABUSE REHABILITATION FACILITY;

5 The SAS System 13:13 Friday, February 23, 2018

209 *IF SERVICE QUANTITY IS 3 OR MORE;*/

210

NOTE: PROCEDURE SQL used (Total process time):

real time 2.37 seconds

cpu time 0.03 seconds

 

211 DATA MMS1;

212 SET &NAME..MMS;

213 ORDDMIS=SUBSTR(AUTHRZTN_1_NUM,1,4);

214

215 IF SPNSR_STUS_CD IN ('A','N','V','J') AND

216 BENE_ROLE_CD='BD' THEN BENECAT='ADSM ';

217 ELSE

218 IF SPNSR_STUS_CD IN ('A','N','V','J') AND

219 BENE_ROLE_CD NE 'BD' THEN BENECAT='ADFM';

220 ELSE

221 IF SPNSR_STUS_CD NOT IN ('A','N','V','J') THEN

222 BENECAT='NADFM';

223

224 IF SUBSTR(AUTHRZTN_1_NUM,5,1)='-' THEN OUTPUT;

225 RUN;

NOTE: There were 1629337250 observations read from the data set M290.MMS.

NOTE: The data set WORK.MMS1 has 558877000 observations and 18 variables.

NOTE: DATA statement used (Total process time):

real time 30:37.33

cpu time 11:02.09

 

226

227

228 ***** NOW DO THE SAME FOR MMS;

229 DATA MMSDEL (KEEP=AUTHRZTN_RFRNC_NUM);

230 SET &NAME..MMSP2;

231 WHERE AUTHRZTN_PRFL_CD IN ('P49' 'P50');

232 PRMRY_SPCLTY_NAME=UPCASE(PRMRY_SPCLTY_NAME);

233 IF PRMRY_SPCLTY_NAME='SUBSTANCE USE REHAB FACILITY'

234 AND AUTHRZD_QTY GE 3;

235 RUN;

NOTE: There were 16426 observations read from the data set M290.MMSP2.

WHERE AUTHRZTN_PRFL_CD in ('P49', 'P50');

NOTE: The data set WORK.MMSDEL has 0 observations and 1 variables.

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.01 seconds

 

236

237 PROC SORT DATA=MMS1;

238 BY AUTHRZTN_RFRNC_NUM;

239 RUN;

ERROR: No disk space is available for the write operation. Filename =

/opt/t7iw/sas94/SASWork/SAS_util000100001641_fedpxt7iw1001.fs.centene.com/ut1641000002.utl.

ERROR: Failure while attempting to write page 5567 of sorted run 71.

ERROR: Failure while attempting to write page 516637 to utility file 1.

6 The SAS System 13:13 Friday, February 23, 2018

ERROR: Failure encountered while creating initial set of sorted runs.

ERROR: Failure encountered during external sort.

ERROR: Sort execution failure.

Super User
Posts: 4,030

Re: Memory Size Issue - How do I resolve this without doing configuration? (I'm on a server)

You have run out of disk space while sorting. Only someone with admin access to your SAS server can fix this properly. You MAY be able to get it to run by adding - options COMPRESS = binary or yes - to the top of your program but there is no guarantee it will work.

 

I see your SAS server OS is Unix. It is possible you have an individual disk space limit setting that needs adjusting by your administrator, or it could be the whole SAS WORK file system is full.

Ask a Question
Discussion stats
  • 1 reply
  • 203 views
  • 0 likes
  • 2 in conversation