Desktop productivity for business analysts and programmers

Numer of observations are off starting at line 222 - goes down considerably...

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 135
Accepted Solution

Numer of observations are off starting at line 222 - goes down considerably...

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

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='M290BHV3';

4 %LET _CLIENTPROCESSFLOWNAME='Process Flow';

5 %LET _CLIENTPROJECTPATH='\\fs.centene.com\Users\home_drive\fs115055\Desktop\Project.egp';

6 %LET _CLIENTPROJECTNAME='Project.egp';

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

39

40

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

42

43 ** UPDATE GLOBAL DATES HERE;

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

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

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

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

48

49

50 ** MMS PULL;

51

52 proc sql ;

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

2 The SAS System 14:37 Tuesday, March 6, 2018

54 CREATE TABLE &NAME..MMS AS

55 SELECT * FROM CONNECTION TO tera

56 (SELECT

57 MMS_AUTHRZTN.SBMTG_DMIS_ID,

58 MMS_AUTHRZTN.AUTHRZTN_PRFL_CD,

59 MMS_AUTHRZTN.AUTHRZTN_RFRNC_NUM,

60 MMS_AUTHRZTN.AUTHRZTN_2_NUM,

61 MMS_AUTHRZTN.FIRST_CLOSED_DT,

62 MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_FROM_DT,

63 MMS_AUTHRZD_SRVC.AUTHRZD_SRVC_TO_DT,

64 MMS_AUTHRZD_SRVC.AUTHRZTN_LTR_SPCLTY_DESC,

65 MMS_AUTHRZD_SRVC.AUTHRZD_QTY,

66 MMS_AUTHRZD_MBR.SPNSR_BRNCH_OF_SRVC_DESC,

67 MMS_AUTHRZD_MBR.PCM_DMIS_ID,

68 MMS_AUTHRZD_MBR.PCM_DMIS_NAME,

69 MMS_AUTHRZD_MBR.BIRTH_DT,

70 MMS_AUTHRZD_MBR.SPNSR_SSN,

71 DMIS_REPORT.PARNT_DMIS_ID,

72 DMIS_REPORT.PARNT_DMIS_FAC_NM,

73 A_ZAA_AUTHMEMBERZIP.EMSM_CD

74 FROM

75 MMS_AUTHRZD_SRVC

76 INNER JOIN MMS_AUTHRZTN

77 ON (MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID=MMS_AUTHRZD_SRVC.AUTHRZTN_UNIQ_ID)

78 INNER JOIN MMS_AUTHRZD_MBR

79 ON (MMS_AUTHRZD_MBR.AUTHRZTN_UNIQ_ID=MMS_AUTHRZTN.AUTHRZTN_UNIQ_ID)

80 LEFT OUTER JOIN ZIP_AREA_ASGNMT A_ZAA_AUTHMEMBERZIP

81 ON (A_ZAA_AUTHMEMBERZIP.ZIP_CD=MMS_AUTHRZD_MBR.ZIP AND

82 CURRENT_DATE BETWEEN A_ZAA_AUTHMEMBERZIP.EFCTV_DT and A_ZAA_AUTHMEMBERZIP.TRMNTN_DT)

83 LEFT OUTER JOIN DMIS_REPORT

84 ON (MMS_AUTHRZD_MBR.PCM_DMIS_ID=DMIS_REPORT.DMIS_ID)

85 WHERE (

86 MMS_AUTHRZTN.FIRST_CLOSED_DT BETWEEN &F_DOS1 AND &L_DOS1

87 AND MMS_AUTHRZTN.AUTHRZTN_CLOSED_FLAG = 'Y'

88 AND MMS_AUTHRZTN.SBMTG_DMIS_ID <> '?'

89 AND MMS_AUTHRZD_MBR.BENE_ROLE_CD = 'BD'

90 AND MMS_AUTHRZD_MBR.SPNSR_STUS_CD IN ( 'A','N','V','J' )

91 AND MMS_AUTHRZTN.CASE_TYPE_CD IN ('OPBH')

92 )

93 );

