NOTE: Remote submit to L18_1 commencing. MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_jobW6O7ZESS; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %sysrput etls_startTime_&handleName = %sysfunc(datetime()); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let IOMServer = %nrquote(besa); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let metaPort = %nrquote(8561); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let metaServer = %nrquote(s-bametasasd1); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let metaRepository = %nrquote(me_besa); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options metaport = &metaPort metaserver = "&metaServer" metarepository = "&metaRepository"; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let jobID = %quote(A50D7W6R.AQ00144N); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %global job_rc trans_rc sqlrc; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let job_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let sqlrc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %global syserr; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro rcSet(error); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&error gt &trans_rc) %then %let trans_rc = &error; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&error gt &job_rc) %then %let job_rc = &error; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend rcSet; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro rcSetDS(error); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): if &error gt input(symget('trans_rc'),12.) then call symput('trans_rc',trim(left(put(&error,12.)))); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): if &error gt input(symget('job_rc'),12.) then call symput('job_rc',trim(left(put(&error,12.)))); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend rcSetDS; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %global etls_debug; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_setDebug; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if &etls_debug ne 0 %then OPTIONS MPRINT; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_setDebug; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro genSASCommand; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local noobjserver; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if "&sysscp"="OS" %then %let noobjserver=; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %if "&sysscp"="VMS_AXP" %then %let noobjserver=%quote(/noobjectserver); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %let noobjserver=%quote(-noobjectserver); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options sascmd = "!sascmd &noobjserver"; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend genSASCommand; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %genSASCommand; 221 The SAS System 12:24 Tuesday, January 12, 2016 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_tsLevel(macroName=, featureName=); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data _null_; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): call symput("¯oName",tslvl("&featureName")); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_tsLevel; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_signon(handleName=etlsRemote, cmacvar=etls_signonStatus, gridRC=rc, useGrid=1, machineIdMacroVariable=etls_machineId, workloadMacroVariable=, log=, output=, additionalSignonOptions=, signonRetries=3); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_gridInstalled =; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_tsLevel(macroName=etls_gridInstalled, featureName=uwugrdsvc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&useGrid = 1) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&etls_gridInstalled" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&gridRC eq ) %then %let gridRC = &handleName.RC ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %global &gridRC; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let workload = ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&workloadMacroVariable ne ) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data _null_; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): call symput('workload',';workload = '||symget("&workloadMacroVariable")); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &gridRC = %sysfunc(grdsvc_enable("&handleName","resource = besa &workload ")); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %put NOTE: Grid service functions are not installed.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local sleeptime sleepIncreaseAmount tryCount; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let sleepIncreaseAmount=5; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let sleeptime=5; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let tryCount=1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &cmacvar=1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %do %until((&&&cmacvar ne 1) or (&tryCount gt %eval(&signonRetries+1))); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data _null_; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): signon &handleName cmacvar=&cmacvar %if ("&log" ne "") %then log=&log; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&output" ne "") %then output=&output; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&additionalSignonOptions" ne "") %then &additionalSignonOptions; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&machineIdMacroVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&etls_gridInstalled" ne "") %then %let &machineIdmacroVariable = %sysfunc(grdsvc_getname(&handleName)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %put NOTE: Grid service functions are not installed.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if "&&&cmacvar" eq "1" %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if &tryCount lt %eval(&signonRetries+1) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put NOTE: Signon attempt #&tryCount failed. Retrying in &sleeptime seconds.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let rc=%sysfunc(sleep(&sleeptime,1)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %if &signonRetries gt 0 %then %put ERROR%QUOTE(:) Final signon attempt, #&tryCount, failed.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let sleeptime=%sysevalf(&sleeptime+&sleepIncreaseAmount,int); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let tryCount=%eval(&tryCount+1); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syserr); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_signon; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_serverConnected(serverId=,macvarname=isConnected); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &macvarname=0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local search_str none_str savenotes; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if %bquote(&sysscp) eq OS %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let search_str=Remote submit for; 222 The SAS System 12:24 Tuesday, January 12, 2016 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let token=5; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let search_str=--------------; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let token=1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): filename __tmptxt TEMP; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let savenotes=%sysfunc(getoption(notes)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options nonotes; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc printto log=__tmptxt new; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options notes; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): listtask; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc printto; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options nonotes; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let dsname=_null_; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data _null_(keep=Session); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): length line $256 sessionId $60; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): infile __tmptxt length=len; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): input @1 line $varying. len; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): if line=:'There are no' then stop; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): if index(line,"&search_str") then do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): sessionId=scan(line,&token," "); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): sessionId=upcase(trim(left(tranwrd(sessionId,'"',' ')))); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): if sessionId=upcase(symget("serverId")) then do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): call symput("&macvarname","1"); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): stop; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): filename __tmptxt clear; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options &savenotes; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_serverConnected; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_signoff(handleName=rmt); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local savemprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let savemprint=%sysfunc(getoption(mprint)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put NOTE: Testing connection with etls_serverConnected macro (mprint off).; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options nomprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local isConnected; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_serverConnected(serverId=&handleName); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): options &savemprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if &isConnected %then signoff &handleName ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_signoff; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_getHandle(statusTable=, handleVariable=, row=); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_dsid = %sysfunc(open(&statusTable)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_dsid = 0) %then %put %sysfunc(sysmsg()); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let rc = %sysfunc(fetchobs(&etls_dsid, &row)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&rc ne 0) %then %put %sysfunc(sysmsg()); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_varnum = %sysfunc(varnum(&etls_dsid,&handleVariable)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_varnum > 0) %then %sysfunc(getvarc(&etls_dsid,&etls_varnum)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %put %sysfunc(sysmsg()); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let rc = %sysfunc(close(&etls_dsid)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; 223 The SAS System 12:24 Tuesday, January 12, 2016 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_getHandle; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_freeHandle(statusTable=, statusVariable=, handleVariable=, handleName=, statusSetting="Finished", endTimeVariable=endTime, startTimeVariable=startTime, signoff=1, returnCodeVariable=, returnCodeMacroVariable=, setMainJobRC=1, statusUnknownReturnCode=., startTimeMacroVariable=, endTimeMacroVariable= ); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&statusTable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local etls_rcMacroVarExisted; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_rcMacroVarExisted = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&returnCodeMacroVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql noprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): select '1' into: etls_rcMacroVarExisted from dictionary.macros where name=upcase("&returnCodeMacroVariable"); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_rcMacroVarExisted = 0) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put WARNING: Return code from inner job not found. Setting status to Unknown.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &returnCodeMacroVariable=&statusUnknownReturnCode; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&setMainJobRC eq 1) %then %rcSet(&&&returnCodeMacroVariable); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let returnCodeMacroVariable=etls_rcmacvar; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &returnCodeMacroVariable=&statusUnknownReturnCode; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local etls_startTimeMacroVarExisted; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_startTimeMacroVarExisted = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&startTimeMacroVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql noprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): select '1' into: etls_startTimeMacroVarExisted from dictionary.macros where name=upcase("&startTimeMacroVariable"); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_startTimeMacroVarExisted = 0) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put WARNING: Start time from inner job not found. No value will be set.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &startTimeMacroVariable=; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local etls_endTimeMacroVarExisted; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_endTimeMacroVarExisted = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&endTimeMacroVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql noprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): select '1' into: etls_endTimeMacroVarExisted from dictionary.macros where name=upcase("&endTimeMacroVariable"); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_endTimeMacroVarExisted = 0) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put WARNING: End time from inner job not found. Setting end time to current time.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &endTimeMacroVariable=%sysfunc(datetime()); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data &statusTable; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): modify &statusTable(where=(&handleVariable = &handleName)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&startTimeMacroVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): &startTimeVariable = input(symget("&startTimeMacroVariable"),32.); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&endTimeVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&endTimeMacroVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): &endTimeVariable = input(symget("&endTimeMacroVariable"),32.); 224 The SAS System 12:24 Tuesday, January 12, 2016 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else &endTimeVariable = datetime(); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&returnCodeVariable" ne "") %then &returnCodeVariable = input(symget("&returnCodeMacroVariable"),32.); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&statusVariable" ne "") %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): if (symget("etls_rcMacroVarExisted") eq "0") then &statusVariable = "Unknown Status"; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): else &statusVariable = &statusSetting; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): call symput('handle',&handleVariable); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): replace; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): stop; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syserr); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&signoff eq 1) %then %etls_signoff(handleName=&handle); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_freeHandle; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_createHandle(statusTable=, statusVariable=, handleVariable=, handlePrefix=rmt, workloadMacroVariable=, row=, machineVariable=, statusSetting="Running", startTimeVariable=startTime, signon=1, useGrid=1, log=, output=, gridRC=, cmacvar=etls_signonStatus, additionalSignonOptions=, signonRetries= ); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local remoteSessionId; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let remoteSessionId = &handlePrefix.&row; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let &cmacvar = 1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local etls_machineId; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&signon eq 1) %then %etls_signon(handleName=&remoteSessionId, useGrid=&useGrid, machineIdMacroVariable=etls_machineId, workloadMacroVariable=&workloadMacroVariable, log=&log, output=&output, cmacvar=&cmacvar, gridRC=&gridRC, additionalSignonOptions=&additionalSignonOptions,signonRetries=&signonRetries); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %let &cmacvar=0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data &statusTable; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): retain ptr &row; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): modify &statusTable point = ptr; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): &handleVariable = "&remoteSessionId"; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&signon eq 1) %then &machineVariable = "&etls_machineId"; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&&&cmacvar ne 0) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): &statusVariable = "Failed Signon"; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else &statusVariable = &statusSetting; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&startTimeVariable ne ) %then &startTimeVariable = datetime(); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): replace; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): stop; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syserr); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_createHandle; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_waitfor(statusTable=, statusVariable=, runningStatusSetting="Running", handleVariable=, completeStatusSetting="Finished", endTimeVariable=endTime, startTimeVariable=startTime, waitType=_ANY_, signoff=1, returnCodeVariable=, returnCodeMacroVariable=, statusUnknownReturnCode=.); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql noprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): select count(*) into :etls_rows from &statusTable where &statusVariable = &runningStatusSetting; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_rows = &etls_rows; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_rows gt 0) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): select &handleVariable into :etlsHandles1 - :etlsHandles&etls_rows from &statusTable where 225 The SAS System 12:24 Tuesday, January 12, 2016 &statusVariable = &runningStatusSetting; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_rows gt 0) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): waitfor &waitType %do i=1 %to &etls_rows; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): &&etlsHandles&i %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %local useDefaultRCMacVar; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&returnCodeMacroVariable" eq "") %then %let useDefaultRCMacVar=Y; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %do i=1 %to &etls_rows; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if ("&useDefaultRCMacVar"="Y") %then %let returnCodeMacroVariable=job_rc&&etlsHandles&i; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): waitfor &&etlsHandles&i timeout=1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&SYSRC eq 0) or (&SYSRC eq -2) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_freeHandle(statusTable=&statusTable, statusVariable=&statusVariable, handleVariable=&handleVariable, handleName="&&etlsHandles&i", statusSetting=&completeStatusSetting, endTimeVariable=&endTimeVariable, startTimeVariable=&startTimeVariable, signoff=&signoff, returnCodeVariable=&returnCodeVariable, returnCodeMacroVariable=&returnCodeMacroVariable, statusUnknownReturnCode=&statusUnknownReturnCode, startTimeMacroVariable=etls_startTime_&&etlsHandles&i., endTimeMacroVariable=etls_endTime_&&etlsHandles&i. ); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put NOTE: Process &&etlsHandles&i. has completed.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %else %if (&SYSRC ne -1) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_freeHandle(statusTable=&statusTable, statusVariable=&statusVariable, handleVariable=&handleVariable, handleName="&&etlsHandles&i", statusSetting="Unknown Status", endTimeVariable=&endTimeVariable, startTimeVariable=&startTimeVariable, signoff=&signoff, returnCodeVariable=&returnCodeVariable, returnCodeMacroVariable=&returnCodeMacroVariable, statusUnknownReturnCode=&statusUnknownReturnCode, startTimeMacroVariable=etls_startTime_&&etlsHandles&i ); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put NOTE: Cannot retrieve status from process &&etlsHandles&i..; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_waitfor; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_getProcessesRunning(statusTable=, statusVariable=, processCountMacro=, statusSetting="Running"); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql noprint; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): select count(*) into: &processCountMacro from &statusTable where &statusVariable = &statusSetting; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_getProcessesRunning; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): LIBNAME ODSBESA ORACLE PATH=DWCPD1 PATH=DWCPD1 SCHEMA=ODSBESA USER=odsbesa PASSWORD="{sas001}b2RzYmVzYSMyMDEw" ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syslibrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = %nrquote(ODSBESA.TCONTA); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030B3); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete W6NK6WAN; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create table work.W6NK6WAN as select IDENT length = 4 label = 'IDENT', CDCONTRATO length = 16 label = 'CDCONTRATO', CDCLIENTE length = 9 label = 'CDCLIENTE', CDMOEDA length = 3 label = 'CDMOEDA', (MTACTUALLC) as MTACTUALLC length = 8 format = 25.3 informat = 25.3 from &SYSLAST where DTCREATE = &dataparam. & CDCLIENTE ^= '00000000'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = work.W6NK6WAN; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = %nrquote(ODSBESA.TTRANSACCOES); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030B2); 226 The SAS System 12:24 Tuesday, January 12, 2016 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete W6N4VID0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create table work.W6N4VID0 as select distinct IDENT length = 4 label = 'IDENT', CDCLIENTE length = 9 label = 'CDCLIENTE' from &SYSLAST where DTTRANSACCAO >= intnx('dtmonth',&dataparam, -6,'b') AND DTTRANSACCAO <= &dataParam. and INCONTCONTAB='A' and CDPRODUTO <> 'DMNT'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = work.W6N4VID0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030AY); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete W6NINDD1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; NOTE: Line generated by the invoked macro "ETLS_PROCESSTOLOOPW6O7ZESI". 7917 case when (TCLIENTE.CDBALCAO in ( '951' ) and W6NK6WAN.MTACTUALLC >= 2014 ) then '0'when ___ 49 7917 ! (W6NK6WAN.MTACTUALLC < 2014 ) then 'SALDO INSUF.' else '14' end as IMPOSTO length = 15 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create table work.W6NINDD1 as select distinct TCONTRATO.CDCLIENTE length = 9 format = $9. informat = $9. label = 'CDCLIENTE', TCLIENTE.LNCLIENTE length = 80 format = $80. informat = $80. label = 'LNCLIENTE', TCONTRATO.CDCONTRATO length = 16 format = $16. informat = $16. label = 'CDCONTRATO', TCONTRATO.CDPRODUTO length = 4 format = $4. informat = $4. label = 'CDPRODUTO', TCONTRATO.CDBALCAO length = 3 format = $3. informat = $3. label = 'CDBALCAO', TCONTRATO.CDMOEDA length = 3 format = $3. informat = $3. label = 'CDMOEDA', W6NK6WAN.MTACTUALLC length = 8 format = 25.3 informat = 25.3 label = 'MTACTUALLC', case when (W6NK6WAN.MTACTUALLC >= 2014) then '2000' else 'SALDO INSUF.' end as COMISSAO length = 15 format = $15. informat = $15. label = 'COMISSAO', case when TCLIENTE.CDBALCAO in ( '026' , '001' , '009') then 'Private' else 'Não Private' end as TP_CLIENTE length = 15 format = $15. informat = $15. label = 'TP_CLIENTE', put(datepart(&dataparam),yymmdd10.) as DTPROC length = 10 format = $10. informat = $10. label = 'DTPROC', case when (TCLIENTE.CDBALCAO in ( '951' ) and W6NK6WAN.MTACTUALLC >= 2014 ) then '0'when (W6NK6WAN.MTACTUALLC < 2014 ) then 'SALDO INSUF.' else '14' end as IMPOSTO length = 15 format = $15. informat = $15. label = 'IMPOSTO', TCATEGORIACLI.LICATEGCLIENTE length = 80 format = $80. informat = $80. label = 'LICATEGCLIENTE', TCLIENTE.IDENT length = 4 format = $4. informat = $4. label = 'IDENT' from work.W6N4VID0 right join ODSBESA.TCONTRATO on ( W6N4VID0.IDENT = TCONTRATO.IDENT and W6N4VID0.CDCLIENTE = TCONTRATO.CDCLIENTE ) left join ODSBESA.TCLIENTE on ( &dataparam. BETWEEN TCLIENTE.DTCHANGE and TCLIENTE.DTENDCHANGE and TCLIENTE.IDENT = TCONTRATO.IDENT and TCLIENTE.CDCLIENTE = TCONTRATO.CDCLIENTE and TCLIENTE.CDCLIENTE <> '00000000' ) left join work.W6NK6WAN on ( W6NK6WAN.CDCLIENTE = TCONTRATO.CDCLIENTE and W6NK6WAN.CDMOEDA = TCONTRATO.CDMOEDA and W6NK6WAN.CDCONTRATO = TCONTRATO.CDCONTRATO ) left join ODSBESA.TCATEGORIACLI on ( TCLIENTE.CDCATEGCLIENTE = TCATEGORIACLI.CDCATEGCLIENTE ) where &dataparam. BETWEEN TCONTRATO.DTCHANGE and TCONTRATO.DTENDCHANGE and TCONTRATO.CDCLIENTE <> '00000000' and TCONTRATO.CDMODULO = 'CA' and TCONTRATO.CDPRODUTO IN ('PNMR' ,'CURR', 'TPRV' , 'TNMR' , 'PRIV', 'NUMR', 'SALR') and W6N4VID0.CDCLIENTE IS NULL ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_completeTarget(); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_completeTarget; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_completeTarget; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = %nrquote(work.W6NINDD1); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030B5); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete W6NKOVYO; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create table work.W6NKOVYO as select CDCLIENTE length = 9 label = 'CDCLIENTE', LNCLIENTE length = 80 label = 'LNCLIENTE', CDCONTRATO length = 16 label = 'CDCONTRATO', CDPRODUTO length = 4 label = 'CDPRODUTO', CDBALCAO length = 3 label = 'CDBALCAO', CDMOEDA length = 3 label = 'CDMOEDA', MTACTUALLC length = 8 label = 'MTACTUALLC', COMISSAO length = 15 label = 'COMISSAO', TP_CLIENTE length = 15 label = 'TP_CLIENTE', DTPROC length = 10 label = 'DTPROC', IMPOSTO length = 15 label = 'IMPOSTO', LICATEGCLIENTE length = 80 label = 'LICATEGCLIENTE', IDENT length = 4 label = 'IDENT', (sum(MTACTUALLC)) as SUM_MTACTUALLC length = 8 from &SYSLAST group by CDCLIENTE; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = work.W6NKOVYO; 227 The SAS System 12:24 Tuesday, January 12, 2016 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = %nrquote(work.W6NKOVYO); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030B6); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete W6NKOYEV; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create table work.W6NKOYEV as select CDCLIENTE length = 9 label = 'CDCLIENTE', LNCLIENTE length = 80 label = 'LNCLIENTE', CDCONTRATO length = 16 label = 'CDCONTRATO', CDPRODUTO length = 4 label = 'CDPRODUTO', CDBALCAO length = 3 label = 'CDBALCAO', CDMOEDA length = 3 label = 'CDMOEDA', MTACTUALLC length = 8 label = 'MTACTUALLC', COMISSAO length = 15 label = 'COMISSAO', TP_CLIENTE length = 15 label = 'TP_CLIENTE', DTPROC length = 10 label = 'DTPROC', IMPOSTO length = 15 label = 'IMPOSTO', LICATEGCLIENTE length = 80 label = 'LICATEGCLIENTE', IDENT length = 4 label = 'IDENT' from &SYSLAST where SUM_MTACTUALLC < 20000; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = work.W6NKOYEV; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = %nrquote(work.W6NKOYEV); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030B7); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let _INPUT1 = work.W6NKOYEV; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let _OUTPUT = work.W6NKP0CJ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let _OUTPUT1 = work.W6NKP0CJ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib=work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete W6NKP0CJ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let data_col = %nrquote(DTPROC); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let rel_table = %nrquote(GLBBESA.S_ODS_DW148); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let data_apagar = %nrquote(&dataparam); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let keep = CDCLIENTE LNCLIENTE CDCONTRATO CDPRODUTO CDBALCAO CDMOEDA MTACTUALLC COMISSAO TP_CLIENTE DTPROC IMPOSTO LICATEGCLIENTE IDENT; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro connect_oracle(schema); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): connect to ORACLE ( %IF %UPCASE(&schema)= CTBBESA %THEN %DO; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): PATH = &ORACLE_CTB_SID USER = &ORACLE_CTB_USER PASSWORD = &ORACLE_CTB_PASSWORD %END; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %ELSE %IF %UPCASE(&schema)= GLBBESA %THEN %DO; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): PATH = &ORACLE_DM_SID USER = &ORACLE_DM_USER PASSWORD = &ORACLE_DM_PASSWORD %END; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): ) %mend connect_oracle; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro tab_oracle(tabela); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let schema=%scan(&tabela,1); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %IF %UPCASE(&schema) = GLBBESA %THEN %DO; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %sysfunc(cats(DMBESA,.,%scan(&tabela,2))) %END; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %ELSE %DO; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): &tabela %END; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend tab_oracle; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data _null_; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): format a Datetime.; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): a = &data_apagar; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): call symput("data_apagarN",put(datepart(a),YYMMDD10.)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %connect_oracle(%scan(&rel_table,1)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): execute( delete from %tab_oracle(&rel_table) where &data_col = %bquote(')&data_apagarN%bquote(')) by oracle; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): disconnect from ORACLE; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create view &_OUTPUT1 as select * from &_INPUT1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; 228 The SAS System 12:24 Tuesday, January 12, 2016 MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syserr); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sysrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): LIBNAME GLBBESA ORACLE PATH=DWCPD1 PATH=DWCPD1 SCHEMA=DMBESA USER=dmbesa PASSWORD="{sas001}ZG1iZXNhIzIwMTA=" ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syslibrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030G3); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete W6O48460; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create table work.W6O48460 as select W6NKP0CJ.CDCLIENTE length = 9 format = $9. informat = $9. label = 'CDCLIENTE', W6NKP0CJ.LNCLIENTE length = 80 format = $80. informat = $80. label = 'LNCLIENTE', W6NKP0CJ.CDCONTRATO length = 16 format = $16. informat = $16. label = 'CDCONTRATO', W6NKP0CJ.CDPRODUTO length = 4 format = $4. informat = $4. label = 'CDPRODUTO', W6NKP0CJ.CDBALCAO length = 3 format = $3. informat = $3. label = 'CDBALCAO', W6NKP0CJ.CDMOEDA length = 3 format = $3. informat = $3. label = 'CDMOEDA', W6NKP0CJ.MTACTUALLC length = 8 format = 25.3 informat = 25.3 label = 'MTACTUALLC', W6NKP0CJ.COMISSAO length = 15 format = $15. informat = $15. label = 'COMISSAO', W6NKP0CJ.TP_CLIENTE length = 15 format = $15. informat = $15. label = 'TP_CLIENTE', W6NKP0CJ.DTPROC length = 10 format = $10. informat = $10. label = 'DTPROC', W6NKP0CJ.IMPOSTO length = 15 format = $15. informat = $15. label = 'IMPOSTO', W6NKP0CJ.LICATEGCLIENTE length = 80 format = $80. informat = $80. label = 'LICATEGCLIENTE', W6NKP0CJ.IDENT length = 4 format = $4. informat = $4. label = 'IDENT' from work.W6NKP0CJ left join GLBBESA.S_ODS_DW148 on ( W6NKP0CJ.CDCLIENTE = S_ODS_DW148.CDCLIENTE and W6NKP0CJ.CDCONTRATO = S_ODS_DW148.CDCONTRATOREL ) where S_ODS_DW148.CDCLIENTE IS NULL ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&sqlrc); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_completeTarget(); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_completeTarget; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_completeTarget; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = %nrquote(work.W6O48460); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let transformID = %quote(A50D7W6R.AR0030AW); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let trans_rc = 0; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %global etls_tableExist; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %global etls_numIndex; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %global etls_lastTable; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_tableExist = -1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_numIndex = -1; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_lastTable = &SYSLAST; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %macro etls_loader; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSOPT = ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete mapped; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put NOTE: Mapping columns ...; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc sql; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): create view work.mapped as select IDENT length = 4, CDCLIENTE length = 9, LNCLIENTE length = 80, CDCONTRATO as CDCONTRATOREL length = 16, CDPRODUTO length = 4, CDBALCAO length = 3, CDMOEDA length = 3, MTACTUALLC as SALDO_AOA length = 8 format = 20.3 informat = 20.3, COMISSAO length = 15, IMPOSTO length = 15, TP_CLIENTE as TPCLIENTE length = 15, LICATEGCLIENTE length = 80, DTPROC length = 10 from &etls_lastTable; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSLAST = work.mapped; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_lastTable = &SYSLAST; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let SYSOPT = ; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %let etls_tableExist = %eval(%sysfunc(exist(GLBBESA.S_ODS_DW148, DATA)) or %sysfunc(exist(GLBBESA.S_ODS_DW148, VIEW))); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %if (&etls_tableExist eq 0) %then %do; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put NOTE: Creating table ...; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): data GLBBESA.S_ODS_DW148 (dbnull = ( IDENT = YES CDCLIENTE = YES LNCLIENTE = YES CDCONTRATOREL = YES CDPRODUTO = YES CDBALCAO = YES CDMOEDA = YES SALDO_AOA = YES COMISSAO = YES IMPOSTO = YES TPCLIENTE = YES 229 The SAS System 12:24 Tuesday, January 12, 2016 LICATEGCLIENTE = YES DTPROC = YES)); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib IDENT length = $4 format = $4. informat = $4. label = 'IDENT'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib CDCLIENTE length = $9 format = $9. informat = $9. label = 'CDCLIENTE'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib LNCLIENTE length = $80 format = $80. informat = $80. label = 'LNCLIENTE'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib CDCONTRATOREL length = $16 format = $16. informat = $16. label = 'CDCONTRATOREL'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib CDPRODUTO length = $4 format = $4. informat = $4. label = 'CDPRODUTO'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib CDBALCAO length = $3 format = $3. informat = $3. label = 'CDBALCAO'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib CDMOEDA length = $3 format = $3. informat = $3. label = 'CDMOEDA'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib SALDO_AOA length = 8 format = 20.3 informat = 20.3 label = 'SALDO_AOA'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib COMISSAO length = $15 format = $15. informat = $15. label = 'COMISSAO'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib IMPOSTO length = $15 format = $15. informat = $15. label = 'IMPOSTO'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib TPCLIENTE length = $15 format = $15. informat = $15. label = 'TPCLIENTE'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib LICATEGCLIENTE length = $80 format = $80. informat = $80. label = 'LICATEGCLIENTE'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): attrib DTPROC length = $10 format = $10. informat = $10. label = 'DTPROC'; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): stop; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syserr); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %end; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %put NOTE: Appending data ...; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc append base = GLBBESA.S_ODS_DW148 data = &etls_lastTable (&SYSOPT) force; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): run; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %rcSet(&syserr); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): delete mapped; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): quit; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_loader; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_loader; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %sysrput job_rc&handleName = &job_rc; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %sysrput etls_endTime_&handleName = %sysfunc(datetime()); MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %mend etls_jobW6O7ZESS; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): %etls_jobW6O7ZESS; MPRINT(ETLS_PROCESSTOLOOPW6O7ZESI): endrsubmit; 1 %macro etls_jobW6O7ZESS; 2 %sysrput etls_startTime_&handleName = %sysfunc(datetime()); 3 %let IOMServer = %nrquote(besa); 4 %let metaPort = %nrquote(8561); 5 %let metaServer = %nrquote(s-bametasasd1); 6 %let metaRepository = %nrquote(me_besa); 218 The SAS System 12:28 Tuesday, January 12, 2016 7 options metaport = &metaPort metaserver = "&metaServer" metarepository = "&metaRepository"; 8 %let jobID = %quote(A50D7W6R.AQ00144N); 9 %global job_rc trans_rc sqlrc; 10 %let job_rc = 0; 11 %let trans_rc = 0; 12 %let sqlrc = 0; 13 %global syserr; 14 %macro rcSet(error); 15 %if (&error gt &trans_rc) %then %let trans_rc = &error; 16 %if (&error gt &job_rc) %then %let job_rc = &error; 17 %mend rcSet; 18 %macro rcSetDS(error); 19 if &error gt input(symget('trans_rc'),12.) then call symput('trans_rc',trim(left(put(&error,12.)))); 20 if &error gt input(symget('job_rc'),12.) then call symput('job_rc',trim(left(put(&error,12.)))); 21 %mend rcSetDS; 22 %global etls_debug; 23 %macro etls_setDebug; 24 %if &etls_debug ne 0 %then OPTIONS MPRINT; 25 ; 26 %mend; 27 %etls_setDebug; 28 %macro genSASCommand; 29 %local noobjserver; 30 %if "&sysscp"="OS" %then %let noobjserver=; 31 %else %if "&sysscp"="VMS_AXP" %then %let noobjserver=%quote(/noobjectserver); 32 %else %let noobjserver=%quote(-noobjectserver); 33 options sascmd = "!sascmd &noobjserver"; 34 %mend genSASCommand; 35 %genSASCommand; 36 %macro etls_tsLevel(macroName=, featureName=); 37 data _null_; 38 call symput("¯oName",tslvl("&featureName")); 39 run; 40 %mend etls_tsLevel; 41 %macro etls_signon(handleName=etlsRemote, cmacvar=etls_signonStatus, gridRC=rc, useGrid=1, 41 ! machineIdMacroVariable=etls_machineId, workloadMacroVariable=, log=, output=, additionalSignonOptions=, signonRetries=3); 42 %let etls_gridInstalled =; 43 %etls_tsLevel(macroName=etls_gridInstalled, featureName=uwugrdsvc); 44 %if (&useGrid = 1) %then %do; 45 %if ("&etls_gridInstalled" ne "") %then %do; 46 %if (&gridRC eq ) %then %let gridRC = &handleName.RC ; 47 %global &gridRC; 48 %let workload = ; 49 %if (&workloadMacroVariable ne ) %then %do; 50 data _null_; 51 call symput('workload',';workload = '||symget("&workloadMacroVariable")); 52 run; 53 %end; 54 %let &gridRC = %sysfunc(grdsvc_enable("&handleName","resource = besa &workload ")); 55 %end; 56 %else %put NOTE: Grid service functions are not installed.; 57 %end; 58 %local sleeptime sleepIncreaseAmount tryCount; 59 %let sleepIncreaseAmount=5; 60 %let sleeptime=5; 61 %let tryCount=1; 62 %let &cmacvar=1; 63 %do %until((&&&cmacvar ne 1) or (&tryCount gt %eval(&signonRetries+1))); 219 The SAS System 12:28 Tuesday, January 12, 2016 64 data _null_; 65 signon &handleName cmacvar=&cmacvar %if ("&log" ne "") %then log=&log; 66 %if ("&output" ne "") %then output=&output; 67 %if ("&additionalSignonOptions" ne "") %then &additionalSignonOptions; 68 ; 69 run; 70 %if ("&machineIdMacroVariable" ne "") %then %do; 71 %if ("&etls_gridInstalled" ne "") %then %let &machineIdmacroVariable = %sysfunc(grdsvc_getname(&handleName)); 72 %else %put NOTE: Grid service functions are not installed.; 73 %end; 74 %if "&&&cmacvar" eq "1" %then %do; 75 %if &tryCount lt %eval(&signonRetries+1) %then %do; 76 %put NOTE: Signon attempt #&tryCount failed. Retrying in &sleeptime seconds.; 77 %let rc=%sysfunc(sleep(&sleeptime,1)); 78 %end; 79 %else %if &signonRetries gt 0 %then %put ERROR%QUOTE(:) Final signon attempt, #&tryCount, failed.; 80 %let sleeptime=%sysevalf(&sleeptime+&sleepIncreaseAmount,int); 81 %let tryCount=%eval(&tryCount+1); 82 %end; 83 %end; 84 %rcSet(&syserr); 85 %mend etls_signon; 86 %macro etls_serverConnected(serverId=,macvarname=isConnected); 87 %let &macvarname=0; 88 %local search_str none_str savenotes; 89 %if %bquote(&sysscp) eq OS %then %do; 90 %let search_str=Remote submit for; 91 %let token=5; 92 %end; 93 %else %do; 94 %let search_str=--------------; 95 %let token=1; 96 %end; 97 filename __tmptxt TEMP; 98 %let savenotes=%sysfunc(getoption(notes)); 99 options nonotes; 100 proc printto log=__tmptxt new; 101 run; 102 options notes; 103 listtask; 104 proc printto; 105 run; 106 options nonotes; 107 %let dsname=_null_; 108 data _null_(keep=Session); 109 length line $256 sessionId $60; 110 infile __tmptxt length=len; 111 input @1 line $varying. len; 112 if line=:'There are no' then stop; 113 if index(line,"&search_str") then do; 114 sessionId=scan(line,&token," "); 115 sessionId=upcase(trim(left(tranwrd(sessionId,'"',' ')))); 116 if sessionId=upcase(symget("serverId")) then do; 117 call symput("&macvarname","1"); 118 stop; 119 end; 120 end; 121 run; 220 The SAS System 12:28 Tuesday, January 12, 2016 122 filename __tmptxt clear; 123 options &savenotes; 124 %mend etls_serverConnected; 125 %macro etls_signoff(handleName=rmt); 126 %local savemprint; 127 %let savemprint=%sysfunc(getoption(mprint)); 128 %put NOTE: Testing connection with etls_serverConnected macro (mprint off).; 129 options nomprint; 130 %local isConnected; 131 %etls_serverConnected(serverId=&handleName); 132 options &savemprint; 133 %if &isConnected %then signoff &handleName ; 134 ; 135 %mend etls_signoff; 136 %macro etls_getHandle(statusTable=, handleVariable=, row=); 137 %let etls_dsid = %sysfunc(open(&statusTable)); 138 %if (&etls_dsid = 0) %then %put %sysfunc(sysmsg()); 139 %else %do; 140 %let rc = %sysfunc(fetchobs(&etls_dsid, &row)); 141 %if (&rc ne 0) %then %put %sysfunc(sysmsg()); 142 %else %do; 143 %let etls_varnum = %sysfunc(varnum(&etls_dsid,&handleVariable)); 144 %if (&etls_varnum > 0) %then %sysfunc(getvarc(&etls_dsid,&etls_varnum)); 145 %else %put %sysfunc(sysmsg()); 146 %end; 147 %let rc = %sysfunc(close(&etls_dsid)); 148 %end; 149 %mend etls_getHandle; 150 %macro etls_freeHandle(statusTable=, statusVariable=, handleVariable=, handleName=, statusSetting="Finished", 150! endTimeVariable=endTime, startTimeVariable=startTime, signoff=1, returnCodeVariable=, returnCodeMacroVariable=, setMainJobRC=1, 151 statusUnknownReturnCode=., startTimeMacroVariable=, endTimeMacroVariable= ); 152 %if ("&statusTable" ne "") %then %do; 153 %local etls_rcMacroVarExisted; 154 %let etls_rcMacroVarExisted = 0; 155 %if ("&returnCodeMacroVariable" ne "") %then %do; 156 proc sql noprint; 157 select '1' into: etls_rcMacroVarExisted from dictionary.macros where name=upcase("&returnCodeMacroVariable"); 158 quit; 159 %rcSet(&sqlrc); 160 %if (&etls_rcMacroVarExisted = 0) %then %do; 161 %put WARNING: Return code from inner job not found. Setting status to Unknown.; 162 %let &returnCodeMacroVariable=&statusUnknownReturnCode; 163 %end; 164 %if (&setMainJobRC eq 1) %then %rcSet(&&&returnCodeMacroVariable); 165 %end; 166 %else %do; 167 %let returnCodeMacroVariable=etls_rcmacvar; 168 %let &returnCodeMacroVariable=&statusUnknownReturnCode; 169 %end; 170 %local etls_startTimeMacroVarExisted; 171 %let etls_startTimeMacroVarExisted = 0; 172 %if ("&startTimeMacroVariable" ne "") %then %do; 173 proc sql noprint; 174 select '1' into: etls_startTimeMacroVarExisted from dictionary.macros where name=upcase("&startTimeMacroVariable"); 175 quit; 176 %rcSet(&sqlrc); 177 %if (&etls_startTimeMacroVarExisted = 0) %then %do; 178 %put WARNING: Start time from inner job not found. No value will be set.; 221 The SAS System 12:28 Tuesday, January 12, 2016 179 %let &startTimeMacroVariable=; 180 %end; 181 %end; 182 %local etls_endTimeMacroVarExisted; 183 %let etls_endTimeMacroVarExisted = 0; 184 %if ("&endTimeMacroVariable" ne "") %then %do; 185 proc sql noprint; 186 select '1' into: etls_endTimeMacroVarExisted from dictionary.macros where name=upcase("&endTimeMacroVariable"); 187 quit; 188 %rcSet(&sqlrc); 189 %if (&etls_endTimeMacroVarExisted = 0) %then %do; 190 %put WARNING: End time from inner job not found. Setting end time to current time.; 191 %let &endTimeMacroVariable=%sysfunc(datetime()); 192 %end; 193 %end; 194 data &statusTable; 195 modify &statusTable(where=(&handleVariable = &handleName)); 196 %if ("&startTimeMacroVariable" ne "") %then %do; 197 &startTimeVariable = input(symget("&startTimeMacroVariable"),32.); 198 ; 199 %end; 200 %if ("&endTimeVariable" ne "") %then %do; 201 %if ("&endTimeMacroVariable" ne "") %then %do; 202 &endTimeVariable = input(symget("&endTimeMacroVariable"),32.); 203 ; 204 %end; 205 %else &endTimeVariable = datetime(); 206 ; 207 %end; 208 %if ("&returnCodeVariable" ne "") %then &returnCodeVariable = input(symget("&returnCodeMacroVariable"),32.); 209 ; 210 %if ("&statusVariable" ne "") %then %do; 211 if (symget("etls_rcMacroVarExisted") eq "0") then &statusVariable = "Unknown Status"; 212 else &statusVariable = &statusSetting; 213 %end; 214 call symput('handle',&handleVariable); 215 replace; 216 stop; 217 run; 218 %rcSet(&syserr); 219 %if (&signoff eq 1) %then %etls_signoff(handleName=&handle); 220 %end; 221 %mend etls_freeHandle; 222 %macro etls_createHandle(statusTable=, statusVariable=, handleVariable=, handlePrefix=rmt, workloadMacroVariable=, row=, 222! machineVariable=, statusSetting="Running", startTimeVariable=startTime, signon=1, useGrid=1, log=, output=, gridRC=, cmacvar= 223 etls_signonStatus, additionalSignonOptions=, signonRetries= ); 224 %local remoteSessionId; 225 %let remoteSessionId = &handlePrefix.&row; 226 %let &cmacvar = 1; 227 %local etls_machineId; 228 %if (&signon eq 1) %then %etls_signon(handleName=&remoteSessionId, useGrid=&useGrid, machineIdMacroVariable=etls_machineId, 228! workloadMacroVariable=&workloadMacroVariable, log=&log, output=&output, cmacvar=&cmacvar, gridRC=&gridRC, 228! additionalSignonOptions= 229 &additionalSignonOptions,signonRetries=&signonRetries); 230 %else %let &cmacvar=0; 231 data &statusTable; 232 retain ptr &row; 233 modify &statusTable point = ptr; 222 The SAS System 12:28 Tuesday, January 12, 2016 234 &handleVariable = "&remoteSessionId"; 235 %if (&signon eq 1) %then &machineVariable = "&etls_machineId"; 236 ; 237 %if (&&&cmacvar ne 0) %then %do; 238 &statusVariable = "Failed Signon"; 239 %end; 240 %else &statusVariable = &statusSetting; 241 ; 242 %if (&startTimeVariable ne ) %then &startTimeVariable = datetime(); 243 ; 244 replace; 245 stop; 246 run; 247 %rcSet(&syserr); 248 %mend etls_createHandle; 249 %macro etls_waitfor(statusTable=, statusVariable=, runningStatusSetting="Running", handleVariable=, 249! completeStatusSetting="Finished", endTimeVariable=endTime, startTimeVariable=startTime, waitType=_ANY_, signoff=1, 249! returnCodeVariable=, 250 returnCodeMacroVariable=, statusUnknownReturnCode=.); 251 proc sql noprint; 252 select count(*) into :etls_rows from &statusTable where &statusVariable = &runningStatusSetting; 253 %let etls_rows = &etls_rows; 254 %if (&etls_rows gt 0) %then %do; 255 select &handleVariable into :etlsHandles1 - :etlsHandles&etls_rows from &statusTable where &statusVariable = 255! &runningStatusSetting; 256 %end; 257 quit; 258 %rcSet(&sqlrc); 259 %if (&etls_rows gt 0) %then %do; 260 waitfor &waitType %do i=1 %to &etls_rows; 261 &&etlsHandles&i %end; 262 ; 263 %end; 264 %local useDefaultRCMacVar; 265 %if ("&returnCodeMacroVariable" eq "") %then %let useDefaultRCMacVar=Y; 266 %do i=1 %to &etls_rows; 267 %if ("&useDefaultRCMacVar"="Y") %then %let returnCodeMacroVariable=job_rc&&etlsHandles&i; 268 waitfor &&etlsHandles&i timeout=1; 269 %if (&SYSRC eq 0) or (&SYSRC eq -2) %then %do; 270 %etls_freeHandle(statusTable=&statusTable, statusVariable=&statusVariable, handleVariable=&handleVariable, 270! handleName="&&etlsHandles&i", statusSetting=&completeStatusSetting, endTimeVariable=&endTimeVariable, 270! startTimeVariable=&startTimeVariable, 271 signoff=&signoff, returnCodeVariable=&returnCodeVariable, returnCodeMacroVariable=&returnCodeMacroVariable, 271! statusUnknownReturnCode=&statusUnknownReturnCode, startTimeMacroVariable=etls_startTime_&&etlsHandles&i., endTimeMacroVariable= 272 etls_endTime_&&etlsHandles&i. ); 273 %put NOTE: Process &&etlsHandles&i. has completed.; 274 %end; 275 %else %if (&SYSRC ne -1) %then %do; 276 %etls_freeHandle(statusTable=&statusTable, statusVariable=&statusVariable, handleVariable=&handleVariable, 276! handleName="&&etlsHandles&i", statusSetting="Unknown Status", endTimeVariable=&endTimeVariable, 276! startTimeVariable=&startTimeVariable, signoff= 277 &signoff, returnCodeVariable=&returnCodeVariable, returnCodeMacroVariable=&returnCodeMacroVariable, 277! statusUnknownReturnCode=&statusUnknownReturnCode, startTimeMacroVariable=etls_startTime_&&etlsHandles&i ); 278 %put NOTE: Cannot retrieve status from process &&etlsHandles&i..; 279 %end; 280 %end; 281 %mend etls_waitfor; 282 %macro etls_getProcessesRunning(statusTable=, statusVariable=, processCountMacro=, statusSetting="Running"); 223 The SAS System 12:28 Tuesday, January 12, 2016 283 proc sql noprint; 284 select count(*) into: &processCountMacro from &statusTable where &statusVariable = &statusSetting; 285 quit; 286 %rcSet(&sqlrc); 287 %mend etls_getProcessesRunning; 288 LIBNAME ODSBESA ORACLE PATH=DWCPD1 PATH=DWCPD1 SCHEMA=ODSBESA USER=odsbesa PASSWORD="{sas001}b2RzYmVzYSMyMDEw" ; 289 %rcSet(&syslibrc); 290 %let SYSLAST = %nrquote(ODSBESA.TCONTA); 291 %let transformID = %quote(A50D7W6R.AR0030B3); 292 %let trans_rc = 0; 293 proc datasets lib = work nolist nowarn memtype = (data view); 294 delete W6NK6WAN; 295 quit; 296 proc sql; 297 create table work.W6NK6WAN as select IDENT length = 4 label = 'IDENT', CDCONTRATO length = 16 label = 'CDCONTRATO', CDCLIENTE 297! length = 9 label = 'CDCLIENTE', CDMOEDA length = 3 label = 'CDMOEDA', (MTACTUALLC) as MTACTUALLC length = 8 format = 25.3 298 informat = 25.3 from &SYSLAST where DTCREATE = &dataparam. & CDCLIENTE ^= '00000000'; 299 quit; 300 %let SYSLAST = work.W6NK6WAN; 301 %rcSet(&sqlrc); 302 %let SYSLAST = %nrquote(ODSBESA.TTRANSACCOES); 303 %let transformID = %quote(A50D7W6R.AR0030B2); 304 %let trans_rc = 0; 305 proc datasets lib = work nolist nowarn memtype = (data view); 306 delete W6N4VID0; 307 quit; 308 proc sql; 309 create table work.W6N4VID0 as select distinct IDENT length = 4 label = 'IDENT', CDCLIENTE length = 9 label = 'CDCLIENTE' from 309! &SYSLAST where DTTRANSACCAO >= intnx('dtmonth',&dataparam, -6,'b') AND DTTRANSACCAO <= &dataParam. and INCONTCONTAB='A' and 310 CDPRODUTO <> 'DMNT'; 311 quit; 312 %let SYSLAST = work.W6N4VID0; 313 %rcSet(&sqlrc); 314 %let transformID = %quote(A50D7W6R.AR0030AY); 315 %let trans_rc = 0; 316 proc datasets lib = work nolist nowarn memtype = (data view); 317 delete W6NINDD1; 318 quit; 319 proc sql; 320 create table work.W6NINDD1 as select distinct TCONTRATO.CDCLIENTE length = 9 format = $9. informat = $9. label = 'CDCLIENTE', 320! TCLIENTE.LNCLIENTE length = 80 format = $80. informat = $80. label = 'LNCLIENTE', TCONTRATO.CDCONTRATO length = 16 format = 320! $16. 321 informat = $16. label = 'CDCONTRATO', TCONTRATO.CDPRODUTO length = 4 format = $4. informat = $4. label = 'CDPRODUTO', 321! TCONTRATO.CDBALCAO length = 3 format = $3. informat = $3. label = 'CDBALCAO', TCONTRATO.CDMOEDA length = 3 format = $3. 321! informat = $3. 322 label = 'CDMOEDA', W6NK6WAN.MTACTUALLC length = 8 format = 25.3 informat = 25.3 label = 'MTACTUALLC', case when 322! (W6NK6WAN.MTACTUALLC >= 2014) then '2000' else 'SALDO INSUF.' end as COMISSAO length = 15 format = $15. informat = $15. label = 322! 'COMISSAO', 323 case when TCLIENTE.CDBALCAO in ( '026' , '001' , '009') then 'Private' else 'Não Private' end as TP_CLIENTE length = 15 format 323! = $15. informat = $15. label = 'TP_CLIENTE', put(datepart(&dataparam),yymmdd10.) as DTPROC length = 10 format = $10. informat 323! = 324 $10. label = 'DTPROC', case when (TCLIENTE.CDBALCAO in ( '951' ) and W6NK6WAN.MTACTUALLC >= 2014 ) then '0'when --- 49 324! (W6NK6WAN.MTACTUALLC < 2014 ) then 'SALDO INSUF.' else '14' end as IMPOSTO length = 15 format = $15. informat = $15. label = 324! 'IMPOSTO', NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. 224 The SAS System 12:28 Tuesday, January 12, 2016 325 TCATEGORIACLI.LICATEGCLIENTE length = 80 format = $80. informat = $80. label = 'LICATEGCLIENTE', TCLIENTE.IDENT length = 4 325! format = $4. informat = $4. label = 'IDENT' from work.W6N4VID0 right join ODSBESA.TCONTRATO on ( W6N4VID0.IDENT = 325! TCONTRATO.IDENT 326 and W6N4VID0.CDCLIENTE = TCONTRATO.CDCLIENTE ) left join ODSBESA.TCLIENTE on ( &dataparam. BETWEEN TCLIENTE.DTCHANGE and 326! TCLIENTE.DTENDCHANGE and TCLIENTE.IDENT = TCONTRATO.IDENT and TCLIENTE.CDCLIENTE = TCONTRATO.CDCLIENTE and TCLIENTE.CDCLIENTE 326! <> 327 '00000000' ) left join work.W6NK6WAN on ( W6NK6WAN.CDCLIENTE = TCONTRATO.CDCLIENTE and W6NK6WAN.CDMOEDA = TCONTRATO.CDMOEDA 327! and W6NK6WAN.CDCONTRATO = TCONTRATO.CDCONTRATO ) left join ODSBESA.TCATEGORIACLI on ( TCLIENTE.CDCATEGCLIENTE = 328 TCATEGORIACLI.CDCATEGCLIENTE ) where &dataparam. BETWEEN TCONTRATO.DTCHANGE and TCONTRATO.DTENDCHANGE and TCONTRATO.CDCLIENTE 328! <> '00000000' and TCONTRATO.CDMODULO = 'CA' and TCONTRATO.CDPRODUTO IN ('PNMR' ,'CURR', 'TPRV' , 'TNMR' , 'PRIV', 'NUMR', 329 'SALR') and W6N4VID0.CDCLIENTE IS NULL ; 330 quit; 331 %rcSet(&sqlrc); 332 %macro etls_completeTarget(); 333 %mend etls_completeTarget; 334 %etls_completeTarget; 335 %let SYSLAST = %nrquote(work.W6NINDD1); 336 %let transformID = %quote(A50D7W6R.AR0030B5); 337 %let trans_rc = 0; 338 proc datasets lib = work nolist nowarn memtype = (data view); 339 delete W6NKOVYO; 340 quit; 341 proc sql; 342 create table work.W6NKOVYO as select CDCLIENTE length = 9 label = 'CDCLIENTE', LNCLIENTE length = 80 label = 'LNCLIENTE', 342! CDCONTRATO length = 16 label = 'CDCONTRATO', CDPRODUTO length = 4 label = 'CDPRODUTO', CDBALCAO length = 3 label = 'CDBALCAO', 343 CDMOEDA length = 3 label = 'CDMOEDA', MTACTUALLC length = 8 label = 'MTACTUALLC', COMISSAO length = 15 label = 'COMISSAO', 343! TP_CLIENTE length = 15 label = 'TP_CLIENTE', DTPROC length = 10 label = 'DTPROC', IMPOSTO length = 15 label = 'IMPOSTO', 344 LICATEGCLIENTE length = 80 label = 'LICATEGCLIENTE', IDENT length = 4 label = 'IDENT', (sum(MTACTUALLC)) as SUM_MTACTUALLC 344! length = 8 from &SYSLAST group by CDCLIENTE; 345 quit; 346 %let SYSLAST = work.W6NKOVYO; 347 %rcSet(&sqlrc); 348 %let SYSLAST = %nrquote(work.W6NKOVYO); 349 %let transformID = %quote(A50D7W6R.AR0030B6); 350 %let trans_rc = 0; 351 proc datasets lib = work nolist nowarn memtype = (data view); 352 delete W6NKOYEV; 353 quit; 354 proc sql; 355 create table work.W6NKOYEV as select CDCLIENTE length = 9 label = 'CDCLIENTE', LNCLIENTE length = 80 label = 'LNCLIENTE', 355! CDCONTRATO length = 16 label = 'CDCONTRATO', CDPRODUTO length = 4 label = 'CDPRODUTO', CDBALCAO length = 3 label = 'CDBALCAO', 356 CDMOEDA length = 3 label = 'CDMOEDA', MTACTUALLC length = 8 label = 'MTACTUALLC', COMISSAO length = 15 label = 'COMISSAO', 356! TP_CLIENTE length = 15 label = 'TP_CLIENTE', DTPROC length = 10 label = 'DTPROC', IMPOSTO length = 15 label = 'IMPOSTO', 357 LICATEGCLIENTE length = 80 label = 'LICATEGCLIENTE', IDENT length = 4 label = 'IDENT' from &SYSLAST where SUM_MTACTUALLC < 357! 20000; 358 quit; 359 %let SYSLAST = work.W6NKOYEV; 360 %rcSet(&sqlrc); 361 %let SYSLAST = %nrquote(work.W6NKOYEV); 362 %let transformID = %quote(A50D7W6R.AR0030B7); 363 %let trans_rc = 0; 364 %let _INPUT1 = work.W6NKOYEV; 365 %let _OUTPUT = work.W6NKP0CJ; 366 %let _OUTPUT1 = work.W6NKP0CJ; 367 proc datasets lib=work nolist nowarn memtype = (data view); 368 delete W6NKP0CJ; 369 quit; 370 %let data_col = %nrquote(DTPROC); 225 The SAS System 12:28 Tuesday, January 12, 2016 371 %let rel_table = %nrquote(GLBBESA.S_ODS_DW148); 372 %let data_apagar = %nrquote(&dataparam); 373 %let keep = CDCLIENTE LNCLIENTE CDCONTRATO CDPRODUTO CDBALCAO CDMOEDA MTACTUALLC COMISSAO TP_CLIENTE DTPROC IMPOSTO 373! LICATEGCLIENTE IDENT; 374 %macro connect_oracle(schema); 375 connect to ORACLE ( %IF %UPCASE(&schema)= CTBBESA %THEN %DO; 376 PATH = &ORACLE_CTB_SID USER = &ORACLE_CTB_USER PASSWORD = &ORACLE_CTB_PASSWORD %END; 377 %ELSE %IF %UPCASE(&schema)= GLBBESA %THEN %DO; 378 PATH = &ORACLE_DM_SID USER = &ORACLE_DM_USER PASSWORD = &ORACLE_DM_PASSWORD %END; 379 ) %mend connect_oracle; 380 %macro tab_oracle(tabela); 381 %let schema=%scan(&tabela,1); 382 %IF %UPCASE(&schema) = GLBBESA %THEN %DO; 383 %sysfunc(cats(DMBESA,.,%scan(&tabela,2))) %END; 384 %ELSE %DO; 385 &tabela %END; 386 %mend tab_oracle; 387 data _null_; 388 format a Datetime.; 389 a = &data_apagar; 390 call symput("data_apagarN",put(datepart(a),YYMMDD10.)); 391 run; 392 proc sql; 393 %connect_oracle(%scan(&rel_table,1)); 394 execute( delete from %tab_oracle(&rel_table) where &data_col = %bquote(')&data_apagarN%bquote(')) by oracle; 395 disconnect from ORACLE; 396 quit; 397 proc sql; 398 create view &_OUTPUT1 as select * from &_INPUT1; 399 quit; 400 %rcSet(&syserr); 401 %rcSet(&sysrc); 402 %rcSet(&sqlrc); 403 LIBNAME GLBBESA ORACLE PATH=DWCPD1 PATH=DWCPD1 SCHEMA=DMBESA USER=dmbesa PASSWORD="{sas001}ZG1iZXNhIzIwMTA=" ; 404 %rcSet(&syslibrc); 405 %let transformID = %quote(A50D7W6R.AR0030G3); 406 %let trans_rc = 0; 407 proc datasets lib = work nolist nowarn memtype = (data view); 408 delete W6O48460; 409 quit; 410 proc sql; 411 create table work.W6O48460 as select W6NKP0CJ.CDCLIENTE length = 9 format = $9. informat = $9. label = 'CDCLIENTE', 411! W6NKP0CJ.LNCLIENTE length = 80 format = $80. informat = $80. label = 'LNCLIENTE', W6NKP0CJ.CDCONTRATO length = 16 format = $16. 411! informat = 412 $16. label = 'CDCONTRATO', W6NKP0CJ.CDPRODUTO length = 4 format = $4. informat = $4. label = 'CDPRODUTO', W6NKP0CJ.CDBALCAO 412! length = 3 format = $3. informat = $3. label = 'CDBALCAO', W6NKP0CJ.CDMOEDA length = 3 format = $3. informat = $3. label = 413 'CDMOEDA', W6NKP0CJ.MTACTUALLC length = 8 format = 25.3 informat = 25.3 label = 'MTACTUALLC', W6NKP0CJ.COMISSAO length = 15 413! format = $15. informat = $15. label = 'COMISSAO', W6NKP0CJ.TP_CLIENTE length = 15 format = $15. informat = $15. label = 414 'TP_CLIENTE', W6NKP0CJ.DTPROC length = 10 format = $10. informat = $10. label = 'DTPROC', W6NKP0CJ.IMPOSTO length = 15 format 414! = $15. informat = $15. label = 'IMPOSTO', W6NKP0CJ.LICATEGCLIENTE length = 80 format = $80. informat = $80. label = 415 'LICATEGCLIENTE', W6NKP0CJ.IDENT length = 4 format = $4. informat = $4. label = 'IDENT' from work.W6NKP0CJ left join 415! GLBBESA.S_ODS_DW148 on ( W6NKP0CJ.CDCLIENTE = S_ODS_DW148.CDCLIENTE and W6NKP0CJ.CDCONTRATO = S_ODS_DW148.CDCONTRATOREL ) where 416 S_ODS_DW148.CDCLIENTE IS NULL ; 417 quit; 418 %rcSet(&sqlrc); 419 %macro etls_completeTarget(); 420 %mend etls_completeTarget; 421 %etls_completeTarget; 226 The SAS System 12:28 Tuesday, January 12, 2016 422 %let SYSLAST = %nrquote(work.W6O48460); 423 %let transformID = %quote(A50D7W6R.AR0030AW); 424 %let trans_rc = 0; 425 %global etls_tableExist; 426 %global etls_numIndex; 427 %global etls_lastTable; 428 %let etls_tableExist = -1; 429 %let etls_numIndex = -1; 430 %let etls_lastTable = &SYSLAST; 431 %macro etls_loader; 432 %let SYSOPT = ; 433 proc datasets lib = work nolist nowarn memtype = (data view); 434 delete mapped; 435 quit; 436 %put NOTE: Mapping columns ...; 437 proc sql; 438 create view work.mapped as select IDENT length = 4, CDCLIENTE length = 9, LNCLIENTE length = 80, CDCONTRATO as CDCONTRATOREL 438! length = 16, CDPRODUTO length = 4, CDBALCAO length = 3, CDMOEDA length = 3, MTACTUALLC as SALDO_AOA length = 8 format = 20.3 439 informat = 20.3, COMISSAO length = 15, IMPOSTO length = 15, TP_CLIENTE as TPCLIENTE length = 15, LICATEGCLIENTE length = 80, 439! DTPROC length = 10 from &etls_lastTable; 440 quit; 441 %let SYSLAST = work.mapped; 442 %let etls_lastTable = &SYSLAST; 443 %let SYSOPT = ; 444 %let etls_tableExist = %eval(%sysfunc(exist(GLBBESA.S_ODS_DW148, DATA)) or %sysfunc(exist(GLBBESA.S_ODS_DW148, VIEW))); 445 %if (&etls_tableExist eq 0) %then %do; 446 %put NOTE: Creating table ...; 447 data GLBBESA.S_ODS_DW148 (dbnull = ( IDENT = YES CDCLIENTE = YES LNCLIENTE = YES CDCONTRATOREL = YES CDPRODUTO = YES CDBALCAO 447! = YES CDMOEDA = YES SALDO_AOA = YES COMISSAO = YES IMPOSTO = YES TPCLIENTE = YES LICATEGCLIENTE = YES DTPROC = YES)); 448 attrib IDENT length = $4 format = $4. informat = $4. label = 'IDENT'; 449 attrib CDCLIENTE length = $9 format = $9. informat = $9. label = 'CDCLIENTE'; 450 attrib LNCLIENTE length = $80 format = $80. informat = $80. label = 'LNCLIENTE'; 451 attrib CDCONTRATOREL length = $16 format = $16. informat = $16. label = 'CDCONTRATOREL'; 452 attrib CDPRODUTO length = $4 format = $4. informat = $4. label = 'CDPRODUTO'; 453 attrib CDBALCAO length = $3 format = $3. informat = $3. label = 'CDBALCAO'; 454 attrib CDMOEDA length = $3 format = $3. informat = $3. label = 'CDMOEDA'; 455 attrib SALDO_AOA length = 8 format = 20.3 informat = 20.3 label = 'SALDO_AOA'; 456 attrib COMISSAO length = $15 format = $15. informat = $15. label = 'COMISSAO'; 457 attrib IMPOSTO length = $15 format = $15. informat = $15. label = 'IMPOSTO'; 458 attrib TPCLIENTE length = $15 format = $15. informat = $15. label = 'TPCLIENTE'; 459 attrib LICATEGCLIENTE length = $80 format = $80. informat = $80. label = 'LICATEGCLIENTE'; 460 attrib DTPROC length = $10 format = $10. informat = $10. label = 'DTPROC'; 461 stop; 462 run; 463 %rcSet(&syserr); 464 %end; 465 %put NOTE: Appending data ...; 466 proc append base = GLBBESA.S_ODS_DW148 data = &etls_lastTable (&SYSOPT) force; 467 run; 468 %rcSet(&syserr); 469 proc datasets lib = work nolist nowarn memtype = (data view); 470 delete mapped; 471 quit; 472 %mend etls_loader; 473 %etls_loader; 474 %sysrput job_rc&handleName = &job_rc; 475 %sysrput etls_endTime_&handleName = %sysfunc(datetime()); 476 %mend etls_jobW6O7ZESS; 227 The SAS System 12:28 Tuesday, January 12, 2016 477 %etls_jobW6O7ZESS; SYMBOLGEN: Macro variable HANDLENAME resolves to L18_1 SYMBOLGEN: Macro variable METAPORT resolves to 8561 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable METASERVER resolves to s-bametasasd1 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable METAREPOSITORY resolves to me_besa SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable ETLS_DEBUG resolves to MPRINT(ETLS_JOBW6O7ZESS): ; SYMBOLGEN: Macro variable SYSSCP resolves to HP IPF SYMBOLGEN: Macro variable SYSSCP resolves to HP IPF SYMBOLGEN: Macro variable NOOBJSERVER resolves to -noobjectserver SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MPRINT(GENSASCOMMAND): options sascmd = "!sascmd -noobjectserver"; MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): LIBNAME ODSBESA ORACLE PATH=DWCPD1 PATH=DWCPD1 SCHEMA=ODSBESA USER=odsbesa PASSWORD="{sas001}b2RzYmVzYSMyMDEw" ; NOTE: Libref ODSBESA was successfully assigned as follows: Engine: ORACLE Physical Name: DWCPD1 SYMBOLGEN: Macro variable SYSLIBRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_JOBW6O7ZESS): delete W6NK6WAN; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds Memory 45k Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): proc sql; SYMBOLGEN: Macro variable SYSLAST resolves to ODSBESA.TCONTA SYMBOLGEN: Macro variable DATAPARAM resolves to 1764460800 MPRINT(ETLS_JOBW6O7ZESS): create table work.W6NK6WAN as select IDENT length = 4 label = 'IDENT', CDCONTRATO length = 16 label = 'CDCONTRATO', CDCLIENTE length = 9 label = 'CDCLIENTE', CDMOEDA length = 3 label = 'CDMOEDA', (MTACTUALLC) as MTACTUALLC length = 8 format = 25.3 informat = 25.3 from ODSBESA.TCONTA where DTCREATE = 1764460800 & CDCLIENTE ^= '00000000'; NOTE: Compressing data set WORK.W6NK6WAN increased size by 46.37 percent. Compressed is 1411 pages; un-compressed would require 964 pages. NOTE: Table WORK.W6NK6WAN created, with 195563 rows and 5 columns. MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.03 seconds 228 The SAS System 12:28 Tuesday, January 12, 2016 user cpu time 1.29 seconds system cpu time 0.13 seconds Memory 405k Page Faults 0 Page Reclaims 5 Page Swaps 0 Voluntary Context Switches 878 Involuntary Context Switches 51 Block Input Operations 0 Block Output Operations 0 SYMBOLGEN: Macro variable SQLRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_JOBW6O7ZESS): delete W6N4VID0; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 45k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): proc sql; SYMBOLGEN: Macro variable SYSLAST resolves to ODSBESA.TTRANSACCOES SYMBOLGEN: Macro variable DATAPARAM resolves to 1764460800 SYMBOLGEN: Macro variable DATAPARAM resolves to 1764460800 MPRINT(ETLS_JOBW6O7ZESS): create table work.W6N4VID0 as select distinct IDENT length = 4 label = 'IDENT', CDCLIENTE length = 9 label = 'CDCLIENTE' from ODSBESA.TTRANSACCOES where DTTRANSACCAO >= intnx('dtmonth',1764460800, -6,'b') AND DTTRANSACCAO <= 1764460800 and INCONTCONTAB='A' and CDPRODUTO <> 'DMNT'; NOTE: The "<>" operator is interpreted as "not equals". NOTE: Compression was disabled for data set WORK.W6N4VID0 because compression overhead would increase the size of the data set. NOTE: Table WORK.W6N4VID0 created, with 3503 rows and 2 columns. MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.20 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds Memory 339k Page Faults 0 Page Reclaims 8 Page Swaps 0 Voluntary Context Switches 25 Involuntary Context Switches 0 229 The SAS System 12:28 Tuesday, January 12, 2016 Block Input Operations 0 Block Output Operations 2 SYMBOLGEN: Macro variable SQLRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_JOBW6O7ZESS): delete W6NINDD1; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 45k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): proc sql; SYMBOLGEN: Macro variable DATAPARAM resolves to 1764460800 NOTE: Line generated by the invoked macro "ETLS_JOBW6O7ZESS". 477 $15. label = 'TP_CLIENTE', put(datepart(&dataparam),yymmdd10.) as DTPROC length = 10 format = $10. informat = $10. label = 477! 'DTPROC', case when (TCLIENTE.CDBALCAO in ( '951' ) and W6NK6WAN.MTACTUALLC >= 2014 ) then '0'when (W6NK6WAN.MTACTUALLC < 2014 --- 49 477! ) NOTE 49-169: The meaning of an identifier after a quoted string may change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended. SYMBOLGEN: Macro variable DATAPARAM resolves to 1764460800 SYMBOLGEN: Macro variable DATAPARAM resolves to 1764460800 MPRINT(ETLS_JOBW6O7ZESS): create table work.W6NINDD1 as select distinct TCONTRATO.CDCLIENTE length = 9 format = $9. informat = $9. label = 'CDCLIENTE', TCLIENTE.LNCLIENTE length = 80 format = $80. informat = $80. label = 'LNCLIENTE', TCONTRATO.CDCONTRATO length = 16 format = $16. informat = $16. label = 'CDCONTRATO', TCONTRATO.CDPRODUTO length = 4 format = $4. informat = $4. label = 'CDPRODUTO', TCONTRATO.CDBALCAO length = 3 format = $3. informat = $3. label = 'CDBALCAO', TCONTRATO.CDMOEDA length = 3 format = $3. informat = $3. label = 'CDMOEDA', W6NK6WAN.MTACTUALLC length = 8 format = 25.3 informat = 25.3 label = 'MTACTUALLC', case when (W6NK6WAN.MTACTUALLC >= 2014) then '2000' else 'SALDO INSUF.' end as COMISSAO length = 15 format = $15. informat = $15. label = 'COMISSAO', case when TCLIENTE.CDBALCAO in ( '026' , '001' , '009') then 'Private' else 'Não Private' end as TP_CLIENTE length = 15 format = $15. informat = $15. label = 'TP_CLIENTE', put(datepart(1764460800),yymmdd10.) as DTPROC length = 10 format = $10. informat = $10. label = 'DTPROC', case when (TCLIENTE.CDBALCAO in ( '951' ) and W6NK6WAN.MTACTUALLC >= 2014 ) then '0'when (W6NK6WAN.MTACTUALLC < 2014 ) then 'SALDO INSUF.' else '14' end as IMPOSTO length = 15 format = $15. informat = $15. label = 'IMPOSTO', TCATEGORIACLI.LICATEGCLIENTE length = 80 format = $80. informat = $80. label = 'LICATEGCLIENTE', TCLIENTE.IDENT length = 4 format = $4. informat = $4. label = 'IDENT' from work.W6N4VID0 right join ODSBESA.TCONTRATO on ( W6N4VID0.IDENT = TCONTRATO.IDENT and W6N4VID0.CDCLIENTE = TCONTRATO.CDCLIENTE ) left join ODSBESA.TCLIENTE on ( 1764460800 BETWEEN TCLIENTE.DTCHANGE and TCLIENTE.DTENDCHANGE and TCLIENTE.IDENT = TCONTRATO.IDENT and TCLIENTE.CDCLIENTE = TCONTRATO.CDCLIENTE and TCLIENTE.CDCLIENTE <> '00000000' ) left join work.W6NK6WAN on ( W6NK6WAN.CDCLIENTE = TCONTRATO.CDCLIENTE and W6NK6WAN.CDMOEDA = TCONTRATO.CDMOEDA and W6NK6WAN.CDCONTRATO = TCONTRATO.CDCONTRATO ) left join ODSBESA.TCATEGORIACLI on ( TCLIENTE.CDCATEGCLIENTE = TCATEGORIACLI.CDCATEGCLIENTE ) where 1764460800 BETWEEN TCONTRATO.DTCHANGE and TCONTRATO.DTENDCHANGE and TCONTRATO.CDCLIENTE <> 230 The SAS System 12:28 Tuesday, January 12, 2016 '00000000' and TCONTRATO.CDMODULO = 'CA' and TCONTRATO.CDPRODUTO IN ('PNMR' ,'CURR', 'TPRV' , 'TNMR' , 'PRIV', 'NUMR', 'SALR') and W6N4VID0.CDCLIENTE IS NULL ; NOTE: The "<>" operator is interpreted as "not equals". NOTE: SAS threaded sort was used. NOTE: Compressing data set WORK.W6NINDD1 decreased size by 38.72 percent. Compressed is 1833 pages; un-compressed would require 2991 pages. NOTE: Table WORK.W6NINDD1 created, with 182421 rows and 13 columns. MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 26.06 seconds user cpu time 14.33 seconds system cpu time 3.11 seconds Memory 2429k Page Faults 0 Page Reclaims 708 Page Swaps 0 Voluntary Context Switches 11492 Involuntary Context Switches 484 Block Input Operations 0 Block Output Operations 3 SYMBOLGEN: Macro variable SQLRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_JOBW6O7ZESS): delete W6NKOVYO; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 45k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): proc sql; SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W6NINDD1 MPRINT(ETLS_JOBW6O7ZESS): create table work.W6NKOVYO as select CDCLIENTE length = 9 label = 'CDCLIENTE', LNCLIENTE length = 80 label = 'LNCLIENTE', CDCONTRATO length = 16 label = 'CDCONTRATO', CDPRODUTO length = 4 label = 'CDPRODUTO', CDBALCAO length = 3 label = 'CDBALCAO', CDMOEDA length = 3 label = 'CDMOEDA', MTACTUALLC length = 8 label = 'MTACTUALLC', COMISSAO length = 15 label = 'COMISSAO', TP_CLIENTE length = 15 label = 'TP_CLIENTE', DTPROC length = 10 label = 'DTPROC', IMPOSTO length = 15 label = 'IMPOSTO', LICATEGCLIENTE length = 80 label = 'LICATEGCLIENTE', IDENT length = 4 label = 'IDENT', (sum(MTACTUALLC)) as SUM_MTACTUALLC length = 8 from WORK.W6NINDD1 group by CDCLIENTE; NOTE: The query requires remerging summary statistics back with the original data. NOTE: Compressing data set WORK.W6NKOVYO decreased size by 37.22 percent. 231 The SAS System 12:28 Tuesday, January 12, 2016 Compressed is 1909 pages; un-compressed would require 3041 pages. NOTE: Table WORK.W6NKOVYO created, with 182421 rows and 14 columns. MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.26 seconds user cpu time 0.98 seconds system cpu time 0.19 seconds Memory 413k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 27 Involuntary Context Switches 16 Block Input Operations 0 Block Output Operations 0 SYMBOLGEN: Macro variable SQLRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_JOBW6O7ZESS): delete W6NKOYEV; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 45k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): proc sql; SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W6NKOVYO MPRINT(ETLS_JOBW6O7ZESS): create table work.W6NKOYEV as select CDCLIENTE length = 9 label = 'CDCLIENTE', LNCLIENTE length = 80 label = 'LNCLIENTE', CDCONTRATO length = 16 label = 'CDCONTRATO', CDPRODUTO length = 4 label = 'CDPRODUTO', CDBALCAO length = 3 label = 'CDBALCAO', CDMOEDA length = 3 label = 'CDMOEDA', MTACTUALLC length = 8 label = 'MTACTUALLC', COMISSAO length = 15 label = 'COMISSAO', TP_CLIENTE length = 15 label = 'TP_CLIENTE', DTPROC length = 10 label = 'DTPROC', IMPOSTO length = 15 label = 'IMPOSTO', LICATEGCLIENTE length = 80 label = 'LICATEGCLIENTE', IDENT length = 4 label = 'IDENT' from WORK.W6NKOVYO where SUM_MTACTUALLC < 20000; NOTE: Compressing data set WORK.W6NKOYEV decreased size by 38.03 percent. Compressed is 1398 pages; un-compressed would require 2256 pages. NOTE: Table WORK.W6NKOYEV created, with 137588 rows and 13 columns. MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.82 seconds user cpu time 0.60 seconds 232 The SAS System 12:28 Tuesday, January 12, 2016 system cpu time 0.15 seconds Memory 321k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 18 Involuntary Context Switches 13 Block Input Operations 0 Block Output Operations 0 SYMBOLGEN: Macro variable SQLRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): proc datasets lib=work nolist nowarn memtype = (data view); MPRINT(ETLS_JOBW6O7ZESS): delete W6NKP0CJ; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 45k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 SYMBOLGEN: Macro variable DATAPARAM resolves to 1764460800 MPRINT(ETLS_JOBW6O7ZESS): data _null_; MPRINT(ETLS_JOBW6O7ZESS): format a Datetime.; SYMBOLGEN: Macro variable DATA_APAGAR resolves to 1764460800 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MPRINT(ETLS_JOBW6O7ZESS): a = 1764460800; MPRINT(ETLS_JOBW6O7ZESS): call symput("data_apagarN",put(datepart(a),YYMMDD10.)); MPRINT(ETLS_JOBW6O7ZESS): run; NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 249k Page Faults 0 Page Reclaims 15 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 233 The SAS System 12:28 Tuesday, January 12, 2016 MPRINT(ETLS_JOBW6O7ZESS): proc sql; SYMBOLGEN: Macro variable REL_TABLE resolves to GLBBESA.S_ODS_DW148 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable SCHEMA resolves to GLBBESA SYMBOLGEN: Macro variable SCHEMA resolves to GLBBESA SYMBOLGEN: Macro variable ORACLE_DM_SID resolves to DWCPD1 SYMBOLGEN: Macro variable ORACLE_DM_USER resolves to DMBESA SYMBOLGEN: Macro variable ORACLE_DM_PASSWORD resolves to "dmbesa#2010" MPRINT(CONNECT_ORACLE): connect to ORACLE ( PATH = DWCPD1 USER = DMBESA PASSWORD = "dmbesa#2010" ) MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): execute( delete from SYMBOLGEN: Macro variable REL_TABLE resolves to GLBBESA.S_ODS_DW148 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable TABELA resolves to GLBBESA.S_ODS_DW148 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable SCHEMA resolves to GLBBESA SYMBOLGEN: Macro variable TABELA resolves to GLBBESA.S_ODS_DW148 SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MPRINT(TAB_ORACLE): DMBESA.S_ODS_DW148 SYMBOLGEN: Macro variable DATA_COL resolves to DTPROC SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. SYMBOLGEN: Macro variable DATA_APAGARN resolves to 2015-11-30 MPRINT(ETLS_JOBW6O7ZESS): where DTPROC = '2015-11-30') by oracle; MPRINT(ETLS_JOBW6O7ZESS): disconnect from ORACLE; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.40 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds Memory 67k Page Faults 0 Page Reclaims 6 Page Swaps 0 Voluntary Context Switches 61 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): proc sql; SYMBOLGEN: Macro variable _OUTPUT1 resolves to work.W6NKP0CJ SYMBOLGEN: Macro variable _INPUT1 resolves to work.W6NKOYEV MPRINT(ETLS_JOBW6O7ZESS): create view work.W6NKP0CJ as select * from work.W6NKOYEV; NOTE: SQL view WORK.W6NKP0CJ has been defined. MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds Memory 35k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 2 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 1 234 The SAS System 12:28 Tuesday, January 12, 2016 SYMBOLGEN: Macro variable SYSERR resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; SYMBOLGEN: Macro variable SYSRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; SYMBOLGEN: Macro variable SQLRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): LIBNAME GLBBESA ORACLE PATH=DWCPD1 PATH=DWCPD1 SCHEMA=DMBESA USER=dmbesa PASSWORD="{sas001}ZG1iZXNhIzIwMTA=" ; NOTE: Libref GLBBESA was successfully assigned as follows: Engine: ORACLE Physical Name: DWCPD1 SYMBOLGEN: Macro variable SYSLIBRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_JOBW6O7ZESS): delete W6O48460; MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 45k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): proc sql; MPRINT(ETLS_JOBW6O7ZESS): create table work.W6O48460 as select W6NKP0CJ.CDCLIENTE length = 9 format = $9. informat = $9. label = 'CDCLIENTE', W6NKP0CJ.LNCLIENTE length = 80 format = $80. informat = $80. label = 'LNCLIENTE', W6NKP0CJ.CDCONTRATO length = 16 format = $16. informat = $16. label = 'CDCONTRATO', W6NKP0CJ.CDPRODUTO length = 4 format = $4. informat = $4. label = 'CDPRODUTO', W6NKP0CJ.CDBALCAO length = 3 format = $3. informat = $3. label = 'CDBALCAO', W6NKP0CJ.CDMOEDA length = 3 format = $3. informat = $3. label = 'CDMOEDA', W6NKP0CJ.MTACTUALLC length = 8 format = 25.3 informat = 25.3 label = 'MTACTUALLC', W6NKP0CJ.COMISSAO length = 15 format = $15. informat = $15. label = 'COMISSAO', W6NKP0CJ.TP_CLIENTE length = 15 format = $15. informat = $15. label = 'TP_CLIENTE', W6NKP0CJ.DTPROC length = 10 format = $10. informat = $10. label = 'DTPROC', W6NKP0CJ.IMPOSTO length = 15 format = $15. informat = $15. label = 'IMPOSTO', W6NKP0CJ.LICATEGCLIENTE length = 80 format = $80. informat = $80. label = 'LICATEGCLIENTE', W6NKP0CJ.IDENT length = 4 format = $4. informat = $4. label = 'IDENT' from work.W6NKP0CJ left join GLBBESA.S_ODS_DW148 on ( 235 The SAS System 12:28 Tuesday, January 12, 2016 W6NKP0CJ.CDCLIENTE = S_ODS_DW148.CDCLIENTE and W6NKP0CJ.CDCONTRATO = S_ODS_DW148.CDCONTRATOREL ) where S_ODS_DW148.CDCLIENTE IS NULL ; NOTE: SAS threaded sort was used. NOTE: Compressing data set WORK.W6O48460 decreased size by 38.03 percent. Compressed is 1398 pages; un-compressed would require 2256 pages. NOTE: Table WORK.W6O48460 created, with 137588 rows and 13 columns. MPRINT(ETLS_JOBW6O7ZESS): quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.99 seconds user cpu time 0.98 seconds system cpu time 0.62 seconds Memory 861k Page Faults 0 Page Reclaims 196 Page Swaps 0 Voluntary Context Switches 638 Involuntary Context Switches 35 Block Input Operations 0 Block Output Operations 581 SYMBOLGEN: Macro variable SQLRC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_JOBW6O7ZESS): ; MPRINT(ETLS_JOBW6O7ZESS): ; SYMBOLGEN: Macro variable SYSLAST resolves to WORK.W6O48460 MPRINT(ETLS_LOADER): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_LOADER): delete mapped; MPRINT(ETLS_LOADER): quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds Memory 45k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 NOTE: Mapping columns ... MPRINT(ETLS_LOADER): proc sql; SYMBOLGEN: Macro variable ETLS_LASTTABLE resolves to WORK.W6O48460 MPRINT(ETLS_LOADER): create view work.mapped as select IDENT length = 4, CDCLIENTE length = 9, LNCLIENTE length = 80, CDCONTRATO as CDCONTRATOREL length = 16, CDPRODUTO length = 4, CDBALCAO length = 3, CDMOEDA length = 3, MTACTUALLC as SALDO_AOA length = 8 format = 20.3 informat = 20.3, COMISSAO length = 15, IMPOSTO length = 15, TP_CLIENTE as TPCLIENTE length = 15, LICATEGCLIENTE length = 80, DTPROC length = 10 from WORK.W6O48460; NOTE: SQL view WORK.MAPPED has been defined. MPRINT(ETLS_LOADER): quit; NOTE: PROCEDURE SQL used (Total process time): 236 The SAS System 12:28 Tuesday, January 12, 2016 real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 59k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 2 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 2 SYMBOLGEN: Macro variable SYSLAST resolves to WORK.mapped SYMBOLGEN: Macro variable ETLS_TABLEEXIST resolves to 1 NOTE: Appending data ... SYMBOLGEN: Macro variable ETLS_LASTTABLE resolves to WORK.mapped SYMBOLGEN: Macro variable SYSOPT resolves to MPRINT(ETLS_LOADER): proc append base = GLBBESA.S_ODS_DW148 data = WORK.mapped () force; MPRINT(ETLS_LOADER): run; NOTE: Appending WORK.MAPPED to GLBBESA.S_ODS_DW148. NOTE: There were 137588 observations read from the data set WORK.MAPPED. NOTE: 137588 observations added. NOTE: The data set GLBBESA.S_ODS_DW148 has . observations and 13 variables. NOTE: PROCEDURE APPEND used (Total process time): real time 8.99 seconds user cpu time 1.87 seconds system cpu time 0.81 seconds Memory 507k Page Faults 0 Page Reclaims 14 Page Swaps 0 Voluntary Context Switches 13936 Involuntary Context Switches 51 Block Input Operations 0 Block Output Operations 0 SYMBOLGEN: Macro variable SYSERR resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable TRANS_RC resolves to 0 SYMBOLGEN: Macro variable ERROR resolves to 0 SYMBOLGEN: Macro variable JOB_RC resolves to 0 MPRINT(ETLS_LOADER): ; MPRINT(ETLS_LOADER): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_LOADER): delete mapped; MPRINT(ETLS_LOADER): quit; NOTE: Deleting WORK.MAPPED (memtype=VIEW). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds Memory 47k Page Faults 0 Page Reclaims 0 Page Swaps 0 Voluntary Context Switches 0 237 The SAS System 12:28 Tuesday, January 12, 2016 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 MPRINT(ETLS_JOBW6O7ZESS): ; SYMBOLGEN: Macro variable HANDLENAME resolves to L18_1 SYMBOLGEN: Macro variable JOB_RC resolves to 0 SYMBOLGEN: Macro variable HANDLENAME resolves to L18_1 NOTE: Remote submit to L18_1 complete.