PROC DELETE DATA=HOA.OBSER_QA; RUN; DATA HOA.OBSER_QA; SET D.SUMMARY&YEARMTH._0762; LOAD_DATE=TODAY(); FORMAT LOAD_DATE MMDDYY10.; LENGTH LOAD_BY $20; LOAD_BY="&_CLIENTUSERID."; RUN; PROC DELETE DATA=HOA.OBSER_QA;
RUN;
DATA HOA.OBSER_QA;
SET D.SUMMARY&YEARMTH._0762;
LOAD_DATE=TODAY();
FORMAT LOAD_DATE MMDDYY10.;
LENGTH LOAD_BY $20;
LOAD_BY="&_CLIENTUSERID.";
RUN; Resource: http://support.sas.com/kb/40/132.html The error stops the process due to the options in SAS and/or Oracle. I cant use a pass through because the replacement dataset or lines are from a SAS dataset. LOG: 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
... View more