NOTE: Table M290.MMS created, with 2467 rows and 17 columns.

94

95 %PUT &SQLXMSG &SQLXRC;

0

96

97

98

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

100 *SHOULD WE KEEP THESE OR ELIMINATE THEM?;

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

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

103 *MTF AND CIV LINKED ON THE PULL ABOVE;

104

105 *CLM DATA PULL CLAIMS MART FOR Q200 CDRL;

106

NOTE: PROCEDURE SQL used (Total process time):

real time 1.05 seconds

3 The SAS System 14:37 Tuesday, March 6, 2018

cpu time 0.04 seconds

 

107 proc sql ;

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

109 CREATE TABLE &NAME..CLM AS

110 SELECT * FROM CONNECTION TO tera

111 (SELECT

112 CLM_HDR_DMNSN.CLM_BASE_NUM,

113 CLM_LINE_FACT.CHCS_ORDR_NUM,

114 DGNS_DMNSN.DGNS_1_CD,

115 PLAN_ADMINR_DMNSN.MCS_SYS_IND,

116 PRVDR_DMNSN.RNDRG_SPCLTY_CD,

117 CLM_HDR_DMNSN.SPNSR_SSN,

118 CLM_HDR_DMNSN.PTNT_BRTH_DT,

119 CLNDR_TIME_DMNSN.CLNDR_DATE,

120 PRCDR_DMNSN.DRVD_PRCDR_CD,

121 DGNS_DMNSN.ICD_VRSN_IND

122 FROM

123 DGNS_DMNSN INNER JOIN CLM_LINE_FACT ON

124 (DGNS_DMNSN.DGNS_DMNSN_ID=

125 CLM_LINE_FACT.DGNS_DMNSN_ID)

126 INNER JOIN CLM_HDR_DMNSN ON

127 (CLM_LINE_FACT.CLM_HDR_ID=

128 CLM_HDR_DMNSN.CLM_HDR_ID)

129 INNER JOIN CLNDR_TIME_DMNSN ON

130 (CLM_LINE_FACT.SRVC_DT_ID=

131 CLNDR_TIME_DMNSN.CLNDR_TIME_ID)

132 INNER JOIN PLAN_ADMINR_DMNSN ON

133 (CLM_LINE_FACT.PLAN_ADMINR_ID=

134 PLAN_ADMINR_DMNSN.PLAN_ADMINR_ID)

135 INNER JOIN PRCDR_DMNSN ON

136 (CLM_LINE_FACT.PRCDR_DMNSN_ID=

137 PRCDR_DMNSN.PRCDR_DMNSN_ID)

138 INNER JOIN PRVDR_DMNSN ON

139 (CLM_LINE_FACT.PRVDR_DMNSN_ID=

140 PRVDR_DMNSN.PRVDR_DMNSN_ID)

141 INNER JOIN CLNDR_TIME_DMNSN A_CLNDR_PRCS ON

142 (A_CLNDR_PRCS.CLNDR_TIME_ID=CLM_LINE_FACT.CLM_PRCS_DT_ID)

143 WHERE

144 CLNDR_TIME_DMNSN.CLNDR_DATE

145 BETWEEN &F_DOS2 AND &L_DOS2

146 AND CLM_LINE_FACT.LINE_ALOWD_AMT > 0

147 AND (PRCDR_DMNSN.DRVD_PRCDR_CD IN

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

149 '90834', '90836', '90837', '90838', '90839',

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

151 OR PRCDR_DMNSN.DRVD_PRCDR_CD IN

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

153 '99211', '99212', '99213', '99214', '99215',

154 '99241', '99242', '99243', '99244', '99245',

155 '99341', '99342', '99343', '99344', '99345',

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

157 AND PRVDR_DMNSN.RNDRG_SPCLTY_CD IN

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

159 /*AND A_CLNDR_PRCS.CLNDR_DATE <= &L_DOS2 */

160 );

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

4 The SAS System 14:37 Tuesday, March 6, 2018

 

161 %PUT &SQLXMSG &SQLXRC;

0

162 /*

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

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

165

NOTE: PROCEDURE SQL used (Total process time):

real time 3.28 seconds

cpu time 0.11 seconds

 

166 DATA &NAME..MMSDEL (KEEP=AUTHRZTN_RFRNC_NUM); /*MAKE PERM*/

167 SET &NAME..MMS;

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

169 AUTHRZTN_LTR_SPCLTY_DESC=UPCASE(AUTHRZTN_LTR_SPCLTY_DESC);

170 IF AUTHRZTN_LTR_SPCLTY_DESC='SUBSTANCE USE REHAB FACILITY'

171 AND AUTHRZD_QTY GE 3;

172 RUN;

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

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

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

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

173

174 PROC SORT DATA=&NAME..MMS; /*MAKE PERMANENT*/

175 BY AUTHRZTN_RFRNC_NUM;

176 RUN;

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

NOTE: The data set M290.MMS has 2467 observations and 17 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

 

177

178 PROC SORT DATA=&NAME..MMSDEL OUT=&NAME..MMSDEL1 NODUPKEY; /*MAKE PERM*/

179 BY AUTHRZTN_RFRNC_NUM;

180 RUN;

NOTE: Input data set is empty.

NOTE: 0 observations with duplicate key values were deleted.

NOTE: The data set M290.MMSDEL1 has 0 observations and 1 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

 

181

182 DATA &NAME..MMS2; /*MAKE PERM*/

183 MERGE &NAME..MMS (IN=IN1) &NAME..MMSDEL1 (IN=IN2); /*MAKE PERM*/

184 BY AUTHRZTN_RFRNC_NUM;

185 IF IN1 AND NOT IN2;

5 The SAS System 14:37 Tuesday, March 6, 2018

186 RUN;

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

NOTE: There were 0 observations read from the data set M290.MMSDEL1.

NOTE: The data set M290.MMS2 has 2467 observations and 17 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

187 ** PREPARE FOR JOIN WITH CLAIMS **;

188

189 DATA &NAME..MMS3 (KEEP=AUTH_NUM /*MAKE PERM*/

190 AUTH_TRKG_NUM

191 BENE_BIRTH_DT

192 BENE_SPNSR_SSN

193 BRNCH_SRVC_DESC

194 FIRST_CLOSED_DT

195 AUTHRZD_SRVC_FROM_DT

196 SBMTG_DMIS_ID

197 PCM_DMIS_ID

198 PCM_DMIS_NAME

199 PARNT_DMIS_ID

200 PARNT_DMIS_FAC_NM

201 EMSM_CD

202 FLAG

203 KEY);

204 SET &NAME..MMS2; /*MAKE PERM*/

205 FLAG='MMS';

206 AUTH_NUM=AUTHRZTN_2_NUM;

207 AUTH_TRKG_NUM=AUTHRZTN_RFRNC_NUM;

208 BENE_BIRTH_DT=BIRTH_DT;

209 BENE_SPNSR_SSN=SPNSR_SSN;

210 BRNCH_SRVC_DESC=SPNSR_BRNCH_OF_SRVC_DESC;

211 KEY=TRIM(BENE_SPNSR_SSN)!!TRIM(BENE_BIRTH_DT); /* KEY FOR MATCHING AGAINST CLMS */

212 RUN;

NOTE: Numeric values have been converted to character values at the places given by: (Line)Smiley SadColumn).

