1 The SAS System 16:00 Wednesday, August 24, 2016 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Base table'; 4 %LET _CLIENTPROJECTPATH='Z:\SAS Data\user\mzhang\Allowance\From Jeff''s folder\Allowance\Title ALLL Feb25, 2016.egp'; 5 %LET _CLIENTPROJECTNAME='Title ALLL Feb25, 2016.egp'; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; NOTE: ODS PDF(EGPDF) printed no output. (This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.) 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGPDF TEMP; 12 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=printer SAS; WARNING: Unsupported device 'ACTIVEX' for PDF(EGPDF) destination. Using device 'ACTXIMG'. NOTE: Writing ODS PDF(EGPDF) output to DISK destination "EGPDF", printer "PDF". 13 FILENAME EGSR TEMP; 14 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 15 STYLE=HtmlBlue 16 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome_9.4/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 GOPTIONS ACCESSIBLE; 25 OPTIONS FULLSTIMER SOURCE SOURCE2 MSGLEVEL=I MPRINT NOTES; 26 PROC OPTIONS GROUP=MEMORY; SAS (r) Proprietary Software Release 9.4 TS1M2 Group=MEMORY SORTSIZE=1073741824 Specifies the amount of memory that is available to the SORT procedure. SUMSIZE=0 Specifies a limit on the amount of memory that is available for data summarization procedures when class variables are active. MAXMEMQUERY=0 Specifies the maximum amount of memory that is allocated for procedures. MEMBLKSZ=16777216 Specifies the memory block size for Windows memory-based libraries. MEMMAXSZ=2147483648 Specifies the maximum amount of memory to allocate for using memory-based libraries. LOADMEMSIZE=0 Specifies a suggested amount of memory that is needed for executable programs loaded by SAS. MEMSIZE=73567756800 Specifies the limit on the amount of virtual memory that can be used during a SAS session. REALMEMSIZE=0 Specifies the amount of real memory SAS can expect to allocate. NOTE: PROCEDURE OPTIONS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 19.09k OS Memory 25440.00k Timestamp 08/24/2016 04:16:06 PM Step Count 27 Switch Count 52 27 PROC OPTIONS GROUP=PERFORMANCE; 2 The SAS System 16:00 Wednesday, August 24, 2016 28 RUN; SAS (r) Proprietary Software Release 9.4 TS1M2 Group=PERFORMANCE ARMAGENT= Specifies an ARM agent (which is an executable module or keyword, such as LOG4SAS) that contains a specific implementation of the ARM API. ARMLOC=ARMLOG.LOG Specifies the location of the ARM log. ARMSUBSYS=(ARM_NONE) Specifies the SAS ARM subsystems to enable or disable. BUFNO=1 Specifies the number of buffers for processing SAS data sets. BUFSIZE=0 Specifies the size of a buffer page for output SAS data sets. CGOPTIMIZE=3 Specifies the level of optimization to perform during code compilation. CMPMODEL=BOTH Specifies the output model type for the MODEL procedure. CMPOPT=(NOEXTRAMATH NOMISSCHECK NOPRECISE NOGUARDCHECK NOGENSYMNAMES NOFUNCDIFFERENCING) Specifies the type of code-generation optimizations to use in the SAS language compiler. COMPRESS=NO Specifies the type of compression to use for observations in output SAS data sets. CPUCOUNT=24 Specifies the number of processors that thread-enabled applications should assume are available for concurrent processing. NODBIDIRECTEXEC The SQL pass-through facility does not optimize the handling of SQL statements. DBSLICEPARM=(THREADED_APPS, 2) Specifies whether SAS procedures, applications, and the DATA step can read DBMS tables in parallel, and the number of threads to use to read the DBMS tables. MAXSEGRATIO=75 Specifies the upper limit for the percentage of index segments that the SPD Engine identifies as containing the value referenced in the WHERE expression. MEXECSIZE=65536 Specifies the maximum macro size that can be executed in memory. MINPARTSIZE=16777216 Specifies the minimum size of the data component partitions for SPD Engine data sets. SORTSIZE=1073741824 Specifies the amount of memory that is available to the SORT procedure. NOSPDEFILECACHE Disables caching of opened SPD Engine files. SPDEINDEXSORTSIZE=33554432 Specifies the memory size for sorting index values. SPDEMAXTHREADS=0 Specifies the maximum number of threads that the SPD Engine can spawn for I/O processing. SPDEPARALLELREAD=NO Enables or disables SPD Engine parallel reads when no WHERE clause is in effect. SPDESORTSIZE=33554432 Specifies the memory size that is used for sorting by the SPD Engine. SPDEUTILLOC= Specifies one or more locations where the SPD Engine can temporarily store utility files. SPDEWHEVAL=COST Specifies the WHERE statement evaluation process for the SPD Engine. SQLGENERATION=(NONE DBMS='TERADATA DB2 ORACLE NETEZZA ASTER GREENPLM HADOOP SAPHANA') Specifies whether and when SAS procedures generate SQL for in-database processing of source data. SQLREDUCEPUT=DBMS For PROC SQL, specifies the engine type to use to optimize a PUT function in a query. SQLREDUCEPUTOBS=0 For PROC SQL, specifies the minimum number of observations that must be in a table for PROC SQL to optimize the PUT function in a query. SQLREDUCEPUTVALUES=0 For PROC SQL, specifies the maximum number of SAS format values that can exist in a PUT function expression to optimize the PUT function in a query. STRIPESIZE= Specifies path and size pairs to identify I/O device stripe size. Stripe size indicates page size when creating a data set or utility file. THREADS Uses threaded processing for SAS applications that support it. UTILLOC="E:\UTILLOC" Specifies one or more file system locations in which threaded applications can store utility files. WORK=E:\WORK\_TD17824_CSSSASAPP_\Prc2 Specifies the libref or location of the Work library. SLEEPCNTL=NO Specifies whether SAS prevents Windows from going into sleep mode. 3 The SAS System 16:00 Wednesday, August 24, 2016 ALIGNSASIOFILES Aligns SAS files on a page boundary for improved performance. MEMSIZE=73567756800 Specifies the limit on the amount of virtual memory that can be used during a SAS session. NOTE: PROCEDURE OPTIONS used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 130.21k OS Memory 25440.00k Timestamp 08/24/2016 04:16:06 PM Step Count 28 Switch Count 140 29 LIBNAME _ALL_ LIST; NOTE: Libref= WORK Scope= IOM ROOT COMP ENV Engine= V9 Access= TEMP Physical Name= E:\WORK\_TD17824_CSSSASAPP_\Prc2 Filename= E:\WORK\_TD17824_CSSSASAPP_\Prc2 NOTE: Libref= DAILY Scope= IOM ROOT COMP ENV Engine= BASE Physical Name= \\CSSSASAPP\CADA\SAS Data\DataMart\eadvdata\Daily Filename= \\CSSSASAPP\CADA\SAS Data\DataMart\eadvdata\Daily NOTE: Libref= ECACTR Scope= IOM ROOT COMP ENV Engine= BASE Physical Name= \\CSSSASAPP\CADA\SAS Data\DataMart\ECA Filename= \\CSSSASAPP\CADA\SAS Data\DataMart\ECA NOTE: Libref= TT Scope= IOM ROOT COMP ENV Engine= V9 Physical Name= \\aacadasas\cada\SAS Data\User\MZHANG\Title Loans Filename= \\aacadasas\cada\SAS Data\User\MZHANG\Title Loans NOTE: Libref= SASDATA Scope= Object Server Engine= BASE Physical Name= D:\SAS\Config\Lev1\SASApp\Data Filename= D:\SAS\Config\Lev1\SASApp\Data NOTE: Libref= KITS Scope= Object Server Engine= BASE Access= READONLY Physical Name= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\Datamart\kits Filename= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\Datamart\kits NOTE: Libref= EVDM Scope= Object Server Engine= META Access= READONLY Physical Name= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\Datamart\evdm Filename= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\Datamart\evdm NOTE: Libref= ARTIFACT Scope= Object Server Engine= BASE Access= READONLY Physical Name= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\apm\Data\artifacts Filename= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\apm\Data\artifacts 4 The SAS System 16:00 Wednesday, August 24, 2016 NOTE: Libref= ACM Scope= Object Server Engine= BASE Access= READONLY Physical Name= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\Datamart\acm Filename= D:\SAS\Config\Lev1\Web\SASEnvironmentManager\emi-framework\Datamart\acm NOTE: Libref= NGSILO Scope= Object Server Engine= ORACLE Physical Name= PEDWQA1 Schema/User= SC_SILO_NG NOTE: Libref= ONLINE Scope= Object Server Engine= BASE Physical Name= \\aacadasas\cada\sas data\datamart\online Filename= \\aacadasas\cada\sas data\datamart\online NOTE: Libref= ONLINEP Scope= Object Server Engine= ORACLE Physical Name= BIOR Schema/User= ONLINEDATA NOTE: Libref= CORIOS Scope= Object Server Engine= BASE Physical Name= E:\Shared\CADA\SAS Data\Corios\Data Filename= E:\Shared\CADA\SAS Data\Corios\Data NOTE: Libref= SCDPEDW Scope= Object Server Engine= ORACLE Physical Name= PEDWQA1 Schema/User= SC_DMARTS NOTE: Libref= COMMON Scope= Object Server Engine= BASE Physical Name= E:\Shared\CADA\SAS Data\Common Shared Filename= E:\Shared\CADA\SAS Data\Common Shared NOTE: Libref= NGAR Scope= Object Server Engine= BASE Physical Name= E:\Shared\CADA\SAS Data\DataMart\NextGen\Accounting Reports Filename= E:\Shared\CADA\SAS Data\DataMart\NextGen\Accounting Reports NOTE: Libref= NEXTGEN Scope= Object Server Engine= BASE Physical Name= E:\Shared\CADA\SAS eMiner\UW Objects\NextGen data Filename= E:\Shared\CADA\SAS eMiner\UW Objects\NextGen data NOTE: Libref= MONEYGRM Scope= Object Server Engine= META Physical Name= EPPROD1 Schema/User= PSOWNER NOTE: Libref= MODELMIS Scope= Object Server Engine= BASE Physical Name= E:\Shared\CADA\SAS eMiner\UW Objects\Model Misc Filename= E:\Shared\CADA\SAS eMiner\UW Objects\Model Misc NOTE: Libref= TITLE Scope= Object Server 5 The SAS System 16:00 Wednesday, August 24, 2016 Engine= BASE Physical Name= E:\Shared\CADA\SAS eMiner\UW Objects\Title data Filename= E:\Shared\CADA\SAS eMiner\UW Objects\Title data NOTE: Libref= INSTALL Scope= Object Server Engine= BASE Physical Name= E:\Shared\CADA\SAS eMiner\UW Objects\Installment data Filename= E:\Shared\CADA\SAS eMiner\UW Objects\Installment data NOTE: Libref= ETAX Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= EDW_RPT NOTE: Libref= EADVDATA Scope= Object Server Engine= BASE Physical Name= E:\Shared\CADA\SAS Data\DataMart\eadvdata Filename= E:\Shared\CADA\SAS Data\DataMart\eadvdata NOTE: Libref= BIOR Scope= Object Server Engine= ORACLE Physical Name= BIOR Schema/User= BIOR NOTE: Libref= TXTITLE Scope= Object Server Engine= META Physical Name= EDWPRD Schema/User= edw NOTE: Libref= QFUND3 Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= QFUND3 NOTE: Libref= SCODSBI Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= SC_ODS_BI NOTE: Libref= RTDM Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= RTDM NOTE: Libref= QFUND1 Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= QFUND1 NOTE: Libref= DWQ1FIN Scope= Object Server Engine= ORACLE Physical Name= PEDWQA1 Schema/User= SC_ODS_FIN NOTE: Libref= OHCSO Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= EDW 6 The SAS System 16:00 Wednesday, August 24, 2016 NOTE: Libref= NGPRODV Scope= Object Server Engine= ORACLE Physical Name= NGRPTNG Schema/User= SC_DMARTS NOTE: Libref= FVS Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= STG_QFUND_VS NOTE: Libref= EEE Scope= Object Server Engine= ORACLE Physical Name= EAPROD1 Schema/User= EEE NOTE: Libref= EDW_STAR Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= EDW_STAR NOTE: Libref= EDW_ST Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= EDW_ST NOTE: Libref= EDW Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= EDW NOTE: Libref= ECA Scope= Object Server Engine= ORACLE Physical Name= EDWPRD Schema/User= ECA NOTE: Libref= CADA Scope= Object Server Engine= ORACLE Physical Name= BIOR Schema/User= CADA NOTE: Libref= EADV Scope= Object Server Engine= ORACLE Physical Name= EAPROD1 Schema/User= eadv NOTE: Libref= STPSAMP Scope= Object Server Engine= BASE Access= READONLY Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\inttech\sample Filename= C:\Program Files\SASHome\SASFoundation\9.4\inttech\sample NOTE: Libref= SASHELP Scope= Kernel Levels= 19 -Level 1- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\nls\en\SASCFG Filename= C:\Program Files\SASHome\SASFoundation\9.4\nls\en\SASCFG 7 The SAS System 16:00 Wednesday, August 24, 2016 -Level 2- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\core\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\core\sashelp -Level 3- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\aacomp\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\aacomp\sashelp -Level 4- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\bimva\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\bimva\sashelp -Level 5- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\cmp\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\cmp\sashelp -Level 6- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\connect\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\connect\sashelp -Level 7- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\creditscrnod\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\creditscrnod\sashelp -Level 8- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\dcmsvr\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\dcmsvr\sashelp -Level 9- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\dmine\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\dmine\sashelp -Level 10- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\ets\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\ets\sashelp -Level 11- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\graph\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\graph\sashelp -Level 12- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\iml\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\iml\sashelp -Level 13- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\inttech\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\inttech\sashelp -Level 14- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\lasr\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\lasr\sashelp -Level 15- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\mmcommon\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\mmcommon\sashelp -Level 16- Engine= V9 8 The SAS System 16:00 Wednesday, August 24, 2016 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\mmcore\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\mmcore\sashelp -Level 17- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\or\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\or\sashelp -Level 18- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\spdsclient\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\spdsclient\sashelp -Level 19- Engine= V9 Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\stat\sashelp Filename= C:\Program Files\SASHome\SASFoundation\9.4\stat\sashelp NOTE: Libref= MAPS Scope= Kernel Engine= V9 Access= READONLY Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\maps Filename= C:\Program Files\SASHome\SASFoundation\9.4\maps NOTE: Libref= MAPSSAS Scope= Kernel Engine= V9 Access= READONLY Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\maps Filename= C:\Program Files\SASHome\SASFoundation\9.4\maps NOTE: Libref= MAPSGFK Scope= Kernel Engine= V9 Access= READONLY Physical Name= C:\Program Files\SASHome\SASFoundation\9.4\mapsgfk Filename= C:\Program Files\SASHome\SASFoundation\9.4\mapsgfk NOTE: Libref= SASUSER Scope= Kernel Engine= V9 Access= READONLY Physical Name= D:\SASWork\My SAS Files\9.4 Filename= D:\SASWork\My SAS Files\9.4 30 PROC OPTIONS OPTION=WORK; SAS (r) Proprietary Software Release 9.4 TS1M2 WORK=E:\WORK\_TD17824_CSSSASAPP_\Prc2 Specifies the libref or location of the Work library. NOTE: PROCEDURE OPTIONS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 19.09k OS Memory 25440.00k Timestamp 08/24/2016 04:16:06 PM Step Count 29 Switch Count 28 31 PROC OPTIONS OPTION=UTILLOC; 32 RUN; SAS (r) Proprietary Software Release 9.4 TS1M2 9 The SAS System 16:00 Wednesday, August 24, 2016 UTILLOC="E:\UTILLOC" Specifies one or more file system locations in which threaded applications can store utility files. NOTE: PROCEDURE OPTIONS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 19.09k OS Memory 25440.00k Timestamp 08/24/2016 04:16:06 PM Step Count 30 Switch Count 32 33 DATA _NULL_; 34 %PUT This job started on &sysdate at &systime; This job started on 24AUG16 at 16:00 35 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 211.59k OS Memory 25440.00k Timestamp 08/24/2016 04:16:06 PM Step Count 31 Switch Count 28 36 37 libname tt '\\aacadasas\cada\SAS Data\User\MZHANG\Title Loans'; NOTE: Libref TT was successfully assigned as follows: Engine: V9 Physical Name: \\aacadasas\cada\SAS Data\User\MZHANG\Title Loans 38 LIBNAME ECACTR BASE "\\CSSSASAPP\CADA\SAS Data\DataMart\ECA"; NOTE: Libref ECACTR was successfully assigned as follows: Engine: BASE Physical Name: \\CSSSASAPP\CADA\SAS Data\DataMart\ECA 39 %let date1=%sysfunc(intnx(month,"&sysdate"d+2,-1,end),date9. ); 40 %put &date1; 31JUL2016 41 %let DBD=35; 42 %let rundt=%sysfunc(putc(&date1,$10.)); 43 %put &rundt; 31JUL2016 44 %let date=%sysfunc(intnx(month,"&sysdate"d+2,-1,end) ); 45 LIBNAME DAILY BASE "\\CSSSASAPP\CADA\SAS Data\DataMart\eadvdata\Daily" ; NOTE: Libref DAILY was successfully assigned as follows: Engine: BASE Physical Name: \\CSSSASAPP\CADA\SAS Data\DataMart\eadvdata\Daily 46 data QF_TP_TRANSACTION_DATA; 47 set ECA.QF_TP_TRANSACTION_DATA ; 48 run; NOTE: There were 13680822 observations read from the data set ECA.QF_TP_TRANSACTION_DATA. NOTE: The data set WORK.QF_TP_TRANSACTION_DATA has 13680822 observations and 28 variables. NOTE: DATA statement used (Total process time): real time 9:20.96 user cpu time 7:29.93 10 The SAS System 16:00 Wednesday, August 24, 2016 system cpu time 11.96 seconds memory 1590.28k OS Memory 26924.00k Timestamp 08/24/2016 04:25:27 PM Step Count 32 Switch Count 45624 49 proc sort data= QF_TP_TRANSACTION_DATA out=tt.tran; 50 BY CUSTOMER_NBR 51 LOAN_NBR 52 TITLE_LOAN_NBR 53 TRANSACTION_DATE 54 TRANSACTION_NBR; 55 run; NOTE: There were 13680822 observations read from the data set WORK.QF_TP_TRANSACTION_DATA. NOTE: SAS threaded sort was used. NOTE: The data set TT.TRAN has 13680822 observations and 28 variables. NOTE: PROCEDURE SORT used (Total process time): real time 2:41.04 user cpu time 30.84 seconds system cpu time 24.92 seconds memory 1056195.56k OS Memory 1081680.00k Timestamp 08/24/2016 04:28:08 PM Step Count 33 Switch Count 265 56 57 proc sql; 58 create table tt.tran as 59 select l.*, b.state_code 60 from tt.tran l, eca.qf_branch_data b 61 where l.branch_nbr=b.branch_nbr; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: Table TT.TRAN created, with 13680821 rows and 29 columns. 62 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2:35.69 user cpu time 11.53 seconds system cpu time 7.98 seconds memory 3448.71k OS Memory 29284.00k Timestamp 08/24/2016 04:30:44 PM Step Count 34 Switch Count 247 63 64 data t4states; 65 set tt.tran; 66 where state_code not in ('LA'); 67 68 if transaction_type='WO' and state_code='VA' then 69 BAL_PRINCIPAL_AMT=0; 70 71 if transaction_type='WO' and state_code='VA' then 11 The SAS System 16:00 Wednesday, August 24, 2016 72 BAL_INT_AMOUNT=0; 73 74 if transaction_type='WO' and state_code='VA' then 75 BAL_LIEN_FEE_AMT=0; 76 77 if transaction_type='WO' and state_code='VA' then 78 REPO_CHARGE=0; 79 80 if transaction_type='WO' and state_code='VA' then 81 STORAGE_COST=0; 82 83 if transaction_type='WO' and state_code='VA' then 84 LATE_FEE=0; 85 86 if transaction_type='WO' and state_code='VA' then 87 OTHER_FEE=0; 88 drop 89 IS_DECEASED 90 IS_CSR 91 CREATE_DATE_TIME 92 UPDATE_DATE_TIME 93 CREATE_USER_NM 94 UPDATE_USER_NM 95 CREATE_PROGRAM_NM 96 UPDATE_PROGRAM_NM 97 DATE_CREATED 98 CREATED_BY; 99 run; NOTE: There were 11776651 observations read from the data set TT.TRAN. WHERE state_code not = 'LA'; NOTE: The data set WORK.T4STATES has 11776651 observations and 19 variables. NOTE: DATA statement used (Total process time): real time 1:12.35 user cpu time 5.59 seconds system cpu time 4.29 seconds memory 546.53k OS Memory 26464.00k Timestamp 08/24/2016 04:31:56 PM Step Count 35 Switch Count 282 100 101 data tt; 102 set t4states; 103 104 if BAL_INT_AMOUNT >=0 or BAL_INT_AMOUNT=. then 105 interest=0; 106 else interest=BAL_INT_AMOUNT; 107 108 if BAL_LIEN_FEE_AMT >=0 or BAL_LIEN_FEE_AMT=. then 109 lien=0; 110 else lien=BAL_LIEN_FEE_AMT; 111 112 if LATE_FEE >=0 or LATE_FEE=. then 113 late=0; 114 else late=LATE_FEE; 115 12 The SAS System 16:00 Wednesday, August 24, 2016 116 if OTHER_FEE >=0 or OTHER_FEE=. then 117 other=0; 118 else other= OTHER_FEE; 119 120 if REPO_CHARGE >=0 or REPO_CHARGE=. then 121 repo=0; 122 else repo=REPO_CHARGE; 123 124 if STORAGE_COST >=0 or STORAGE_COST=. then 125 storage=0; 126 else storage=STORAGE_COST; 127 128 if BAL_PRINCIPAL_AMT>=0 or BAL_PRINCIPAL_AMT=. then 129 principal_paid=0; 130 else principal_paid=BAL_PRINCIPAL_AMT; 131 132 if REPO_CHARGE >=0 then 133 repo_cashout=REPO_CHARGE; 134 else repo_cashout=0; 135 136 if STORAGE_COST >=0 then 137 stoc_cashout=STORAGE_COST; 138 else stoc_cashout=0; 139 140 if OTHER_FEE>=0 then 141 repo2_cashout=OTHER_FEE; 142 else repo2_cashout=0; 143 144 run; NOTE: There were 11776651 observations read from the data set WORK.T4STATES. NOTE: The data set WORK.TT has 11776651 observations and 29 variables. NOTE: DATA statement used (Total process time): real time 19.73 seconds user cpu time 2.42 seconds system cpu time 4.62 seconds memory 535.87k OS Memory 26464.00k Timestamp 08/24/2016 04:32:16 PM Step Count 36 Switch Count 224 145 146 proc sort data= tt; 147 BY 148 LOAN_NBR 149 TITLE_LOAN_NBR 150 TRANSACTION_DATE 151 TRANSACTION_NBR; 152 run; NOTE: There were 11776651 observations read from the data set WORK.TT. NOTE: SAS threaded sort was used. NOTE: The data set WORK.TT has 11776651 observations and 29 variables. NOTE: PROCEDURE SORT used (Total process time): real time 48.70 seconds user cpu time 17.45 seconds system cpu time 13.95 seconds 13 The SAS System 16:00 Wednesday, August 24, 2016 memory 1059164.84k OS Memory 1084892.00k Timestamp 08/24/2016 04:33:04 PM Step Count 37 Switch Count 110 153 154 data principal; 155 set tt; 156 BY 157 LOAN_NBR 158 TITLE_LOAN_NBR 159 TRANSACTION_DATE 160 TRANSACTION_NBR; 161 162 if first.loan_nbr; 163 principal= BAL_PRINCIPAL_AMT; 164 trandt=datepart(transaction_date); 165 run; NOTE: There were 11776651 observations read from the data set WORK.TT. NOTE: The data set WORK.PRINCIPAL has 215068 observations and 31 variables. NOTE: DATA statement used (Total process time): real time 25.39 seconds user cpu time 2.87 seconds system cpu time 1.51 seconds memory 629.06k OS Memory 26464.00k Timestamp 08/24/2016 04:33:30 PM Step Count 38 Switch Count 100 166 167 proc sql; 168 create table t_t as 169 select a.*, 170 case when b.principal=. then 0 171 else b.principal 172 end as princ_cashout 173 from tt a left join principal b 174 on a.loan_nbr=b.loan_nbr and a.transaction_nbr=b.transaction_nbr and a.transaction_date=b.transaction_date ; NOTE: SAS threaded sort was used. NOTE: Table WORK.T_T created, with 11776651 rows and 30 columns. 175 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:12.56 user cpu time 19.03 seconds system cpu time 11.78 seconds memory 1052103.78k OS Memory 1076992.00k Timestamp 08/24/2016 04:34:42 PM Step Count 39 Switch Count 80 176 177 178 proc sql; 14 The SAS System 16:00 Wednesday, August 24, 2016 179 create table t_t2 as 180 select a.*,b.trandt as open_dt format=mmddyy.,b.principal as adv 181 from t_t a left join principal b 182 on a.loan_nbr=b.loan_nbr ; NOTE: SAS threaded sort was used. NOTE: Table WORK.T_T2 created, with 11776651 rows and 32 columns. 183 quit; NOTE: PROCEDURE SQL used (Total process time): real time 25.41 seconds user cpu time 4.56 seconds system cpu time 3.26 seconds memory 14347.34k OS Memory 39608.00k Timestamp 08/24/2016 04:35:08 PM Step Count 40 Switch Count 72 184 185 186 proc sql; 187 create table tt2 as 188 select *,-1*sum(interest,lien,late,other,repo,storage,principal_paid) as cashin, 189 sum( repo_cashout,stoc_cashout, princ_cashout,repo2_cashout) as cashout, 190 calculated cashin-calculated cashout as net_cf 191 192 from t_t2 193 group by loan_nbr 194 order BY 195 LOAN_NBR, 196 TITLE_LOAN_NBR, 197 TRANSACTION_DATE, 198 TRANSACTION_NBR; NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated table-expression referenced a summary function. NOTE: SAS threaded sort was used. NOTE: Table WORK.TT2 created, with 11776651 rows and 35 columns. 199 quit; NOTE: PROCEDURE SQL used (Total process time): real time 48.45 seconds user cpu time 18.51 seconds system cpu time 10.93 seconds memory 1052431.03k OS Memory 1078376.00k Timestamp 08/24/2016 04:35:56 PM Step Count 41 Switch Count 96 200 201 data tt3; 202 set tt2; 203 BY 204 LOAN_NBR 205 TITLE_LOAN_NBR 206 TRANSACTION_DATE 207 TRANSACTION_NBR; 208 15 The SAS System 16:00 Wednesday, August 24, 2016 209 if first.loan_nbr then 210 num=0; 211 num+1; 212 trandt=datepart(transaction_date); 213 format trandt mmddyy.; 214 215 run; NOTE: There were 11776651 observations read from the data set WORK.TT2. NOTE: The data set WORK.TT3 has 11776651 observations and 37 variables. NOTE: DATA statement used (Total process time): real time 26.68 seconds user cpu time 3.51 seconds system cpu time 3.48 seconds memory 643.90k OS Memory 27488.00k Timestamp 08/24/2016 04:36:23 PM Step Count 42 Switch Count 116 216 217 218 proc sql; 219 create table default as 220 select loan_nbr, transaction_type,num, TITLE_LOAN_NBR, 221 TRANSACTION_DATE, 222 TRANSACTION_NBR 223 from tt3 224 where transaction_type in ('DEF','REPO','STOC','AGND','WO','WOB','SLD','DSLD','DFP','RPO','SET') 225 order by loan_nbr,num 226 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.DEFAULT created, with 195817 rows and 6 columns. 227 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.09 seconds user cpu time 0.84 seconds system cpu time 1.09 seconds memory 25671.46k OS Memory 51576.00k Timestamp 08/24/2016 04:36:25 PM Step Count 43 Switch Count 60 228 229 data recover; 230 set default; 231 by loan_nbr num; 232 233 if last.LOAN_NBR; 234 235 run; NOTE: There were 195817 observations read from the data set WORK.DEFAULT. NOTE: The data set WORK.RECOVER has 58603 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.10 seconds 16 The SAS System 16:00 Wednesday, August 24, 2016 user cpu time 0.07 seconds system cpu time 0.01 seconds memory 569.62k OS Memory 27488.00k Timestamp 08/24/2016 04:36:25 PM Step Count 44 Switch Count 52 236 237 data recover2; 238 set recover; 239 where transaction_type not in('AGND','DFP','RPO'); 240 bad=1; 241 run; NOTE: There were 45135 observations read from the data set WORK.RECOVER. WHERE transaction_type not in ('AGND', 'DFP', 'RPO'); NOTE: The data set WORK.RECOVER2 has 45135 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.07 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 514.06k OS Memory 27488.00k Timestamp 08/24/2016 04:36:25 PM Step Count 45 Switch Count 42 242 243 244 proc sql; 245 create table tt4 as 246 select a.*, 247 case 248 when b.bad=. then 1 249 else 0 250 end 251 as good 252 from tt3 a left join recover2 b 253 on a.loan_nbr=b.loan_nbr 254 255 order by LOAN_NBR, 256 num; NOTE: SAS threaded sort was used. NOTE: Table WORK.TT4 created, with 11776651 rows and 38 columns. 257 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:21.33 user cpu time 32.54 seconds system cpu time 28.31 seconds memory 1057278.01k OS Memory 1082068.00k Timestamp 08/24/2016 04:37:46 PM Step Count 46 Switch Count 93 258 17 The SAS System 16:00 Wednesday, August 24, 2016 259 260 261 PROC SQL; 262 CREATE TABLE void AS 263 SELECT transaction_nbr,REF_TRAN_CODE 264 265 FROM TT4 t1 266 WHERE t1.VOID_FLAG = 'V'; NOTE: Table WORK.VOID created, with 16428 rows and 2 columns. 267 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 12.03 seconds user cpu time 0.40 seconds system cpu time 1.42 seconds memory 3253.87k OS Memory 30564.00k Timestamp 08/24/2016 04:37:59 PM Step Count 47 Switch Count 54 268 269 270 proc sql; 271 create table tt5 as 272 select s.* 273 from tt4 s 274 where transaction_nbr not in (select transaction_nbr from void) and transaction_nbr not in (select ref_tran_code from 274 ! void); NOTE: Table WORK.TT5 created, with 11743796 rows and 38 columns. 275 quit; NOTE: PROCEDURE SQL used (Total process time): real time 33.29 seconds user cpu time 15.82 seconds system cpu time 4.18 seconds memory 3563.12k OS Memory 30564.00k Timestamp 08/24/2016 04:38:32 PM Step Count 48 Switch Count 92 276 277 /*compare AA and ECA*/ 278 279 proc sql; 280 create table eca_state_loan1 as 281 select s.*, e.AACherwell, 282 case 283 when e.AACherwell=. then 'AA' 284 else 'ECA' 285 end as company 286 from tt5 s 287 left join ecactr.DATA_IMPORTED_FROM_ECA_QFUND_TO_ e 288 on s.branch_nbr=e.ECAQFund; NOTE: SAS threaded sort was used. NOTE: Table WORK.ECA_STATE_LOAN1 created, with 11743796 rows and 40 columns. 18 The SAS System 16:00 Wednesday, August 24, 2016 289 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:36.04 user cpu time 20.59 seconds system cpu time 19.68 seconds memory 1052016.92k OS Memory 1077888.00k Timestamp 08/24/2016 04:40:08 PM Step Count 49 Switch Count 84 290 291 292 293 294 proc sql; 295 create table tt8 as 296 select s.*, 297 case 298 when e.CLOSED_DATE=. then 'open' 299 else 'close' 300 end as close 301 from eca_state_loan1 s 302 left join eca.qf_branch_data e 303 on s.branch_nbr =e.branch_nbr ; NOTE: Table WORK.TT8 created, with 11743796 rows and 41 columns. 304 quit; NOTE: PROCEDURE SQL used (Total process time): real time 34.32 seconds user cpu time 5.25 seconds system cpu time 5.03 seconds memory 3457.59k OS Memory 30820.00k Timestamp 08/24/2016 04:40:42 PM Step Count 50 Switch Count 96 305 306 data store; 307 set ECA.TBLSTOREINFO; 308 309 open_dt=datepart(OPENDATE); 310 storenbr=input(store,best.); 311 run; NOTE: Invalid argument to function INPUT at line 310 column 11. REGION=1 DIVISION=1C STORE=107A STORENAME=107A - GREER OPENDATE=25MAR2004:00:00:00 OPENCLOSED=0 CLOSEDATE=01DEC2006:00:00:00 STORETYPE=CA STATE=SC PAYDAYLOANSTARTDATE=. PAYDAYLOANENDDATE=. SLOCSTARTDATE=. SLOCENDDATE=. SLOC2STARTDATE=. SLOC2ENDDATE=. ULOCSTARTDATE=. ULOCENDDATE=. TITLEPLEDGESTARTDATE=. TITLEPLEDGEENDDATE=. CHECKCASHINGSTARTDATE=. CHECKCASHINGENDDATE=. BILLPAYSTARTDATE=. BILLPAYENDDATE=. PREPAIDHOMEPHONESTARTDATE=. PREPAIDHOMEPHONEENDDATE=. WIRETRANSFERSTARTDATE=. WIRETRANSFERENDDATE=. PREPAIDDEBITSTARTDATE=. PREPAIDDEBITENDDATE=. EXTENDEDPAYMENTPLANSTARTDATE=. EXTENDEDPAYMENTPLANENDDATE=. PREPAIDCELLPHONESTARTDATE=. PREPAIDCELLPHONEENDDATE=. GOLDBUYSTARTDATE=. GOLDBUYENDDATE=. INSTALLMENTLOANSTARTDATE=. INSTALLMENTLOANENDDATE=. CREATE_DATE_TIME=13SEP2013:15:09:04 UPDATE_DATE_TIME=. CREATE_USER_NM=SVC_DSUSER UPDATE_USER_NM= CREATE_PROGRAM_NM=TBLSTOREINFO_L_History UPDATE_PROGRAM_NM= open_dt=16155 storenbr=. _ERROR_=1 _N_=17 NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 5 at 309:10 19 The SAS System 16:00 Wednesday, August 24, 2016 NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values. Each place is given by: (Number of times) at (Line):(Column). 1 at 310:11 NOTE: There were 720 observations read from the data set ECA.TBLSTOREINFO. NOTE: The data set WORK.STORE has 720 observations and 45 variables. NOTE: DATA statement used (Total process time): real time 5.22 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 1785.56k OS Memory 28600.00k Timestamp 08/24/2016 04:40:47 PM Step Count 51 Switch Count 83 312 313 314 315 proc sql; 316 create table tt9 as 317 select s.*,e.open_dt as storeopen format=mmddyy. 318 319 from tt8 s 320 left join store e 321 on s.branch_nbr =e.storenbr 322 /*having storeopen<=s.open_dt*/ 323 ; NOTE: Table WORK.TT9 created, with 11743796 rows and 42 columns. 324 quit; NOTE: PROCEDURE SQL used (Total process time): real time 45.53 seconds user cpu time 4.62 seconds system cpu time 4.98 seconds memory 3484.34k OS Memory 30820.00k Timestamp 08/24/2016 04:41:33 PM Step Count 52 Switch Count 106 325 326 327 PROC SQL; 328 CREATE TABLE tt.tt10 AS 329 SELECT t1.* 330 FROM WORK.TT9 t1 331 WHERE t1.close = 'open' 332 ORDER BY t1.TRANSACTION_DATE; NOTE: SAS threaded sort was used. NOTE: Table TT.TT10 created, with 11743796 rows and 42 columns. 333 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 1:32.29 user cpu time 21.45 seconds system cpu time 17.26 seconds memory 1050904.21k 20 The SAS System 16:00 Wednesday, August 24, 2016 OS Memory 1077072.00k Timestamp 08/24/2016 04:43:05 PM Step Count 53 Switch Count 83 334 335 336 337 proc sql; 338 create table tt.tt10 as 339 select a.*,b.vin 340 from tt.tt10 a left join eca.QF_TP_VEHICLE_DETAILS b 341 on a.loan_nbr=b.loan_nbr; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table TT.TT10 created, with 11743796 rows and 43 columns. 342 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2:44.39 user cpu time 30.98 seconds system cpu time 21.65 seconds memory 1051229.12k OS Memory 1077072.00k Timestamp 08/24/2016 04:45:50 PM Step Count 54 Switch Count 280 343 344 data loan1; 345 set tt.tran; 346 BY CUSTOMER_NBR 347 LOAN_NBR 348 TITLE_LOAN_NBR 349 TRANSACTION_DATE 350 TRANSACTION_NBR; 351 352 if first.CUSTOMER_NBR; 353 first_dt=datepart(TRANSACTION_DATE); 354 format first_dt mmddyy.; 355 run; NOTE: There were 13680821 observations read from the data set TT.TRAN. NOTE: The data set WORK.LOAN1 has 168202 observations and 30 variables. NOTE: DATA statement used (Total process time): real time 49.56 seconds user cpu time 5.65 seconds system cpu time 1.65 seconds memory 637.62k OS Memory 27488.00k Timestamp 08/24/2016 04:46:39 PM Step Count 55 Switch Count 140 356 357 proc sql; 358 create table tt as 21 The SAS System 16:00 Wednesday, August 24, 2016 359 select t1.company, 360 t1.CUSTOMER_NBR, t1.STATE_CODE,t1.LOAN_NBR,t1.BRANCH_NBR,t1.TRANSACTION_TYPE,t1.TRANSACTION_DATE, 361 t1.TOTAL_AMOUNT_DUE,t1.TRANSACTION_NBR,t1.TITLE_LOAN_NBR, 362 t1.interest, 363 t1.lien, 364 t1.late, 365 t1.other, 366 t1.repo, 367 t1.storage, 368 t1.principal_paid, 369 t1.repo_cashout, 370 t1.stoc_cashout, 371 t1.repo2_cashout, 372 t1.princ_cashout, 373 t1.open_dt, 374 t1.adv, 375 t1.cashin, 376 t1.cashout, 377 t1.net_cf, 378 t1.num, 379 t1.trandt, 380 t1.good, 381 t1.storeopen,b.first_dt,t1.BAL_PRINCIPAL_AMT,t1.BAL_INT_AMOUNT,t1.REPO_CHARGE 382 383 from tt.tt10 t1 left join loan1 b 384 on t1.customer_nbr=b.customer_nbr 385 order BY 386 LOAN_NBR, 387 TITLE_LOAN_NBR, 388 TRANSACTION_DATE, 389 TRANSACTION_NBR; NOTE: SAS threaded sort was used. NOTE: Table WORK.TT created, with 11743796 rows and 34 columns. 390 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2:03.84 user cpu time 34.21 seconds system cpu time 23.82 seconds memory 1057946.90k OS Memory 1083068.00k Timestamp 08/24/2016 04:48:43 PM Step Count 56 Switch Count 240 391 392 proc sql; 393 create table tt as 394 select a.*, b.MAKE,b.YEAR_,b.vin 395 from tt a left join eca.QF_TP_VEHICLE_DETAILS b 396 on a.loan_nbr=b.loan_nbr 397 having a.open_dt<=&date 398 order by LOAN_NBR, 399 TITLE_LOAN_NBR, 400 TRANSACTION_DATE, 401 TRANSACTION_NBR; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. 22 The SAS System 16:00 Wednesday, August 24, 2016 NOTE: SAS threaded sort was used. NOTE: Table WORK.TT created, with 11731980 rows and 37 columns. 402 quit; NOTE: PROCEDURE SQL used (Total process time): real time 46.55 seconds user cpu time 24.68 seconds system cpu time 10.73 seconds memory 1052387.07k OS Memory 1078276.00k Timestamp 08/24/2016 04:49:30 PM Step Count 57 Switch Count 467 403 404 proc sql; 405 create table sch_deal as 406 select a.*,b.due_date,TOTAL_AMOUNT_DUE 407 from tt a inner join eca.qf_tp_loan_data b 408 on a.LOAN_NBR=b.LOAN_NBR and a.TITLE_LOAN_NBR=b.TITLE_LOAN_NBR 409 410 order by loan_nbr, trandt; WARNING: Variable TOTAL_AMOUNT_DUE already exists on file WORK.SCH_DEAL. NOTE: SAS threaded sort was used. NOTE: Table WORK.SCH_DEAL created, with 11731980 rows and 38 columns. 411 quit; NOTE: PROCEDURE SQL used (Total process time): real time 54.37 seconds user cpu time 25.39 seconds system cpu time 10.96 seconds memory 1051201.46k OS Memory 1077084.00k Timestamp 08/24/2016 04:50:24 PM Step Count 58 Switch Count 412 412 413 data sch_deal2; 414 set sch_deal; 415 due_dt=datepart(due_date); 416 format due_dt mmddyy.; 417 run; NOTE: There were 11731980 observations read from the data set WORK.SCH_DEAL. NOTE: The data set WORK.SCH_DEAL2 has 11731980 observations and 39 variables. NOTE: DATA statement used (Total process time): real time 20.22 seconds user cpu time 1.70 seconds system cpu time 3.53 seconds memory 564.12k OS Memory 27744.00k Timestamp 08/24/2016 04:50:44 PM Step Count 59 Switch Count 64 418 419 proc sort data=sch_deal2; 23 The SAS System 16:00 Wednesday, August 24, 2016 420 by 421 LOAN_NBR 422 TITLE_LOAN_NBR 423 TRANSACTION_DATE 424 TRANSACTION_NBR; 425 quit; NOTE: There were 11731980 observations read from the data set WORK.SCH_DEAL2. NOTE: SAS threaded sort was used. NOTE: The data set WORK.SCH_DEAL2 has 11731980 observations and 39 variables. NOTE: PROCEDURE SORT used (Total process time): real time 57.65 seconds user cpu time 16.96 seconds system cpu time 17.45 seconds memory 1054436.73k OS Memory 1081408.00k Timestamp 08/24/2016 04:51:42 PM Step Count 60 Switch Count 110 426 427 data sch_deal3; 428 set sch_deal2; 429 by 430 LOAN_NBR 431 TITLE_LOAN_NBR 432 TRANSACTION_DATE 433 TRANSACTION_NBR; 434 435 if first.TITLE_LOAN_NBR; 436 sub_dealdt=trandt; 437 run; NOTE: There were 11731980 observations read from the data set WORK.SCH_DEAL2. NOTE: The data set WORK.SCH_DEAL3 has 968205 observations and 40 variables. NOTE: DATA statement used (Total process time): real time 8.42 seconds user cpu time 1.81 seconds system cpu time 1.75 seconds memory 636.28k OS Memory 27744.00k Timestamp 08/24/2016 04:51:50 PM Step Count 61 Switch Count 72 438 439 proc sql; 440 create table sch_deal4 as 441 select a.*,b.sub_dealdt format=mmddyy. 442 from sch_deal2 a left join sch_deal3 b 443 on a.TITLE_LOAN_NBR=b.TITLE_LOAN_NBR 444 order BY 445 LOAN_NBR, 446 TITLE_LOAN_NBR, 447 TRANSACTION_DATE, 448 TRANSACTION_NBR; NOTE: SAS threaded sort was used. NOTE: Table WORK.SCH_DEAL4 created, with 11731980 rows and 40 columns. 24 The SAS System 16:00 Wednesday, August 24, 2016 449 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:31.93 user cpu time 36.81 seconds system cpu time 27.48 seconds memory 1054377.87k OS Memory 1080588.00k Timestamp 08/24/2016 04:53:22 PM Step Count 62 Switch Count 92 450 451 452 PROC SQL; 453 CREATE TABLE WORK.QUERY_FOR_ST_0000 AS 454 SELECT DISTINCT t1.CUSTOMER_NBR, 455 t1.LOAN_NBR, 456 t1.AVERAGE_VALUE 457 FROM eca.QF_TP_LOAN_DATA t1 458 WHERE t1.VALUE_USED NOT IS MISSING; NOTE: Table WORK.QUERY_FOR_ST_0000 created, with 343772 rows and 3 columns. 459 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 8.08 seconds user cpu time 1.39 seconds system cpu time 0.09 seconds memory 3236.96k OS Memory 31332.00k Timestamp 08/24/2016 04:53:30 PM Step Count 63 Switch Count 1424 460 461 proc sql; 462 create table book3 as 463 select a.*,b.AVERAGE_VALUE as book_value,adv/AVERAGE_VALUE as adv_over_book 464 from sch_deal4 as a left join QUERY_FOR_ST_0000 as b 465 on a.loan_nbr=b.loan_nbr 466 ORDER BY LOAN_NBR, 467 TITLE_LOAN_NBR, 468 TRANSACTION_DATE, 469 TRANSACTION_NBR 470 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.BOOK3 created, with 11732149 rows and 42 columns. 471 quit; NOTE: PROCEDURE SQL used (Total process time): real time 43.35 seconds user cpu time 20.82 seconds system cpu time 11.48 seconds memory 1050989.03k OS Memory 1077180.00k Timestamp 08/24/2016 04:54:14 PM Step Count 64 Switch Count 88 25 The SAS System 16:00 Wednesday, August 24, 2016 472 473 proc sort data=eca.qf_tp_loan_data out=schdeal; 474 by customer_nbr LOAN_NBR TITLE_LOAN_NBR; 475 run; NOTE: Sorting was performed by the data source. NOTE: There were 1415850 observations read from the data set ECA.QF_TP_LOAN_DATA. NOTE: The data set WORK.SCHDEAL has 1415850 observations and 34 variables. NOTE: PROCEDURE SORT used (Total process time): real time 1:02.05 user cpu time 31.39 seconds system cpu time 1.89 seconds memory 1570.09k OS Memory 29020.00k Timestamp 08/24/2016 04:55:16 PM Step Count 65 Switch Count 5667 476 477 data schdeal2; 478 set schdeal ; 479 by customer_nbr LOAN_NBR TITLE_LOAN_NBR; 480 if last.loan_nbr; 481 run; NOTE: There were 1415850 observations read from the data set WORK.SCHDEAL. NOTE: The data set WORK.SCHDEAL2 has 650407 observations and 34 variables. NOTE: DATA statement used (Total process time): real time 1.23 seconds user cpu time 0.37 seconds system cpu time 0.64 seconds memory 642.21k OS Memory 28256.00k Timestamp 08/24/2016 04:55:17 PM Step Count 66 Switch Count 48 482 483 PROC SQL; 484 CREATE TABLE pd AS 485 SELECT t1.* ,b.loan_status,b.due_date as Last_due 486 FROM book3 t1 left join schdeal2 b 487 on t1.LOAN_NBR=b.LOAN_NBR 488 order by t1.CUSTOMER_NBR, 489 t1.LOAN_NBR, 490 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD created, with 11732487 rows and 44 columns. 491 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 1:21.40 user cpu time 21.21 seconds system cpu time 16.46 seconds memory 1052153.21k OS Memory 1078484.00k Timestamp 08/24/2016 04:56:38 PM 26 The SAS System 16:00 Wednesday, August 24, 2016 Step Count 67 Switch Count 80 492 493 494 data pd2; 495 set pd; 496 LastDue=datepart(Last_due); 497 format LastDue mmddyy.; 498 run; NOTE: There were 11732487 observations read from the data set WORK.PD. NOTE: The data set WORK.PD2 has 11732487 observations and 45 variables. NOTE: DATA statement used (Total process time): real time 34.69 seconds user cpu time 2.23 seconds system cpu time 5.42 seconds memory 576.43k OS Memory 28512.00k Timestamp 08/24/2016 04:57:13 PM Step Count 68 Switch Count 72 499 500 501 proc sort data=pd2; 502 by customer_nbr loan_nbr num; 503 run; NOTE: There were 11732487 observations read from the data set WORK.PD2. NOTE: SAS threaded sort was used. NOTE: The data set WORK.PD2 has 11732487 observations and 45 variables. NOTE: PROCEDURE SORT used (Total process time): real time 1:09.34 user cpu time 15.61 seconds system cpu time 18.42 seconds memory 1053856.21k OS Memory 1081576.00k Timestamp 08/24/2016 04:58:22 PM Step Count 69 Switch Count 90 504 505 506 507 data dealend; 508 set pd2; 509 by customer_nbr loan_nbr num; 510 if last.loan_nbr; 511 if loan_status^='OPN' then dealenddt=trandt; 512 else dealenddt=.; 513 run; NOTE: There were 11732487 observations read from the data set WORK.PD2. NOTE: The data set WORK.DEALEND has 202774 observations and 46 variables. NOTE: DATA statement used (Total process time): real time 13.29 seconds user cpu time 1.84 seconds 27 The SAS System 16:00 Wednesday, August 24, 2016 system cpu time 1.51 seconds memory 650.06k OS Memory 28256.00k Timestamp 08/24/2016 04:58:36 PM Step Count 70 Switch Count 60 514 515 516 PROC SQL; 517 CREATE TABLE pd3 AS 518 SELECT t1.* ,b.dealenddt format=mmddyy., 519 case 520 when t1.TRANSACTION_TYPE in ('DEF','WO') then 1 521 else 0 522 end as DEF 523 FROM pd2 t1 left join dealend b 524 on t1.LOAN_NBR=b.LOAN_NBR 525 order by t1.CUSTOMER_NBR, 526 t1.LOAN_NBR, 527 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD3 created, with 11734433 rows and 47 columns. 528 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 2:36.72 user cpu time 37.64 seconds system cpu time 32.53 seconds memory 1054772.03k OS Memory 1081184.00k Timestamp 08/24/2016 05:01:13 PM Step Count 71 Switch Count 112 529 530 proc sql; 531 create table def as 532 select distinct LOAN_NBR, TITLE_LOAN_NBR, min(trandt) as default_dt format=mmddyy.,mean(def) as def 533 from pd3 534 where def=1 535 group by TITLE_LOAN_NBR 536 order by LOAN_NBR,TITLE_LOAN_NBR ; NOTE: The query requires remerging summary statistics back with the original data. NOTE: SAS threaded sort was used. NOTE: Table WORK.DEF created, with 65654 rows and 4 columns. 537 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.45 seconds user cpu time 0.79 seconds system cpu time 1.73 seconds memory 14268.43k OS Memory 40248.00k Timestamp 08/24/2016 05:01:15 PM Step Count 72 Switch Count 54 28 The SAS System 16:00 Wednesday, August 24, 2016 538 539 540 541 PROC SQL; 542 CREATE TABLE pd4 AS 543 SELECT t1.* ,b.default_dt,b.def,b.TITLE_LOAN_NBR as def_TITLE_NBR, 544 case 545 when t1.TRANSACTION_TYPE='WOB' then 1 546 else 0 547 end as bankruptcy 548 FROM pd3(drop=def) t1 left join def b 549 on t1.TITLE_LOAN_NBR=b.TITLE_LOAN_NBR 550 order by t1.LOAN_NBR, 551 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD4 created, with 11734433 rows and 50 columns. 552 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 2:31.61 user cpu time 39.62 seconds system cpu time 37.14 seconds memory 1059328.87k OS Memory 1084856.00k Timestamp 08/24/2016 05:03:47 PM Step Count 73 Switch Count 100 553 554 proc sql; 555 create table BNK as 556 select distinct customer_nbr,loan_nbr, trandt,TRANSACTION_TYPE 557 from pd4 558 where bankruptcy=1 559 order by loan_nbr, trandt; NOTE: Table WORK.BNK created, with 2035 rows and 4 columns. 560 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.09 seconds user cpu time 0.48 seconds system cpu time 1.51 seconds memory 3259.21k OS Memory 31332.00k Timestamp 08/24/2016 05:03:49 PM Step Count 74 Switch Count 46 561 562 data BNK1; 563 set BNK; 564 by loan_nbr trandt; 565 if first.loan_nbr; 566 run; NOTE: There were 2035 observations read from the data set WORK.BNK. NOTE: The data set WORK.BNK1 has 2034 observations and 4 variables. NOTE: DATA statement used (Total process time): 29 The SAS System 16:00 Wednesday, August 24, 2016 real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 567.28k OS Memory 28256.00k Timestamp 08/24/2016 05:03:49 PM Step Count 75 Switch Count 44 567 568 569 570 PROC SQL; 571 CREATE TABLE pd4_1 AS 572 SELECT distinct t1.* ,b.trandt as BNK_dt, 573 case 574 when t1.TRANSACTION_TYPE='BUY' then 1 575 else 0 576 end as Paidoff 577 FROM pd4 (drop=bankruptcy) t1 left join BNK1 b 578 on t1.LOAN_NBR=b.LOAN_NBR 579 order by t1.CUSTOMER_NBR, 580 t1.LOAN_NBR, 581 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD4_1 created, with 11733417 rows and 51 columns. 582 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 2:48.67 user cpu time 1:10.40 system cpu time 34.09 seconds memory 1056756.42k OS Memory 1082592.00k Timestamp 08/24/2016 05:06:37 PM Step Count 76 Switch Count 136 583 584 585 proc sql; 586 create table pd5 as 587 select distinct customer_nbr,loan_nbr, trandt,TRANSACTION_TYPE,dealenddt 588 from pd4_1 589 where Paidoff=1 590 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD5 created, with 126595 rows and 5 columns. 590 ! quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.65 seconds user cpu time 0.78 seconds system cpu time 1.90 seconds memory 24203.37k OS Memory 50816.00k Timestamp 08/24/2016 05:06:40 PM Step Count 77 Switch Count 46 30 The SAS System 16:00 Wednesday, August 24, 2016 591 592 PROC SQL; 593 CREATE TABLE pd4_2 AS 594 SELECT distinct t1.* , 595 case 596 when t1.dealenddt^=b.trandt and b.trandt^=. then b.trandt 597 else t1.dealenddt 598 end as lastdt 599 FROM pd4_1 t1 left join pd5 b 600 on t1.LOAN_NBR=b.LOAN_NBR 601 order by t1.CUSTOMER_NBR, 602 t1.LOAN_NBR, 603 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD4_2 created, with 11733417 rows and 52 columns. 604 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 3:20.71 user cpu time 1:28.86 system cpu time 56.42 seconds memory 1058213.53k OS Memory 1084132.00k Timestamp 08/24/2016 05:10:01 PM Step Count 78 Switch Count 106 605 606 ***************add new********; 607 608 609 PROC SQL; 610 CREATE TABLE wo AS 611 SELECT t1.* , 612 case 613 when t1.TRANSACTION_TYPE in ('WO') then 1 614 else 0 615 end as WO 616 FROM pd4_2 t1; NOTE: Table WORK.WO created, with 11733417 rows and 53 columns. 617 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 28.62 seconds user cpu time 4.37 seconds system cpu time 5.78 seconds memory 3303.21k OS Memory 31332.00k Timestamp 08/24/2016 05:10:29 PM Step Count 79 Switch Count 94 618 619 620 proc sql; 621 create table wo2 as 31 The SAS System 16:00 Wednesday, August 24, 2016 622 select distinct loan_nbr, TITLE_LOAN_NBR, min(trandt) as wo_dt format=mmddyy.,mean(wo) as WO 623 from wo 624 where wo=1 625 group by LOAN_NBR 626 order by loan_nbr; NOTE: The query requires remerging summary statistics back with the original data. NOTE: SAS threaded sort was used. NOTE: Table WORK.WO2 created, with 40441 rows and 4 columns. 627 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.79 seconds user cpu time 0.81 seconds system cpu time 2.00 seconds memory 8737.15k OS Memory 34716.00k Timestamp 08/24/2016 05:10:32 PM Step Count 80 Switch Count 66 628 629 630 PROC SQL; 631 CREATE TABLE ALLL AS 632 SELECT t1.* ,b.wo_dt,b.WO 633 FROM pd4_2 t1 left join WO2 b 634 on t1.LOAN_NBR=b.LOAN_NBR 635 order by t1.loan_nbr, 636 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.ALLL created, with 11733427 rows and 54 columns. 637 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 2:31.49 user cpu time 36.71 seconds system cpu time 38.37 seconds memory 1062977.15k OS Memory 1088780.00k Timestamp 08/24/2016 05:13:04 PM Step Count 81 Switch Count 84 638 639 data sold; 640 set ALLL; 641 if TRANSACTION_TYPE='SLD'; 642 run; NOTE: There were 11733427 observations read from the data set WORK.ALLL. NOTE: The data set WORK.SOLD has 4761 observations and 54 variables. NOTE: DATA statement used (Total process time): real time 3.73 seconds user cpu time 0.89 seconds system cpu time 1.76 seconds memory 604.28k OS Memory 28256.00k Timestamp 08/24/2016 05:13:07 PM 32 The SAS System 16:00 Wednesday, August 24, 2016 Step Count 82 Switch Count 36 643 644 data repo; 645 set ALLL; 646 if TRANSACTION_TYPE='REPO'; 647 run; NOTE: There were 11733427 observations read from the data set WORK.ALLL. NOTE: The data set WORK.REPO has 5433 observations and 54 variables. NOTE: DATA statement used (Total process time): real time 2.48 seconds user cpu time 0.87 seconds system cpu time 1.60 seconds memory 604.37k OS Memory 28256.00k Timestamp 08/24/2016 05:13:10 PM Step Count 83 Switch Count 44 648 649 proc sql; 650 create table ALLL2 as 651 select a.*,b.cashin as Sold,c.cashout as Repo_cost 652 from ALLL a left join sold b 653 on a.loan_nbr=b.loan_nbr and a.trandt=b.trandt and a.num=b.num left join repo c 654 on a.loan_nbr=c.loan_nbr and a.trandt=c.trandt and a.num=c.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.ALLL2 created, with 11733431 rows and 56 columns. 655 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1:53.67 user cpu time 26.45 seconds system cpu time 25.53 seconds memory 1051428.25k OS Memory 1077352.00k Timestamp 08/24/2016 05:15:04 PM Step Count 84 Switch Count 80 656 657 data ALLL2; 658 set ALLL2; 659 if transaction_type='WO' and state_code='VA' then BAL_PRINCIPAL_AMT=0; 660 if transaction_type='WO' and state_code='VA' then BAL_INT_AMOUNT=0; 661 if transaction_type='WO' and state_code='VA' then principal_paid=0; 662 if transaction_type='WO' and state_code='VA' then interest=0; 663 if transaction_type='WO' and state_code='VA' then Repo_cost=0; 664 run; NOTE: There were 11733431 observations read from the data set WORK.ALLL2. NOTE: The data set WORK.ALLL2 has 11733431 observations and 56 variables. NOTE: DATA statement used (Total process time): real time 36.51 seconds user cpu time 2.00 seconds system cpu time 7.26 seconds 33 The SAS System 16:00 Wednesday, August 24, 2016 memory 618.00k OS Memory 28768.00k Timestamp 08/24/2016 05:15:40 PM Step Count 85 Switch Count 80 665 666 proc sort data= ALLL2; 667 by loan_nbr num; 668 run; NOTE: There were 11733431 observations read from the data set WORK.ALLL2. NOTE: SAS threaded sort was used. NOTE: The data set WORK.ALLL2 has 11733431 observations and 56 variables. NOTE: PROCEDURE SORT used (Total process time): real time 46.79 seconds user cpu time 15.39 seconds system cpu time 16.48 seconds memory 1056551.39k OS Memory 1084480.00k Timestamp 08/24/2016 05:16:27 PM Step Count 86 Switch Count 90 669 670 data ALLL3; 671 set ALLL2; 672 by loan_nbr ; 673 if BAL_PRINCIPAL_AMT=. then BAL_PRINCIPAL_AMT=0; 674 /* if first.loan_nbr then Prin_bal=0;*/ 675 676 if first.loan_nbr then Prin_bal=BAL_PRINCIPAL_AMT; 677 else Prin_bal+BAL_PRINCIPAL_AMT; 678 679 if first.loan_nbr then Interest_bal=BAL_INT_AMOUNT; 680 else Interest_bal+BAL_INT_AMOUNT; 681 682 if first.loan_nbr then repo_bal=REPO_CHARGE; 683 else repo_bal+REPO_CHARGE; 684 run; NOTE: There were 11733431 observations read from the data set WORK.ALLL2. NOTE: The data set WORK.ALLL3 has 11733431 observations and 59 variables. NOTE: DATA statement used (Total process time): real time 28.34 seconds user cpu time 2.57 seconds system cpu time 5.82 seconds memory 677.12k OS Memory 28768.00k Timestamp 08/24/2016 05:16:55 PM Step Count 87 Switch Count 112 685 686 687 data interest_paid; 688 set ALLL3; 689 if TRANSACTION_TYPE ^='SLD'; 34 The SAS System 16:00 Wednesday, August 24, 2016 690 run; NOTE: There were 11733431 observations read from the data set WORK.ALLL3. NOTE: The data set WORK.INTEREST_PAID has 11728668 observations and 59 variables. NOTE: DATA statement used (Total process time): real time 22.15 seconds user cpu time 1.95 seconds system cpu time 6.04 seconds memory 617.53k OS Memory 28768.00k Timestamp 08/24/2016 05:17:17 PM Step Count 88 Switch Count 64 691 692 proc sql; 693 create table ALLL3 as 694 select a.*, 695 case 696 when b.interest=. then 0 697 else -1*b.interest 698 end as interest_cashin, 699 case 700 when b.principal_paid=. then 0 701 else -1*b.principal_paid 702 end as prin_paid 703 704 from ALLL3 a left join interest_paid b 705 on a.loan_nbr=b.loan_nbr and a.num=b.num; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table WORK.ALLL3 created, with 11736999 rows and 61 columns. 706 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2:16.09 user cpu time 40.61 seconds system cpu time 38.10 seconds memory 1054629.84k OS Memory 1080864.00k Timestamp 08/24/2016 05:19:34 PM Step Count 89 Switch Count 148 707 708 709 data tt.state4_ALLL; 710 set ALLL3; 711 if repo_bal=. then repo_bal=0; 712 if repo_cost=. then repo_cost=0; 713 format lastdt mmddyy.; 714 run; NOTE: There were 11736999 observations read from the data set WORK.ALLL3. NOTE: The data set TT.STATE4_ALLL has 11736999 observations and 61 variables. NOTE: DATA statement used (Total process time): real time 1:47.49 35 The SAS System 16:00 Wednesday, August 24, 2016 user cpu time 3.51 seconds system cpu time 9.32 seconds memory 619.03k OS Memory 28768.00k Timestamp 08/24/2016 05:21:21 PM Step Count 90 Switch Count 175 715 716 717 proc sort data=tt.state4_ALLL; 718 by loan_nbr num; 719 run; NOTE: There were 11736999 observations read from the data set TT.STATE4_ALLL. NOTE: SAS threaded sort was used. NOTE: The data set TT.STATE4_ALLL has 11736999 observations and 61 variables. NOTE: PROCEDURE SORT used (Total process time): real time 3:13.80 user cpu time 19.04 seconds system cpu time 24.00 seconds memory 1054472.67k OS Memory 1082396.00k Timestamp 08/24/2016 05:24:35 PM Step Count 91 Switch Count 289 720 721 data last; 722 set tt.state4_ALLL; 723 by loan_nbr num; 724 if last.loan_nbr; 725 if TRANSACTION_TYPE in ('DEF', 726 'DFP', 727 'REPO', 728 'RPY', 729 'SLD') then lastdt=.; 730 run; NOTE: There were 11736999 observations read from the data set TT.STATE4_ALLL. NOTE: The data set WORK.LAST has 202763 observations and 61 variables. NOTE: DATA statement used (Total process time): real time 1:01.30 user cpu time 3.95 seconds system cpu time 2.43 seconds memory 675.34k OS Memory 28768.00k Timestamp 08/24/2016 05:25:36 PM Step Count 92 Switch Count 148 731 732 PROC SQL; 733 CREATE TABLE tt.state4_ALLL AS 734 SELECT distinct t1.* ,b.lastdt format=mmddyy. 735 FROM tt.state4_ALLL (drop=lastdt) t1 left join last b 736 on t1.LOAN_NBR=b.LOAN_NBR 737 order by t1.CUSTOMER_NBR, 36 The SAS System 16:00 Wednesday, August 24, 2016 738 t1.LOAN_NBR, 739 t1.num; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table TT.STATE4_ALLL created, with 11733426 rows and 61 columns. 740 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 4:32.08 user cpu time 1:26.39 system cpu time 41.68 seconds memory 1057215.34k OS Memory 1083044.00k Timestamp 08/24/2016 05:30:08 PM Step Count 93 Switch Count 196 741 742 *****************LA Below; 743 data LA; 744 set tt.tran; 745 where state_code in ( 'LA'); 746 run; NOTE: There were 1904170 observations read from the data set TT.TRAN. WHERE state_code='LA'; NOTE: The data set WORK.LA has 1904170 observations and 29 variables. NOTE: DATA statement used (Total process time): real time 47.98 seconds user cpu time 1.87 seconds system cpu time 2.45 seconds memory 533.75k OS Memory 28768.00k Timestamp 08/24/2016 05:30:56 PM Step Count 94 Switch Count 110 747 748 proc sort data= LA; 749 BY 750 LOAN_NBR 751 TITLE_LOAN_NBR 752 TRANSACTION_DATE 753 TRANSACTION_NBR; 754 run; NOTE: There were 1904170 observations read from the data set WORK.LA. NOTE: SAS threaded sort was used. NOTE: The data set WORK.LA has 1904170 observations and 29 variables. NOTE: PROCEDURE SORT used (Total process time): real time 1.75 seconds user cpu time 2.42 seconds system cpu time 1.45 seconds memory 766838.00k OS Memory 794840.00k Timestamp 08/24/2016 05:30:58 PM Step Count 95 Switch Count 50 37 The SAS System 16:00 Wednesday, August 24, 2016 755 756 proc sql; 757 create table ssn as 758 select s.*, e.ssn 759 from la s 760 left join eca.QF_CUSTOMER_DETAILS e 761 on s.customer_nbr=e.customer_nbr; NOTE: SAS threaded sort was used. NOTE: Table WORK.SSN created, with 1904170 rows and 30 columns. 762 quit; NOTE: PROCEDURE SQL used (Total process time): real time 13.77 seconds user cpu time 5.20 seconds system cpu time 2.87 seconds memory 731733.82k OS Memory 758420.00k Timestamp 08/24/2016 05:31:12 PM Step Count 96 Switch Count 152 763 764 proc sql; 765 create table loan as 766 select l.*, b.state_code 767 from eca.qf_tp_loan_data l, eca.qf_branch_data b 768 where l.branch_nbr=b.branch_nbr; NOTE: Table WORK.LOAN created, with 1415850 rows and 35 columns. 769 quit; NOTE: PROCEDURE SQL used (Total process time): real time 47.66 seconds user cpu time 31.29 seconds system cpu time 1.37 seconds memory 3414.96k OS Memory 31844.00k Timestamp 08/24/2016 05:31:59 PM Step Count 97 Switch Count 5665 770 771 proc sql; 772 create table ssn2 as 773 select distinct a.*,b.* 774 from ssn a left join loan b 775 on a.loan_nbr=b.loan_nbr; WARNING: Variable LOAN_NBR already exists on file WORK.SSN2. WARNING: Variable CUSTOMER_NBR already exists on file WORK.SSN2. WARNING: Variable BRANCH_NBR already exists on file WORK.SSN2. WARNING: Variable TITLE_LOAN_NBR already exists on file WORK.SSN2. WARNING: Variable DATE_CREATED already exists on file WORK.SSN2. WARNING: Variable CREATED_BY already exists on file WORK.SSN2. WARNING: Variable CREATE_DATE_TIME already exists on file WORK.SSN2. WARNING: Variable UPDATE_DATE_TIME already exists on file WORK.SSN2. WARNING: Variable CREATE_USER_NM already exists on file WORK.SSN2. WARNING: Variable UPDATE_USER_NM already exists on file WORK.SSN2. 38 The SAS System 16:00 Wednesday, August 24, 2016 WARNING: Variable CREATE_PROGRAM_NM already exists on file WORK.SSN2. WARNING: Variable UPDATE_PROGRAM_NM already exists on file WORK.SSN2. WARNING: Variable STATE_CODE already exists on file WORK.SSN2. NOTE: SAS threaded sort was used. NOTE: Table WORK.SSN2 created, with 1904170 rows and 52 columns. 776 quit; NOTE: PROCEDURE SQL used (Total process time): real time 48.51 seconds user cpu time 18.40 seconds system cpu time 15.40 seconds memory 1056887.09k OS Memory 1082736.00k Timestamp 08/24/2016 05:32:48 PM Step Count 98 Switch Count 82 777 778 779 780 data ssn3; 781 set ssn2; 782 783 if PREV_TITLE_LOAN_NBR =. then 784 flg=1; 785 else flg=0; 786 run; NOTE: There were 1904170 observations read from the data set WORK.SSN2. NOTE: The data set WORK.SSN3 has 1904170 observations and 53 variables. NOTE: DATA statement used (Total process time): real time 8.44 seconds user cpu time 0.34 seconds system cpu time 1.67 seconds memory 610.28k OS Memory 28768.00k Timestamp 08/24/2016 05:32:56 PM Step Count 99 Switch Count 56 787 788 proc sql; 789 create table ssn6 as 790 select * 791 from ssn3 792 where flg=1 793 order by ssn,TITLE_LOAN_NBR,transaction_nbr; NOTE: SAS threaded sort was used. NOTE: Table WORK.SSN6 created, with 338907 rows and 53 columns. 794 quit; NOTE: PROCEDURE SQL used (Total process time): real time 1.04 seconds user cpu time 0.78 seconds system cpu time 0.65 seconds memory 239633.87k OS Memory 266784.00k Timestamp 08/24/2016 05:32:57 PM 39 The SAS System 16:00 Wednesday, August 24, 2016 Step Count 100 Switch Count 48 795 796 data first; 797 set ssn6; 798 by ssn TITLE_LOAN_NBR TRANSACTION_NBR 799 ; 800 if first.TITLE_LOAN_NBR; 801 run; NOTE: There were 338907 observations read from the data set WORK.SSN6. NOTE: The data set WORK.FIRST has 77518 observations and 53 variables. NOTE: DATA statement used (Total process time): real time 0.29 seconds user cpu time 0.09 seconds system cpu time 0.20 seconds memory 668.75k OS Memory 28768.00k Timestamp 08/24/2016 05:32:58 PM Step Count 101 Switch Count 44 802 803 804 proc sql; 805 create table ssn3 as 806 select a.*, 807 case 808 when b.flg=. then 0 809 else 1 810 end as flg 811 from ssn3(drop=flg) a left join first b 812 on a.TRANSACTION_NBR=b.TRANSACTION_NBR 813 order by a.ssn,a.TITLE_LOAN_NBR,a.transaction_nbr; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table WORK.SSN3 created, with 1904170 rows and 53 columns. 814 quit; NOTE: PROCEDURE SQL used (Total process time): real time 23.70 seconds user cpu time 9.35 seconds system cpu time 8.67 seconds memory 1052333.23k OS Memory 1078544.00k Timestamp 08/24/2016 05:33:21 PM Step Count 102 Switch Count 76 815 816 proc sql; 817 create table la as 818 select a.*, b.MAKE,b.YEAR_,b.vin 819 from ssn3 a left join eca.QF_TP_VEHICLE_DETAILS b 820 on a.loan_nbr=b.loan_nbr 821 order by ssn ,vin, TITLE_LOAN_NBR, transaction_nbr; 40 The SAS System 16:00 Wednesday, August 24, 2016 NOTE: SAS threaded sort was used. NOTE: Table WORK.LA created, with 1904170 rows and 56 columns. 822 quit; NOTE: PROCEDURE SQL used (Total process time): real time 14.82 seconds user cpu time 13.20 seconds system cpu time 8.07 seconds memory 1053576.43k OS Memory 1079864.00k Timestamp 08/24/2016 05:33:36 PM Step Count 103 Switch Count 423 823 824 825 data ssn4; 826 set la; 827 by ssn vin TITLE_LOAN_NBR transaction_nbr; 828 grp+flg; 829 run; NOTE: There were 1904170 observations read from the data set WORK.LA. NOTE: The data set WORK.SSN4 has 1904170 observations and 57 variables. NOTE: DATA statement used (Total process time): real time 1.98 seconds user cpu time 0.75 seconds system cpu time 1.23 seconds memory 683.56k OS Memory 28768.00k Timestamp 08/24/2016 05:33:38 PM Step Count 104 Switch Count 40 830 831 proc sort data=ssn4; 832 by grp loan_date TITLE_LOAN_NBR transaction_nbr; 833 run; NOTE: There were 1904170 observations read from the data set WORK.SSN4. NOTE: SAS threaded sort was used. NOTE: The data set WORK.SSN4 has 1904170 observations and 57 variables. NOTE: PROCEDURE SORT used (Total process time): real time 5.29 seconds user cpu time 3.25 seconds system cpu time 4.18 seconds memory 1055021.95k OS Memory 1082948.00k Timestamp 08/24/2016 05:33:44 PM Step Count 105 Switch Count 42 834 835 data ssn5; 836 set ssn4; 837 by grp loan_date; 838 839 if first.grp then 41 The SAS System 16:00 Wednesday, August 24, 2016 840 num=0; 841 num+1; 842 retain dealnbr; 843 if first.grp then dealnbr=loan_nbr; 844 run; NOTE: There were 1904170 observations read from the data set WORK.SSN4. NOTE: The data set WORK.SSN5 has 1904170 observations and 59 variables. NOTE: DATA statement used (Total process time): real time 6.50 seconds user cpu time 0.68 seconds system cpu time 1.78 seconds memory 681.90k OS Memory 28768.00k Timestamp 08/24/2016 05:33:50 PM Step Count 106 Switch Count 72 845 846 data tt; 847 set ssn5(rename=(grp=deal_num)); 848 849 if BAL_INT_AMOUNT >=0 or BAL_INT_AMOUNT=. then 850 interest=0; 851 else interest=BAL_INT_AMOUNT; 852 853 if BAL_LIEN_FEE_AMT >=0 or BAL_LIEN_FEE_AMT=. then 854 lien=0; 855 else lien=BAL_LIEN_FEE_AMT; 856 857 if LATE_FEE >=0 or LATE_FEE=. then 858 late=0; 859 else late=LATE_FEE; 860 861 if OTHER_FEE >=0 or OTHER_FEE=. then 862 other=0; 863 else other= OTHER_FEE; 864 865 if REPO_CHARGE >=0 or REPO_CHARGE=. then 866 repo=0; 867 else repo=REPO_CHARGE; 868 869 if STORAGE_COST >=0 or STORAGE_COST=. then 870 storage=0; 871 else storage=STORAGE_COST; 872 873 if BAL_PRINCIPAL_AMT>=0 or BAL_PRINCIPAL_AMT=. then 874 principal_paid=0; 875 else principal_paid=BAL_PRINCIPAL_AMT; 876 877 if BAL_PRINCIPAL_AMT>=0 or BAL_PRINCIPAL_AMT=. then 878 princ_cashout=BAL_PRINCIPAL_AMT; 879 else princ_cashout=0; 880 881 if REPO_CHARGE >=0 then 882 repo_cashout=REPO_CHARGE; 883 else repo_cashout=0; 884 42 The SAS System 16:00 Wednesday, August 24, 2016 885 if STORAGE_COST >=0 then 886 stoc_cashout=STORAGE_COST; 887 else stoc_cashout=0; 888 889 if OTHER_FEE>=0 then 890 repo2_cashout=OTHER_FEE; 891 else repo2_cashout=0; 892 run; NOTE: There were 1904170 observations read from the data set WORK.SSN5. NOTE: The data set WORK.TT has 1904170 observations and 70 variables. NOTE: DATA statement used (Total process time): real time 9.95 seconds user cpu time 0.54 seconds system cpu time 2.14 seconds memory 666.96k OS Memory 28768.00k Timestamp 08/24/2016 05:34:00 PM Step Count 107 Switch Count 220 893 894 proc sort data= tt; 895 BY deal_num 896 897 TRANSACTION_NBR; 898 run; NOTE: There were 1904170 observations read from the data set WORK.TT. NOTE: SAS threaded sort was used. NOTE: The data set WORK.TT has 1904170 observations and 70 variables. NOTE: PROCEDURE SORT used (Total process time): real time 17.37 seconds user cpu time 3.23 seconds system cpu time 4.57 seconds memory 1051970.98k OS Memory 1079864.00k Timestamp 08/24/2016 05:34:17 PM Step Count 108 Switch Count 50 899 900 data principal; 901 set tt; 902 BY deal_num 903 TRANSACTION_NBR; 904 905 if first.deal_num; 906 principal= BAL_PRINCIPAL_AMT; 907 run; NOTE: There were 1904170 observations read from the data set WORK.TT. NOTE: The data set WORK.PRINCIPAL has 77518 observations and 71 variables. NOTE: DATA statement used (Total process time): real time 1.01 seconds user cpu time 0.37 seconds system cpu time 0.62 seconds memory 708.28k 43 The SAS System 16:00 Wednesday, August 24, 2016 OS Memory 28768.00k Timestamp 08/24/2016 05:34:18 PM Step Count 109 Switch Count 56 908 909 data opendt; 910 set tt; 911 BY deal_num 912 913 TRANSACTION_NBR; 914 915 if first.deal_num; 916 trandt=datepart(loan_date); 917 run; NOTE: There were 1904170 observations read from the data set WORK.TT. NOTE: The data set WORK.OPENDT has 77518 observations and 71 variables. NOTE: DATA statement used (Total process time): real time 1.06 seconds user cpu time 0.32 seconds system cpu time 0.64 seconds memory 711.21k OS Memory 28768.00k Timestamp 08/24/2016 05:34:20 PM Step Count 110 Switch Count 56 918 /**/ 919 /*proc sql;*/ 920 /* create table t_t as*/ 921 /* select a.*, */ 922 /* case */ 923 /* when b.principal=. then 0*/ 924 /* else b.principal*/ 925 /* end */ 926 /* as princ_cashout*/ 927 /* from tt a left join principal b*/ 928 /* on a.loan_nbr=b.loan_nbr and a.transaction_nbr=b.transaction_nbr and a.transaction_date=b.transaction_date;*/ 929 /*quit;*/ 930 931 proc sql; 932 create table t_t3 as 933 select a.*,b.principal as adv 934 from tt a left join principal b 935 on a.deal_num=b.deal_num; NOTE: SAS threaded sort was used. NOTE: Table WORK.T_T3 created, with 1904170 rows and 71 columns. 936 quit; NOTE: PROCEDURE SQL used (Total process time): real time 5.11 seconds user cpu time 1.04 seconds system cpu time 2.37 seconds memory 7846.18k OS Memory 33700.00k Timestamp 08/24/2016 05:34:25 PM Step Count 111 Switch Count 52 44 The SAS System 16:00 Wednesday, August 24, 2016 937 938 proc sql; 939 create table t_t2 as 940 select a.*,b.trandt as open_dt format=mmddyy. 941 from t_t3 a left join opendt b 942 on a.deal_num=b.deal_num; NOTE: SAS threaded sort was used. NOTE: Table WORK.T_T2 created, with 1904170 rows and 72 columns. 943 quit; NOTE: PROCEDURE SQL used (Total process time): real time 10.88 seconds user cpu time 1.31 seconds system cpu time 2.01 seconds memory 7518.48k OS Memory 33444.00k Timestamp 08/24/2016 05:34:36 PM Step Count 112 Switch Count 52 944 945 proc sql; 946 create table tt2 as 947 select *,-1*sum(interest,lien,late,other,repo,storage,principal_paid) as cashin, 948 sum( repo_cashout,stoc_cashout, princ_cashout,repo2_cashout) as cashout, 949 calculated cashin-calculated cashout as net_cf 950 951 from t_t2 952 group by loan_nbr 953 order BY deal_num, 954 LOAN_NBR, 955 TITLE_LOAN_NBR, 956 TRANSACTION_DATE, 957 TRANSACTION_NBR; NOTE: A GROUP BY clause has been discarded because neither the SELECT clause nor the optional HAVING clause of the associated table-expression referenced a summary function. NOTE: SAS threaded sort was used. NOTE: Table WORK.TT2 created, with 1904170 rows and 75 columns. 958 quit; NOTE: PROCEDURE SQL used (Total process time): real time 24.13 seconds user cpu time 5.17 seconds system cpu time 6.71 seconds memory 1048945.50k OS Memory 1075764.00k Timestamp 08/24/2016 05:35:00 PM Step Count 113 Switch Count 96 959 960 data tt3; 961 set tt2; 962 BY deal_num 963 LOAN_NBR 964 TITLE_LOAN_NBR 45 The SAS System 16:00 Wednesday, August 24, 2016 965 TRANSACTION_DATE 966 TRANSACTION_NBR; 967 968 if first.deal_num then 969 no=0; 970 no+1; 971 trandt=datepart(transaction_date); 972 format trandt mmddyy.; 973 run; NOTE: There were 1904170 observations read from the data set WORK.TT2. NOTE: The data set WORK.TT3 has 1904170 observations and 77 variables. NOTE: DATA statement used (Total process time): real time 10.20 seconds user cpu time 0.79 seconds system cpu time 1.40 seconds memory 833.78k OS Memory 28512.00k Timestamp 08/24/2016 05:35:10 PM Step Count 114 Switch Count 88 974 975 proc sql; 976 create table default as 977 select deal_num, transaction_type,no, TITLE_LOAN_NBR, 978 TRANSACTION_DATE, 979 TRANSACTION_NBR 980 from tt3 981 where transaction_type in ('DEF','REPO','STOC','AGND','WO','WOB','SLD','DSLD','DFP','RPO','SET') 982 order by deal_num,no 983 ; NOTE: SAS threaded sort was used. NOTE: Table WORK.DEFAULT created, with 72304 rows and 6 columns. 984 quit; NOTE: PROCEDURE SQL used (Total process time): real time 3.07 seconds user cpu time 0.31 seconds system cpu time 0.59 seconds memory 12325.40k OS Memory 39256.00k Timestamp 08/24/2016 05:35:13 PM Step Count 115 Switch Count 60 985 986 data recover; 987 set default; 988 by deal_num no; 989 990 if last.deal_num; 991 run; NOTE: There were 72304 observations read from the data set WORK.DEFAULT. NOTE: The data set WORK.RECOVER has 28720 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.20 seconds 46 The SAS System 16:00 Wednesday, August 24, 2016 user cpu time 0.03 seconds system cpu time 0.01 seconds memory 568.68k OS Memory 28512.00k Timestamp 08/24/2016 05:35:13 PM Step Count 116 Switch Count 48 992 993 data recover2; 994 set recover; 995 where transaction_type not in('AGND','DFP','RPO'); 996 bad=1;; 997 run; NOTE: There were 24757 observations read from the data set WORK.RECOVER. WHERE transaction_type not in ('AGND', 'DFP', 'RPO'); NOTE: The data set WORK.RECOVER2 has 24757 observations and 7 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 512.43k OS Memory 28512.00k Timestamp 08/24/2016 05:35:13 PM Step Count 117 Switch Count 42 998 999 proc sql; 1000 create table tt4 as 1001 select a.*, 1002 case 1003 when b.bad=. then 1 1004 else 0 1005 end 1006 as good 1007 from tt3 a left join recover2 b 1008 on a.deal_num=b.deal_num 1009 order by deal_num, 1010 no; NOTE: SAS threaded sort was used. NOTE: Table WORK.TT4 created, with 1904170 rows and 78 columns. 1011 quit; NOTE: PROCEDURE SQL used (Total process time): real time 41.05 seconds user cpu time 8.89 seconds system cpu time 13.60 seconds memory 1058333.15k OS Memory 1084240.00k Timestamp 08/24/2016 05:35:54 PM Step Count 118 Switch Count 80 1012 1013 data la2; 1014 set tt4; 47 The SAS System 16:00 Wednesday, August 24, 2016 1015 /* where open_dt>='01mar2012'd and open_dt<='09apr2014'd-90;*/ 1016 1017 if transaction_type='REPO' then 1018 flg_repo=1; 1019 else flg_repo=0; 1020 run; NOTE: There were 1904170 observations read from the data set WORK.TT4. NOTE: The data set WORK.LA2 has 1904170 observations and 79 variables. NOTE: DATA statement used (Total process time): real time 9.20 seconds user cpu time 0.56 seconds system cpu time 1.93 seconds memory 770.75k OS Memory 28768.00k Timestamp 08/24/2016 05:36:03 PM Step Count 119 Switch Count 64 1021 1022 PROC SQL; 1023 CREATE TABLE void AS 1024 SELECT transaction_nbr,REF_TRAN_CODE 1025 1026 FROM la2 t1 1027 WHERE t1.VOID_FLAG = 'V'; NOTE: Table WORK.VOID created, with 6618 rows and 2 columns. 1028 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 0.98 seconds user cpu time 0.28 seconds system cpu time 0.68 seconds memory 3347.40k OS Memory 31844.00k Timestamp 08/24/2016 05:36:04 PM Step Count 120 Switch Count 46 1029 1030 proc sql; 1031 create table la3 as 1032 select s.* 1033 from la2 s 1034 where transaction_nbr not in (select transaction_nbr from void) and transaction_nbr not in (select ref_tran_code 1034 ! from void); NOTE: Table WORK.LA3 created, with 1890934 rows and 79 columns. 1035 quit; NOTE: PROCEDURE SQL used (Total process time): real time 12.50 seconds user cpu time 2.09 seconds system cpu time 2.36 seconds memory 3560.12k OS Memory 31844.00k Timestamp 08/24/2016 05:36:17 PM Step Count 121 Switch Count 60 48 The SAS System 16:00 Wednesday, August 24, 2016 1036 1037 1038 /*compare AA and ECA*/ 1039 proc sql; 1040 create table eca_state_loan1 as 1041 select s.*, e.AACherwell, 1042 case 1043 when e.AACherwell=. then 'AA' 1044 else 'ECA' 1045 end 1046 as company 1047 from la3 s 1048 left join ecactr.DATA_IMPORTED_FROM_ECA_QFUND_TO_ e 1049 on s.branch_nbr=e.ECAQFund; NOTE: SAS threaded sort was used. NOTE: Table WORK.ECA_STATE_LOAN1 created, with 1890934 rows and 81 columns. 1050 quit; NOTE: PROCEDURE SQL used (Total process time): real time 22.76 seconds user cpu time 4.56 seconds system cpu time 9.01 seconds memory 1049161.01k OS Memory 1075736.00k Timestamp 08/24/2016 05:36:40 PM Step Count 122 Switch Count 68 1051 1052 proc sql; 1053 create table tt8 as 1054 select s.*, 1055 case 1056 when e.CLOSED_DATE=. then 'open' 1057 else 'close' 1058 end 1059 as close 1060 from eca_state_loan1 s 1061 left join eca.qf_branch_data e 1062 on s.branch_nbr =e.branch_nbr; NOTE: Table WORK.TT8 created, with 1890934 rows and 82 columns. 1063 quit; NOTE: PROCEDURE SQL used (Total process time): real time 10.78 seconds user cpu time 1.01 seconds system cpu time 1.62 seconds memory 3593.00k OS Memory 31844.00k Timestamp 08/24/2016 05:36:51 PM Step Count 123 Switch Count 76 1064 1065 data store; 1066 set ECA.TBLSTOREINFO; 1067 open_dt=datepart(OPENDATE); 49 The SAS System 16:00 Wednesday, August 24, 2016 1068 storenbr=input(store,best.); 1069 run; NOTE: Invalid argument to function INPUT at line 1068 column 11. REGION=1 DIVISION=1C STORE=107A STORENAME=107A - GREER OPENDATE=25MAR2004:00:00:00 OPENCLOSED=0 CLOSEDATE=01DEC2006:00:00:00 STORETYPE=CA STATE=SC PAYDAYLOANSTARTDATE=. PAYDAYLOANENDDATE=. SLOCSTARTDATE=. SLOCENDDATE=. SLOC2STARTDATE=. SLOC2ENDDATE=. ULOCSTARTDATE=. ULOCENDDATE=. TITLEPLEDGESTARTDATE=. TITLEPLEDGEENDDATE=. CHECKCASHINGSTARTDATE=. CHECKCASHINGENDDATE=. BILLPAYSTARTDATE=. BILLPAYENDDATE=. PREPAIDHOMEPHONESTARTDATE=. PREPAIDHOMEPHONEENDDATE=. WIRETRANSFERSTARTDATE=. WIRETRANSFERENDDATE=. PREPAIDDEBITSTARTDATE=. PREPAIDDEBITENDDATE=. EXTENDEDPAYMENTPLANSTARTDATE=. EXTENDEDPAYMENTPLANENDDATE=. PREPAIDCELLPHONESTARTDATE=. PREPAIDCELLPHONEENDDATE=. GOLDBUYSTARTDATE=. GOLDBUYENDDATE=. INSTALLMENTLOANSTARTDATE=. INSTALLMENTLOANENDDATE=. CREATE_DATE_TIME=13SEP2013:15:09:04 UPDATE_DATE_TIME=. CREATE_USER_NM=SVC_DSUSER UPDATE_USER_NM= CREATE_PROGRAM_NM=TBLSTOREINFO_L_History UPDATE_PROGRAM_NM= open_dt=16155 storenbr=. _ERROR_=1 _N_=17 NOTE: Missing values were generated as a result of performing an operation on missing values. Each place is given by: (Number of times) at (Line):(Column). 5 at 1067:10 NOTE: Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values. Each place is given by: (Number of times) at (Line):(Column). 1 at 1068:11 NOTE: There were 720 observations read from the data set ECA.TBLSTOREINFO. NOTE: The data set WORK.STORE has 720 observations and 45 variables. NOTE: DATA statement used (Total process time): real time 0.04 seconds user cpu time 0.04 seconds system cpu time 0.00 seconds memory 1786.09k OS Memory 29624.00k Timestamp 08/24/2016 05:36:51 PM Step Count 124 Switch Count 79 1070 1071 proc sql; 1072 create table tt9 as 1073 select s.*,e.open_dt as storeopen format=mmddyy. 1074 1075 from tt8 s 1076 left join store e 1077 on s.branch_nbr =e.storenbr 1078 ; NOTE: Table WORK.TT9 created, with 1890934 rows and 83 columns. 1079 quit; NOTE: PROCEDURE SQL used (Total process time): real time 11.32 seconds user cpu time 1.18 seconds system cpu time 2.68 seconds memory 3620.00k OS Memory 31844.00k Timestamp 08/24/2016 05:37:02 PM Step Count 125 Switch Count 62 1080 1081 PROC SQL; 1082 CREATE TABLE tt.la4 AS 1083 SELECT t1.* 1084 FROM WORK.TT9 t1 50 The SAS System 16:00 Wednesday, August 24, 2016 1085 WHERE t1.close = 'open' 1086 1087 ORDER BY deal_num, num; NOTE: SAS threaded sort was used. NOTE: Table TT.LA4 created, with 1890934 rows and 83 columns. 1088 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 45.76 seconds user cpu time 5.46 seconds system cpu time 8.95 seconds memory 1049624.32k OS Memory 1076428.00k Timestamp 08/24/2016 05:37:48 PM Step Count 126 Switch Count 63 1089 1090 1091 data tt.la4; 1092 set tt.la4; 1093 retain dealnbr; 1094 if num=1 then dealnbr=loan_nbr; 1095 run; NOTE: There were 1890934 observations read from the data set TT.LA4. NOTE: The data set TT.LA4 has 1890934 observations and 83 variables. NOTE: DATA statement used (Total process time): real time 46.01 seconds user cpu time 0.95 seconds system cpu time 2.54 seconds memory 704.28k OS Memory 28512.00k Timestamp 08/24/2016 05:38:34 PM Step Count 127 Switch Count 95 1096 1097 1098 1099 PROC SQL; 1100 CREATE TABLE WORK.FILTER AS 1101 SELECT t1.LOAN_NBR, 1102 t1.BRANCH_NBR, 1103 t1.CUSTOMER_NBR, 1104 t1.TRANSACTION_TYPE, 1105 t1.TITLE_LOAN_NBR, 1106 t1.STATE_CODE, 1107 TOTAL_AMOUNT_DUE, 1108 t1.SSN, 1109 t1.LOAN_DATE, 1110 t1.DUE_DATE, 1111 t1.LOAN_STATUS, 1112 t1.TITLE_STATUS, 1113 t1.dealnbr, 1114 t1.num, 1115 t1.interest, 1116 t1.lien, 51 The SAS System 16:00 Wednesday, August 24, 2016 1117 t1.late, 1118 t1.other, 1119 t1.repo, 1120 t1.storage, 1121 t1.principal_paid, 1122 t1.princ_cashout, 1123 t1.repo_cashout, 1124 t1.stoc_cashout, 1125 t1.repo2_cashout, 1126 t1.adv, 1127 t1.open_dt, 1128 t1.cashin, 1129 t1.cashout, 1130 t1.net_cf, 1131 t1.no, 1132 t1.trandt, 1133 t1.company, 1134 make, 1135 year_,vin, 1136 BAL_PRINCIPAL_AMT,BAL_INT_AMOUNT,REPO_CHARGE 1137 1138 FROM tt.LA4 t1 1139 having open_dt<=&date 1140 ORDER BY t1.dealnbr, 1141 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.FILTER created, with 1889901 rows and 39 columns. 1142 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 17.91 seconds user cpu time 3.23 seconds system cpu time 1.78 seconds memory 774483.31k OS Memory 801372.00k Timestamp 08/24/2016 05:38:52 PM Step Count 128 Switch Count 224 1143 1144 1145 data f2(drop=due_date LOAN_DATE); 1146 set filter; 1147 due_dt=datepart(due_date); 1148 sub_dealdt=datepart(LOAN_DATE); 1149 format due_dt sub_dealdt mmddyy.; 1150 run; NOTE: There were 1889901 observations read from the data set WORK.FILTER. NOTE: The data set WORK.F2 has 1889901 observations and 39 variables. NOTE: DATA statement used (Total process time): real time 4.11 seconds user cpu time 0.39 seconds system cpu time 0.93 seconds memory 571.21k OS Memory 28512.00k Timestamp 08/24/2016 05:38:56 PM Step Count 129 Switch Count 52 52 The SAS System 16:00 Wednesday, August 24, 2016 1151 1152 1153 data loan1; 1154 set tt.tran; 1155 BY CUSTOMER_NBR 1156 LOAN_NBR 1157 TITLE_LOAN_NBR 1158 TRANSACTION_DATE 1159 TRANSACTION_NBR; 1160 1161 if first.CUSTOMER_NBR; 1162 first_dt=datepart(TRANSACTION_DATE); 1163 format first_dt mmddyy.; 1164 run; NOTE: There were 13680821 observations read from the data set TT.TRAN. NOTE: The data set WORK.LOAN1 has 168202 observations and 30 variables. NOTE: DATA statement used (Total process time): real time 49.81 seconds user cpu time 5.12 seconds system cpu time 1.62 seconds memory 637.68k OS Memory 28512.00k Timestamp 08/24/2016 05:39:46 PM Step Count 130 Switch Count 140 1165 1166 1167 proc sql; 1168 create table tt as 1169 select distinct t1.*,b.first_dt 1170 from f2 t1 left join loan1 b 1171 on t1.customer_nbr=b.customer_nbr 1172 order BY customer_nbr, 1173 dealnbr, 1174 num; NOTE: SAS threaded sort was used. NOTE: Table WORK.TT created, with 1889901 rows and 40 columns. 1175 quit; NOTE: PROCEDURE SQL used (Total process time): real time 20.22 seconds user cpu time 10.04 seconds system cpu time 9.29 seconds memory 1055561.89k OS Memory 1081632.00k Timestamp 08/24/2016 05:40:06 PM Step Count 131 Switch Count 64 1176 1177 1178 proc sql; 1179 create table st as 1180 select l.*, b.state_code 53 The SAS System 16:00 Wednesday, August 24, 2016 1181 from eca.QF_TP_LOAN_DATA l, eca.qf_branch_data b 1182 where l.branch_nbr=b.branch_nbr; NOTE: Table WORK.ST created, with 1415850 rows and 35 columns. 1183 quit; NOTE: PROCEDURE SQL used (Total process time): real time 54.93 seconds user cpu time 31.40 seconds system cpu time 1.64 seconds memory 3428.56k OS Memory 31844.00k Timestamp 08/24/2016 05:41:01 PM Step Count 132 Switch Count 5669 1184 data st1; 1185 set st; 1186 deal_dt=datepart(LOAN_DATE); 1187 yr=year(deal_dt); 1188 mon=month(deal_dt); 1189 mon_vintage="Y"||strip(put(yr,best.))||"Q"||strip(put(mon,best.))||""; 1190 run; NOTE: There were 1415850 observations read from the data set WORK.ST. NOTE: The data set WORK.ST1 has 1415850 observations and 39 variables. NOTE: DATA statement used (Total process time): real time 2.59 seconds user cpu time 0.68 seconds system cpu time 1.18 seconds memory 583.00k OS Memory 28768.00k Timestamp 08/24/2016 05:41:03 PM Step Count 133 Switch Count 48 1191 1192 1193 PROC SQL; 1194 CREATE TABLE WORK.QUERY_FOR_ST_0000 AS 1195 SELECT DISTINCT t1.CUSTOMER_NBR, 1196 t1.LOAN_NBR, 1197 t1.STATE_CODE, 1198 t1.AVERAGE_VALUE 1199 FROM WORK.ST t1 1200 WHERE t1.VALUE_USED NOT IS MISSING; NOTE: SAS threaded sort was used. NOTE: Table WORK.QUERY_FOR_ST_0000 created, with 343772 rows and 4 columns. 1201 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 1.04 seconds user cpu time 1.31 seconds system cpu time 0.53 seconds memory 89779.56k OS Memory 116364.00k Timestamp 08/24/2016 05:41:04 PM Step Count 134 Switch Count 56 54 The SAS System 16:00 Wednesday, August 24, 2016 1202 1203 proc sql; 1204 create table book3 as 1205 select a.*,b.AVERAGE_VALUE as book_value,adv/AVERAGE_VALUE as adv_over_book 1206 from tt as a left join QUERY_FOR_ST_0000 as b 1207 on a.loan_nbr=b.loan_nbr 1208 ORDER BY customer_nbr, 1209 dealnbr, 1210 num; NOTE: SAS threaded sort was used. NOTE: Table WORK.BOOK3 created, with 1889901 rows and 42 columns. 1211 quit; NOTE: PROCEDURE SQL used (Total process time): real time 6.18 seconds user cpu time 6.03 seconds system cpu time 3.46 seconds memory 820087.93k OS Memory 847000.00k Timestamp 08/24/2016 05:41:11 PM Step Count 135 Switch Count 64 1212 1213 1214 PROC SQL; 1215 CREATE TABLE WORK.FILTER_FOR_RETRO_0003 AS 1216 SELECT t1.* , 1217 b.SSN,DOB 1218 FROM book3 t1 left join eca.qf_customer_details b 1219 on t1.customer_nbr=b.customer_nbr; WARNING: Variable SSN already exists on file WORK.FILTER_FOR_RETRO_0003. NOTE: SAS threaded sort was used. NOTE: Table WORK.FILTER_FOR_RETRO_0003 created, with 1889901 rows and 43 columns. 1220 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 7.59 seconds user cpu time 3.46 seconds system cpu time 0.96 seconds memory 37550.43k OS Memory 63860.00k Timestamp 08/24/2016 05:41:18 PM Step Count 136 Switch Count 197 1221 1222 1223 1224 proc sort data=WORK.FILTER_FOR_RETRO_0003 out=schdeal; 1225 by dealnbr num; 1226 run; NOTE: There were 1889901 observations read from the data set WORK.FILTER_FOR_RETRO_0003. NOTE: SAS threaded sort was used. NOTE: The data set WORK.SCHDEAL has 1889901 observations and 43 variables. NOTE: PROCEDURE SORT used (Total process time): 55 The SAS System 16:00 Wednesday, August 24, 2016 real time 2.98 seconds user cpu time 1.92 seconds system cpu time 1.65 seconds memory 822591.95k OS Memory 850544.00k Timestamp 08/24/2016 05:41:21 PM Step Count 137 Switch Count 38 1227 1228 data schdeal2; 1229 set schdeal ; 1230 by dealnbr num; 1231 if last.dealnbr; 1232 run; NOTE: There were 1889901 observations read from the data set WORK.SCHDEAL. NOTE: The data set WORK.SCHDEAL2 has 72991 observations and 43 variables. NOTE: DATA statement used (Total process time): real time 0.76 seconds user cpu time 0.34 seconds system cpu time 0.40 seconds memory 646.78k OS Memory 28768.00k Timestamp 08/24/2016 05:41:22 PM Step Count 138 Switch Count 48 1233 1234 PROC SQL; 1235 CREATE TABLE pd2 AS 1236 SELECT t1.* ,b.loan_status as last_status,b.due_dt as Last_due 1237 FROM WORK.FILTER_FOR_RETRO_0003 t1 left join schdeal2 b 1238 on t1.dealnbr=b.dealnbr 1239 order by dealnbr,num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD2 created, with 1889901 rows and 45 columns. 1240 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 11.81 seconds user cpu time 4.60 seconds system cpu time 3.98 seconds memory 850880.39k OS Memory 877444.00k Timestamp 08/24/2016 05:41:34 PM Step Count 139 Switch Count 56 1241 1242 1243 data pd2; 1244 set pd2; 1245 LastDue=datepart(Last_due); 1246 DOB_=datepart(DOB); 1247 format LastDue DOB_ mmddyy.; 1248 run; 56 The SAS System 16:00 Wednesday, August 24, 2016 NOTE: There were 1889901 observations read from the data set WORK.PD2. NOTE: The data set WORK.PD2 has 1889901 observations and 47 variables. NOTE: DATA statement used (Total process time): real time 8.86 seconds user cpu time 0.42 seconds system cpu time 1.20 seconds memory 580.59k OS Memory 28768.00k Timestamp 08/24/2016 05:41:43 PM Step Count 140 Switch Count 56 1249 1250 1251 1252 data dealend; 1253 set pd2; 1254 by dealnbr num; 1255 if last.dealnbr; 1256 if last_status^='OPN' then dealenddt=trandt; 1257 else dealenddt=.; 1258 run; NOTE: There were 1889901 observations read from the data set WORK.PD2. NOTE: The data set WORK.DEALEND has 72991 observations and 48 variables. NOTE: DATA statement used (Total process time): real time 0.90 seconds user cpu time 0.26 seconds system cpu time 0.43 seconds memory 655.96k OS Memory 28768.00k Timestamp 08/24/2016 05:41:44 PM Step Count 141 Switch Count 48 1259 1260 1261 PROC SQL; 1262 CREATE TABLE pd3 AS 1263 SELECT t1.* ,b.dealenddt format=mmddyy., 1264 case 1265 when t1.TRANSACTION_TYPE in ('DEF') then 1 1266 else 0 1267 end as DEF 1268 FROM pd2 t1 left join dealend b 1269 on t1.dealnbr=b.dealnbr 1270 order by 1271 t1.dealnbr, 1272 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD3 created, with 1889901 rows and 49 columns. 1273 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 11.78 seconds user cpu time 5.00 seconds system cpu time 4.60 seconds memory 906333.31k 57 The SAS System 16:00 Wednesday, August 24, 2016 OS Memory 933464.00k Timestamp 08/24/2016 05:41:55 PM Step Count 142 Switch Count 76 1274 1275 1276 proc sql; 1277 create table def as 1278 select distinct dealnbr, TITLE_LOAN_NBR, min(trandt) as default_dt format=mmddyy.,mean(def) as def 1279 from pd3 1280 where def=1 1281 group by TITLE_LOAN_NBR 1282 order by dealnbr,TITLE_LOAN_NBR ; NOTE: The query requires remerging summary statistics back with the original data. NOTE: SAS threaded sort was used. NOTE: Table WORK.DEF created, with 28137 rows and 4 columns. 1283 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.56 seconds user cpu time 0.17 seconds system cpu time 0.37 seconds memory 6898.60k OS Memory 33440.00k Timestamp 08/24/2016 05:41:56 PM Step Count 143 Switch Count 54 1284 1285 1286 1287 1288 1289 PROC SQL; 1290 CREATE TABLE pd4 AS 1291 SELECT t1.* ,b.default_dt,b.def,b.TITLE_LOAN_NBR as def_TITLE_NBR, 1292 case 1293 when t1.TRANSACTION_TYPE='WOB' then 1 1294 else 0 1295 end as bankruptcy 1296 FROM pd3(drop=def) t1 left join def b 1297 on t1.TITLE_LOAN_NBR=b.TITLE_LOAN_NBR 1298 order by t1.dealnbr, 1299 t1.num; NOTE: SAS threaded sort was used. NOTE: Table WORK.PD4 created, with 1889901 rows and 52 columns. 1300 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 11.23 seconds user cpu time 5.56 seconds system cpu time 4.48 seconds memory 950560.78k OS Memory 977560.00k Timestamp 08/24/2016 05:42:07 PM Step Count 144 Switch Count 68 58 The SAS System 16:00 Wednesday, August 24, 2016 1301 1302 proc sql; 1303 create table BNK as 1304 select distinct dealnbr,customer_nbr,loan_nbr, TITLE_LOAN_NBR,trandt,TRANSACTION_TYPE 1305 from pd4 1306 where bankruptcy=1; NOTE: Table WORK.BNK created, with 701 rows and 6 columns. 1307 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.50 seconds user cpu time 0.07 seconds system cpu time 0.42 seconds memory 3259.12k OS Memory 31844.00k Timestamp 08/24/2016 05:42:08 PM Step Count 145 Switch Count 42 1308 1309 data BNK1; 1310 set BNK; 1311 by dealnbr trandt; 1312 if first.dealnbr; 1313 run; NOTE: There were 701 observations read from the data set WORK.BNK. NOTE: The data set WORK.BNK1 has 701 observations and 6 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.00 seconds system cpu time 0.03 seconds memory 570.90k OS Memory 28768.00k Timestamp 08/24/2016 05:42:08 PM Step Count 146 Switch Count 40 1314 1315 PROC SQL; 1316 CREATE TABLE pd4 AS 1317 SELECT t1.* ,b.trandt as BNK_dt 1318 FROM pd4(drop=bankruptcy) t1 left join BNK1 b 1319 on t1.dealnbr=b.dealnbr 1320 order by t1.dealnbr, 1321 t1.num; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table WORK.PD4 created, with 1889901 rows and 52 columns. 1322 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 9.21 seconds user cpu time 2.78 seconds system cpu time 2.03 seconds memory 950099.06k 59 The SAS System 16:00 Wednesday, August 24, 2016 OS Memory 976792.00k Timestamp 08/24/2016 05:42:17 PM Step Count 147 Switch Count 60 1323 1324 1325 PROC SQL; 1326 CREATE TABLE wo AS 1327 SELECT t1.* , 1328 case 1329 when t1.TRANSACTION_TYPE in ('WO') then 1 1330 else 0 1331 end as WO 1332 FROM pd4 t1; NOTE: Table WORK.WO created, with 1889901 rows and 53 columns. 1333 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 1.57 seconds user cpu time 0.64 seconds system cpu time 0.90 seconds memory 3302.84k OS Memory 31844.00k Timestamp 08/24/2016 05:42:18 PM Step Count 148 Switch Count 54 1334 1335 1336 proc sql; 1337 create table wo2 as 1338 select distinct dealnbr, min(trandt) as wo_dt format=mmddyy.,mean(wo) as WO 1339 from wo 1340 where wo=1 1341 group by dealnbr 1342 order by dealnbr ; NOTE: Table WORK.WO2 created, with 23345 rows and 3 columns. 1343 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.78 seconds user cpu time 0.17 seconds system cpu time 0.37 seconds memory 3281.40k OS Memory 31844.00k Timestamp 08/24/2016 05:42:19 PM Step Count 149 Switch Count 50 1344 1345 1346 PROC SQL; 1347 CREATE TABLE tt.LA_ALLL AS 1348 SELECT t1.* ,b.wo_dt,b.WO 1349 FROM pd4 t1 left join WO2 b 1350 on t1.dealnbr =b.dealnbr 1351 order by t1.dealnbr, 60 The SAS System 16:00 Wednesday, August 24, 2016 1352 t1.num; NOTE: SAS threaded sort was used. NOTE: Table TT.LA_ALLL created, with 1889901 rows and 54 columns. 1353 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 12.59 seconds user cpu time 2.96 seconds system cpu time 2.64 seconds memory 980301.51k OS Memory 1007064.00k Timestamp 08/24/2016 05:42:32 PM Step Count 150 Switch Count 55 1354 1355 data sold; 1356 set tt.LA_ALLL; 1357 if TRANSACTION_TYPE='SLD'; 1358 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: The data set WORK.SOLD has 371 observations and 54 variables. NOTE: DATA statement used (Total process time): real time 8.48 seconds user cpu time 0.51 seconds system cpu time 0.28 seconds memory 605.12k OS Memory 28768.00k Timestamp 08/24/2016 05:42:40 PM Step Count 151 Switch Count 44 1359 1360 data repo; 1361 set tt.LA_ALLL; 1362 if TRANSACTION_TYPE='REPO'; 1363 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: The data set WORK.REPO has 444 observations and 54 variables. NOTE: DATA statement used (Total process time): real time 0.57 seconds user cpu time 0.26 seconds system cpu time 0.29 seconds memory 605.21k OS Memory 28768.00k Timestamp 08/24/2016 05:42:41 PM Step Count 152 Switch Count 36 1364 1365 proc sql; 1366 create table tt.LA_ALLL as 1367 select a.*,b.cashin as Sold,c.cashout as Repo_cost 1368 from tt.LA_ALLL a left join sold b 1369 on a.loan_nbr=b.loan_nbr and a.trandt=b.trandt and a.num=b.num left join repo c 1370 on a.loan_nbr=c.loan_nbr and a.trandt=c.trandt and a.num=c.num; 61 The SAS System 16:00 Wednesday, August 24, 2016 WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table TT.LA_ALLL created, with 1889901 rows and 56 columns. 1371 quit; NOTE: PROCEDURE SQL used (Total process time): real time 28.63 seconds user cpu time 5.12 seconds system cpu time 4.81 seconds memory 997997.15k OS Memory 1024196.00k Timestamp 08/24/2016 05:43:10 PM Step Count 153 Switch Count 71 1372 1373 1374 proc sort data=tt.LA_ALLL; 1375 by dealnbr no; 1376 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: SAS threaded sort was used. NOTE: The data set TT.LA_ALLL has 1889901 observations and 56 variables. NOTE: PROCEDURE SORT used (Total process time): real time 21.84 seconds user cpu time 2.70 seconds system cpu time 2.03 seconds memory 1001590.60k OS Memory 1029516.00k Timestamp 08/24/2016 05:43:31 PM Step Count 154 Switch Count 49 1377 1378 1379 data tt.LA_ALLL; 1380 set tt.LA_ALLL; 1381 by dealnbr ; 1382 if BAL_PRINCIPAL_AMT=. then BAL_PRINCIPAL_AMT=0; 1383 /* if first.dealnbr then Prin_bal=0;*/ 1384 1385 if first.dealnbr then Prin_bal=BAL_PRINCIPAL_AMT; 1386 else Prin_bal+BAL_PRINCIPAL_AMT; 1387 1388 if first.dealnbr then Interest_bal=BAL_INT_AMOUNT; 1389 else Interest_bal+BAL_INT_AMOUNT; 1390 1391 if first.dealnbr then repo_bal=REPO_CHARGE; 1392 else repo_bal+REPO_CHARGE; 1393 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: The data set TT.LA_ALLL has 1889901 observations and 59 variables. NOTE: DATA statement used (Total process time): real time 19.80 seconds user cpu time 0.82 seconds 62 The SAS System 16:00 Wednesday, August 24, 2016 system cpu time 1.48 seconds memory 678.43k OS Memory 28768.00k Timestamp 08/24/2016 05:43:51 PM Step Count 155 Switch Count 99 1394 1395 1396 data interest_paid; 1397 set tt.LA_ALLL; 1398 if TRANSACTION_TYPE ^='SLD'; 1399 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: The data set WORK.INTEREST_PAID has 1889530 observations and 59 variables. NOTE: DATA statement used (Total process time): real time 16.23 seconds user cpu time 0.90 seconds system cpu time 1.68 seconds memory 618.84k OS Memory 28768.00k Timestamp 08/24/2016 05:44:07 PM Step Count 156 Switch Count 60 1400 1401 proc sql; 1402 create table tt.la_alll as 1403 select a.*, 1404 case 1405 when b.interest=. then 0 1406 else -1*b.interest 1407 end as interest_cashin, 1408 case 1409 when b.principal_paid=. then 0 1410 else -1*b.principal_paid 1411 end as prin_paid 1412 1413 from tt.LA_ALLL a left join interest_paid b 1414 on a.dealnbr=b.dealnbr and a.no=b.no; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table TT.LA_ALLL created, with 1889901 rows and 61 columns. 1415 quit; NOTE: PROCEDURE SQL used (Total process time): real time 34.22 seconds user cpu time 7.32 seconds system cpu time 7.28 seconds memory 1049687.03k OS Memory 1076416.00k Timestamp 08/24/2016 05:44:42 PM Step Count 157 Switch Count 107 1416 63 The SAS System 16:00 Wednesday, August 24, 2016 1417 1418 data tt.LA_ALLL; 1419 set tt.LA_ALLL; 1420 if repo_bal=. then repo_bal=0; 1421 if repo_cost=. then repo_cost=0; 1422 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: The data set TT.LA_ALLL has 1889901 observations and 61 variables. NOTE: DATA statement used (Total process time): real time 21.93 seconds user cpu time 0.61 seconds system cpu time 1.46 seconds memory 618.09k OS Memory 28768.00k Timestamp 08/24/2016 05:45:04 PM Step Count 158 Switch Count 63 1423 1424 1425 1426 1427 proc sort data= tt.LA_ALLL; 1428 by dealnbr no; 1429 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: SAS threaded sort was used. NOTE: The data set TT.LA_ALLL has 1889901 observations and 61 variables. NOTE: PROCEDURE SORT used (Total process time): real time 25.50 seconds user cpu time 2.95 seconds system cpu time 3.84 seconds memory 1054465.93k OS Memory 1082396.00k Timestamp 08/24/2016 05:45:29 PM Step Count 159 Switch Count 46 1430 1431 data last; 1432 set tt.LA_ALLL; 1433 by dealnbr no; 1434 if last.dealnbr; 1435 if TRANSACTION_TYPE in ('DEF', 1436 'DFP', 1437 'REPO', 1438 'RPY', 1439 'SLD') then dealenddt=.; 1440 run; NOTE: There were 1889901 observations read from the data set TT.LA_ALLL. NOTE: The data set WORK.LAST has 72991 observations and 61 variables. NOTE: DATA statement used (Total process time): real time 10.07 seconds user cpu time 0.67 seconds system cpu time 0.46 seconds 64 The SAS System 16:00 Wednesday, August 24, 2016 memory 675.62k OS Memory 28768.00k Timestamp 08/24/2016 05:45:39 PM Step Count 160 Switch Count 72 1441 1442 PROC SQL; 1443 CREATE TABLE tt.LA_ALLL AS 1444 SELECT distinct t1.* ,b.dealenddt format=mmddyy. 1445 FROM tt.LA_ALLL(drop=dealenddt) t1 left join last b 1446 on t1.dealnbr=b.dealnbr 1447 order by t1.CUSTOMER_NBR, 1448 t1.dealnbr, 1449 t1.no; WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem. NOTE: SAS threaded sort was used. NOTE: Table TT.LA_ALLL created, with 1889901 rows and 61 columns. 1450 QUIT; NOTE: PROCEDURE SQL used (Total process time): real time 40.43 seconds user cpu time 14.11 seconds system cpu time 8.89 seconds memory 1055185.50k OS Memory 1081016.00k Timestamp 08/24/2016 05:46:20 PM Step Count 161 Switch Count 87 1451 1452 1453 GOPTIONS NOACCESSIBLE; 1454 %LET _CLIENTTASKLABEL=; 1455 %LET _CLIENTPROJECTPATH=; 1456 %LET _CLIENTPROJECTNAME=; 1457 %LET _SASPROGRAMFILE=; 1458 1459 ;*';*";*/;quit;run; 1460 ODS _ALL_ CLOSE; NOTE: ODS PDF(EGPDF) printed no output. (This sometimes results from failing to place a RUN statement before the ODS PDF(EGPDF) CLOSE statement.) 1461 1462 1463 QUIT; RUN; 1464