1 The SAS System 11:54 Tuesday, July 31, 2018 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='p05_Load'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH='\\nashq1\SAS_HOA\ETL 5 ! Process\Dashboards\AdministrativeObservation\pgm\ELM_AdminObser_Official_MH.egp'; 6 %LET _CLIENTPROJECTPATHHOST='LHQ08907'; 7 %LET _CLIENTPROJECTNAME='ELM_AdminObser_Official_MH.egp'; 8 %LET _SASPROGRAMFILE='\\Nashq1\sas_hoa\ETL Process\Dashboards\AdministrativeObservation\pgm\p05_Load.sas'; 9 %LET _SASPROGRAMFILEHOST='LHQ08907'; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=PNG; 13 FILENAME EGSR TEMP; 14 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 15 STYLE=HTMLBlue 16 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css") 17 NOGTITLE 18 NOGFOOTNOTE 19 GPATH=&sasworklocation 20 ENCODING=UTF8 21 options(rolap="on") 22 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 23 24 /* Start of Node: p06_Load.sas */ 25 options options sastrace=',,,db' sastraceloc=saslog; 26 27 LIBNAME HOA ORACLE USER="&USR.[HOA]" PW="&PWD" PATH="@HOAP" SCHEMA=HOA; NOTE: Libref HOA was successfully assigned as follows: Engine: ORACLE Physical Name: @HOAP 28 29 PROC DELETE DATA=HOA.ADMIN_QA; 30 RUN; 9090 1848685467 tkvercn1 0 DELETE ( ORACLE_1: Prepared: on connection 1 9091 1848685467 tkvercn1 0 DELETE ( SELECT * FROM HOA.ADMIN_QA 9092 1848685467 tkvercn1 0 DELETE ( 9093 1848685467 tkvercn1 0 DELETE ( 9094 1848685467 tkvercn1 0 DELETE ( ORACLE_2: Executed: on connection 1 9095 1848685467 tkvercn1 0 DELETE ( DROP TABLE HOA.ADMIN_QA 9096 1848685467 tkvercn1 0 DELETE ( 9097 1848685467 tkvercn1 0 DELETE ( NOTE: Deleting HOA.ADMIN_QA (memtype=DATA). NOTE: PROCEDURE DELETE used (Total process time): real time 0.26 seconds cpu time 0.03 seconds 31 32 DATA HOA.ADMIN_QA; 33 SET D.SUMMARY&YEARMTH._0169; 34 LOAD_DATE=TODAY(); 35 FORMAT LOAD_DATE MMDDYY10.; 36 LENGTH LOAD_BY $20; 37 LOAD_BY="&_CLIENTUSERID."; 38 RUN; 2 The SAS System 11:54 Tuesday, July 31, 2018 9098 1848685468 tkvercn1 0 DATASTEP ORACLE_3: Prepared: on connection 1 9099 1848685468 tkvercn1 0 DATASTEP SELECT * FROM HOA.ADMIN_QA 9100 1848685468 tkvercn1 0 DATASTEP 9101 1848685468 tkvercn1 0 DATASTEP NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. 9102 1848685468 tkvercn1 0 DATASTEP ORACLE_4: Executed: on connection 2 9103 1848685468 tkvercn1 0 DATASTEP CREATE TABLE HOA.ADMIN_QA(YEARMTH DATE,PPG VARCHAR2 (64),LOB VARCHAR2 (56),PLAN_PARTNER VARCHAR2 (16),SEGMENT VARCHAR2 (64),FACILITY VARCHAR2 (800),FACILITY_ID VARCHAR2 (800),SUM_Administrative NUMBER ,SUM_MEMBER NUMBER ,Administrative_PTMPY NUMBER (7,2),LOAD_DATE DATE,LOAD_BY VARCHAR2 (80)) 9104 1848685468 tkvercn1 0 DATASTEP 9105 1848685468 tkvercn1 0 DATASTEP 9106 1848685468 tkvercn1 0 DATASTEP ORACLE_5: Prepared: on connection 2 9107 1848685468 tkvercn1 0 DATASTEP INSERT INTO HOA.ADMIN_QA (YEARMTH,PPG,LOB,PLAN_PARTNER,SEGMENT,FACILITY,FACILITY_ID,SUM_Administrative,SUM_MEMBER,Administrative_PTMPY,LOAD_DATE,LOAD_BY) VALUES (TO_DATE(:YEARMTH,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:PPG,:LOB,:PLAN_PARTNER,:SEGMENT,:FACILITY,:FACILITY_ID,:SUM_Administrat ive,:SUM_MEMBER,:Administrative_PTMPY,TO_DATE(:LOAD_DATE,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:LOAD_BY) 9108 1848685468 tkvercn1 0 DATASTEP 9109 1848685468 tkvercn1 0 DATASTEP **NOTE**: ORACLE_5 on connection 2The Execute statements associated with this Insert statement are suppressed due to SASTRACE brief setting-SASTRACE=',,,bd'. Remove the 'b' to get full trace. 9110 1848685468 tkvercn1 0 DATASTEP NOTE: There were 7103 observations read from the data set D.SUMMARY201807_0169. NOTE: The data set HOA.ADMIN_QA has 7103 observations and 12 variables. NOTE: DATA statement used (Total process time): real time 0.92 seconds cpu time 0.07 seconds 39 40 PROC DELETE DATA=HOA.OBSER_QA; 41 RUN; 9111 1848685468 tkvercn1 0 DELETE ( ORACLE_717: Prepared: on connection 1 9112 1848685468 tkvercn1 0 DELETE ( SELECT * FROM HOA.OBSER_QA 9113 1848685468 tkvercn1 0 DELETE ( 9114 1848685468 tkvercn1 0 DELETE ( 9115 1848685469 tkvercn1 0 DELETE ( ORACLE_718: Executed: on connection 1 9116 1848685469 tkvercn1 0 DELETE ( DROP TABLE HOA.OBSER_QA 9117 1848685469 tkvercn1 0 DELETE ( 9118 1848685469 tkvercn1 0 DELETE ( NOTE: Deleting HOA.OBSER_QA (memtype=DATA). NOTE: PROCEDURE DELETE used (Total process time): real time 0.11 seconds cpu time 0.00 seconds 42 43 DATA HOA.OBSER_QA; 44 SET D.SUMMARY&YEARMTH._0762; 45 LOAD_DATE=TODAY(); 46 FORMAT LOAD_DATE MMDDYY10.; 47 LENGTH LOAD_BY $20; 48 LOAD_BY="&_CLIENTUSERID."; 49 RUN; 9119 1848685469 tkvercn1 0 DATASTEP 3 The SAS System 11:54 Tuesday, July 31, 2018 ORACLE_719: Prepared: on connection 1 9120 1848685469 tkvercn1 0 DATASTEP SELECT * FROM HOA.OBSER_QA 9121 1848685469 tkvercn1 0 DATASTEP 9122 1848685469 tkvercn1 0 DATASTEP NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables. 9123 1848685469 tkvercn1 0 DATASTEP ORACLE_720: Executed: on connection 2 9124 1848685469 tkvercn1 0 DATASTEP CREATE TABLE HOA.OBSER_QA(YEARMTH DATE,PPG VARCHAR2 (64),LOB VARCHAR2 (56),PLAN_PARTNER VARCHAR2 (16),SEGMENT VARCHAR2 (64),FACILITY VARCHAR2 (800),FACILITY_ID VARCHAR2 (800),SUM_Observation NUMBER ,SUM_MEMBER NUMBER ,Observation_PTMPY NUMBER (7,2),LOAD_DATE DATE,LOAD_BY VARCHAR2 (80)) 9125 1848685469 tkvercn1 0 DATASTEP 9126 1848685469 tkvercn1 0 DATASTEP 9127 1848685469 tkvercn1 0 DATASTEP ORACLE_721: Prepared: on connection 2 9128 1848685469 tkvercn1 0 DATASTEP INSERT INTO HOA.OBSER_QA (YEARMTH,PPG,LOB,PLAN_PARTNER,SEGMENT,FACILITY,FACILITY_ID,SUM_Observation,SUM_MEMBER,Observation_PTMPY,LOAD_DATE,LOAD_BY) VALUES (TO_DATE(:YEARMTH,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:PPG,:LOB,:PLAN_PARTNER,:SEGMENT,:FACILITY,:FACILITY_ID,:SUM_Observation ,:SUM_MEMBER,:Observation_PTMPY,TO_DATE(:LOAD_DATE,'DDMONYYYY','NLS_DATE_LANGUAGE=American'),:LOAD_BY) 9129 1848685469 tkvercn1 0 DATASTEP 9130 1848685469 tkvercn1 0 DATASTEP **NOTE**: ORACLE_721 on connection 2The Execute statements associated with this Insert statement are suppressed due to SASTRACE brief setting-SASTRACE=',,,bd'. Remove the 'b' to get full trace. 9131 1848685469 tkvercn1 0 DATASTEP ERROR: ERROR: ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this column. With the occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been reached. ROLLBACK has been issued(Any Rows processed after the last COMMIT are lost). Total rows processed: 15422 Rows failed : 1 NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 15430 observations read from the data set D.SUMMARY201807_0762. WARNING: The data set HOA.OBSER_QA may be incomplete. When this step was stopped there were 15429 observations and 12 variables. ERROR: ROLLBACK issued due to errors for data set HOA.OBSER_QA.DATA. NOTE: DATA statement used (Total process time): real time 1.50 seconds cpu time 0.26 seconds 50 51 52 53 54 %LET _CLIENTTASKLABEL=; 55 %LET _CLIENTPROCESSFLOWNAME=; 56 %LET _CLIENTPROJECTPATH=; 57 %LET _CLIENTPROJECTPATHHOST=; 58 %LET _CLIENTPROJECTNAME=; 59 %LET _SASPROGRAMFILE=; 60 %LET _SASPROGRAMFILEHOST=; 61 62 ;*';*";*/;quit;run; 63 ODS _ALL_ CLOSE; 64 65 66 QUIT; RUN; 67