211:34

NOTE: There were 2467 observations read from the data set M290.MMS2.

NOTE: The data set M290.MMS3 has 2467 observations and 15 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

 

213

214 DATA &NAME..CLM1;

215 SET &NAME..CLM;

216 KEY=TRIM(SPNSR_SSN)!!TRIM(PTNT_BRTH_DT);

217 RUN;

NOTE: Numeric values have been converted to character values at the places given by: (Line)Smiley SadColumn).

216:30

NOTE: There were 75866 observations read from the data set M290.CLM.

NOTE: The data set M290.CLM1 has 75866 observations and 11 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

6 The SAS System 14:37 Tuesday, March 6, 2018

cpu time 0.02 seconds

 

218

219 PROC SORT DATA=&NAME..CLM1; /*MAKE PERM*/

220 BY CLM_BASE_NUM;

221 RUN;

NOTE: There were 75866 observations read from the data set M290.CLM1.

NOTE: The data set M290.CLM1 has 75866 observations and 11 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.04 seconds

cpu time 0.04 seconds

 

222

223 PROC SQL;

224 CREATE TABLE &NAME..MMSCLM AS /*MAKE PERM*/

225 SELECT DISTINCT

226 A.EMSM_CD,

227 A.BRNCH_SRVC_DESC,

228 A.PARNT_DMIS_ID,

229 A.PARNT_DMIS_FAC_NM,

230 A.PCM_DMIS_ID,

231 A.PCM_DMIS_NAME,

232 A.FIRST_CLOSED_DT,

233 A.AUTH_NUM,

234 A.AUTH_TRKG_NUM,

235 A.KEY,

236 CASE

237 WHEN B.CLM_BASE_NUM IS NOT MISSING THEN 1

238 ELSE 0

239 END AS CLMCNT

240 FROM &NAME..MMS3 AS A LEFT JOIN &NAME..CLM1 AS B /*MAKE PERM*/

241 ON (TRIM(LEFT(A.KEY))) = (TRIM(LEFT(B.KEY)))

242 and (B.CLNDR_DATE GE A.FIRST_CLOSED_DT

243 OR B.CLNDR_DATE GE AUTHRZD_SRVC_FROM_DT);

NOTE: Table M290.MMSCLM created, with 491 rows and 11 columns.

244 QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.06 seconds

cpu time 0.10 seconds

 

245

246 ** DETAILED REPORT VIEW **;

247

248 DATA &NAME..DETAIL;

249 SET &NAME..MMSCLM (DROP=PARNT_DMIS_FAC_NM);

250 WHERE CLMCNT = 0; /* UNACTIVATED REFERRALS ONLY */

251 RUN;

NOTE: There were 413 observations read from the data set M290.MMSCLM.

WHERE CLMCNT=0;

NOTE: The data set M290.DETAIL has 413 observations and 10 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds


Accepted Solutions
Solution
‎03-06-2018 04:59 PM
Super User
Posts: 6,935

Re: Numer of observations are off starting at line 222 - goes down considerably...

Yes, the number of observations goes down.  If you think that's wrong, you should easily be able to locate an observation that you think belongs in the data set but was not included.  Once you find one, the investigation can begin as to why it isn't included.

View solution in original post


All Replies
Super User
Posts: 13,942

Re: Numer of observations are off starting at line 222 - goes down considerably...

Assuming by "Line 222" that you mean that one or about line 222 in the attached log the indicated number of records doesn't match your expectations. Since Line 222 is actually blank do you mean the step before or following

 

Since we don't have your data we really can't tell. Is the number to large? Too small? How do you know? If you know the expected number of records then one of your conditions for selection, either a join or where clause has an issue.

Look for string = comparisons where case may be involved: "MAY" is not equal to  "May",

 

Or possibly DISTINCT is removing more than you expected based on combinations of variables.

Solution
‎03-06-2018 04:59 PM
Super User
Posts: 6,935

Re: Numer of observations are off starting at line 222 - goes down considerably...

Yes, the number of observations goes down.  If you think that's wrong, you should easily be able to locate an observation that you think belongs in the data set but was not included.  Once you find one, the investigation can begin as to why it isn't included.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 147 views
  • 1 like
  • 3 in conversation