1 The SAS System 16:54 Tuesday, August 9, 2016 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTNAME=''; 7 %LET _SASPROGRAMFILE=; 8 9 ODS _ALL_ CLOSE; 10 OPTIONS DEV=ACTIVEX; 11 FILENAME EGHTML TEMP; 12 ODS HTML(ID=EGHTML) FILE=EGHTML 13 ENCODING='utf-8' 14 STYLE=HtmlBlue 15 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome2/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 16 ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v94/sasgraph.exe#version=9,4") 17 NOGTITLE 18 NOGFOOTNOTE 19 GPATH=&sasworklocation 20 ; NOTE: Writing HTML(EGHTML) Body file: EGHTML 21 FILENAME EGSR TEMP; 22 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 23 STYLE=HtmlBlue 24 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome2/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 25 NOGTITLE 26 NOGFOOTNOTE 27 GPATH=&sasworklocation 28 ENCODING=UTF8 29 options(rolap="on") 30 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 31 32 /**************************************************************************** 33 * Job: J_SUM_CUSTOMER_CONSUMER A59T10QB.BL0000MT * 34 * Description: * 35 * * 36 * Metadata Server: 192.168.15.81 * 37 * Port: 8562 * 38 * Location: /SAS Data Integration Studio Custom Tree/3. Summary * 39 * Mart/Jobs/01 CCRD * 40 * * 41 * Server: SASMain A59T10QB.AS000002 * 42 * * 43 * Source Tables: SAS_JHCLAS_V - gpads.SAS_JHCLAS_V A59T10QB.BG0000W7 * 44 * SAS_CFPAR3_V - gpads.SAS_CFPAR3_V A59T10QB.BG0000T7 * 45 * SAS_CFADDR_V - gpads.SAS_CFADDR_V A59T10QB.BG0000T0 * 46 * SAS_CFZEMP_V - gpads.SAS_CFZEMP_V A59T10QB.BG0000TA * 47 * SAS_BIPARE_V - gpads.SAS_BIPARE_V A59T10QB.BG0000S0 * 48 * SAS_CFZPPT_V - gpads.SAS_CFZPPT_V A59T10QB.BG0000TB * 49 * SAS_LNMAST_V - gpads.SAS_LNMAST_V A59T10QB.BG0000WO * 50 * DDS_P_JOB_OCCUPATION(1) - A59T10QB.BG000282 * 51 * ddsbase.DDS_P_JOB_OCCUPATION * 52 * SAS_CFMAST_SUM_CUSTOMER_CONS_V - A59T10QB.BG0000T5 * 53 * gpads.SAS_CFMAST_SUM_CUSTOMER_CONS_V * 54 * Target Tables: SUM_CUSTOMER_CONSUMER_EOD(1) - A59T10QB.BG00027Z * 55 * ccrd.SUM_CUSTOMER_CONSUMER_EOD * 56 * SUM_CUSTOMER_CONSUMER(1) - A59T10QB.BG000283 * 2 The SAS System 16:54 Tuesday, August 9, 2016 57 * ccrd.SUM_CUSTOMER_CONSUMER * 58 * * 59 * Generated on: Tuesday, August 9, 2016 4:53:11 PM ICT * 60 * Generated by: sas * 61 * Version: SAS Data Integration Studio 4.901 * 62 ****************************************************************************/ 63 64 /* Generate the process id for job */ 65 %put Process ID: &SYSJOBID; Process ID: 8696 66 67 /* General macro variables */ 68 %let jobID = %quote(A59T10QB.BL0000MT); 69 %let etls_jobName = %nrquote(J_SUM_CUSTOMER_CONSUMER); 70 %let etls_userID = %nrquote(sas); 71 72 /* Setup to capture return codes */ 73 %global job_rc trans_rc sqlrc; 74 %let sysrc = 0; 75 %let job_rc = 0; 76 %let trans_rc = 0; 77 %let sqlrc = 0; 78 %global etls_stepStartTime; 79 /* initialize syserr to 0 */ 80 data _null_; 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 763.90k OS Memory 23200.00k Timestamp 08/09/2016 04:54:54 PM Step Count 4 Switch Count 18 Page Faults 0 Page Reclaims 337 Page Swaps 0 Voluntary Context Switches 22 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 81 82 %macro rcSet(error); 83 %if (&error gt &trans_rc) %then 84 %let trans_rc = &error; 85 %if (&error gt &job_rc) %then 86 %let job_rc = &error; 87 %mend rcSet; 88 89 %macro rcSetDS(error); 90 if &error gt input(symget('trans_rc'),12.) then 91 call symput('trans_rc',trim(left(put(&error,12.)))); 92 if &error gt input(symget('job_rc'),12.) then 93 call symput('job_rc',trim(left(put(&error,12.)))); 94 %mend rcSetDS; 95 3 The SAS System 16:54 Tuesday, August 9, 2016 96 /* Create metadata macro variables */ 97 %let IOMServer = %nrquote(SASMain); 98 %let metaPort = %nrquote(8562); 99 %let metaServer = %nrquote(sas-metadata); 100 101 /* Setup for capturing job status */ 102 %let etls_startTime = %sysfunc(datetime(),datetime.); 103 %let etls_recordsBefore = 0; 104 %let etls_recordsAfter = 0; 105 %let etls_lib = 0; 106 %let etls_table = 0; 107 108 %global etls_debug; 109 %macro etls_setDebug; 110 %if %str(&etls_debug) ne 0 %then 111 OPTIONS MPRINT%str(;); 112 %mend; 113 %etls_setDebug; 114 115 /*---- Start of Pre-Process Code ----*/ 116 117 libname err '/sasdata/err'; NOTE: Libref ERR was successfully assigned as follows: Engine: V9 Physical Name: /sasdata/err 118 119 %qw_del_first(target_table=CCRD.SUM_CUSTOMER_CONSUMER, transaction_date=period); MPRINT(QW_DEL_FIRST): proc sql noprint; MPRINT(QW_DEL_FIRST): select count(*) into:exist_in_target from CCRD.SUM_CUSTOMER_CONSUMER where period = 20674; MPRINT(QW_DEL_FIRST): quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 6496.21k OS Memory 29608.00k Timestamp 08/09/2016 04:54:54 PM Step Count 5 Switch Count 20 Page Faults 0 Page Reclaims 205 Page Swaps 0 Voluntary Context Switches 24 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 0 120 /*---- End of Pre-Process Code ----*/ 121 122 %rcSet(&syserr); 123 %rcSet(&sqlrc); 124 125 /*==========================================================================* 126 * Step: SAS Extract A59T10QB.BP0004U3 * 127 * Transform: Extract * 128 * Description: * 129 * * 130 * Source Table: SAS_CFMAST_SUM_CUSTOMER_CONS_V - A59T10QB.BG0000T5 * 4 The SAS System 16:54 Tuesday, August 9, 2016 131 * gpads.SAS_CFMAST_SUM_CUSTOMER_CONS_V * 132 * Target Table: Extract Target - work.WCFMAST A59T10QB.BK0001LR * 133 *==========================================================================*/ 134 135 %let transformID = %quote(A59T10QB.BP0004U3); 136 %let trans_rc = 0; 137 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 138 139 /* Access the data for GPADS */ 140 LIBNAME gpads ODBC DATASRC=GPOCBCNISP SCHEMA=ads USER=user_sas_di PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ; NOTE: Libref GPADS was successfully assigned as follows: Engine: ODBC Physical Name: GPOCBCNISP 141 %rcSet(&syslibrc); 142 143 %let SYSLAST = %nrquote(gpads.SAS_CFMAST_SUM_CUSTOMER_CONS_V); 144 145 %global etls_sql_pushDown; 146 %let etls_sql_pushDown = -1; 147 option DBIDIRECTEXEC; 148 149 /*---- Map the columns ----*/ 150 proc datasets lib = work nolist nowarn memtype = (data view); 151 delete WCFMAST; 152 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 250.06k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 6 Switch Count 26 Page Faults 0 Page Reclaims 87 Page Swaps 0 Voluntary Context Switches 50 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 153 154 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 155 proc sql; 156 create view work.WCFMAST as 157 select 158 cfcif_2 as customer_id 159 label = 'CFCIF_2', 160 (datejul(cfbird)) as CFBIRD length = 8 161 format = ddmmyyyy10. 162 label = 'CFBIRD', 163 CFSEX 164 label = 'CFSEX', 165 CFUIC1 166 label = 'CFUIC1', 5 The SAS System 16:54 Tuesday, August 9, 2016 167 CFUIC2 168 label = 'CFUIC2', 169 CFUIC4 170 label = 'CFUIC4', 171 CFSNME 172 label = 'CFSNME', 173 CFCLAS 174 label = 'CFCLAS', 175 CFSSCD 176 label = 'CFSSCD', 177 CFSSNO 178 label = 'CFSSNO' 179 from &SYSLAST 180 ; NOTE: SQL view WORK.WCFMAST has been defined. 181 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 100.90k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 7 Switch Count 127 Page Faults 0 Page Reclaims 38 Page Swaps 0 Voluntary Context Switches 458 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 182 183 %let SYSLAST = work.WCFMAST; 184 185 %global etls_sql_pushDown; 186 %let etls_sql_pushDown = &sys_sql_ip_all; 187 188 %rcSet(&sqlrc); 189 190 191 192 /** Step end SAS Extract **/ 193 194 /*==========================================================================* 195 * Step: SQL Join A59T10QB.BP0004U4 * 196 * Transform: Join * 197 * Description: * 198 * * 199 * Source Tables: Extract Target - work.WCFMAST A59T10QB.BK0001LR * 200 * SAS_JHCLAS_V - gpads.SAS_JHCLAS_V A59T10QB.BG0000W7 * 201 * Target Table: SQL Target - work.W5T0VJXB A59T10QB.BK0001LS * 202 *==========================================================================*/ 203 204 %let transformID = %quote(A59T10QB.BP0004U4); 205 %let trans_rc = 0; 206 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 6 The SAS System 16:54 Tuesday, August 9, 2016 207 208 %global etls_sql_pushDown; 209 %let etls_sql_pushDown = -1; 210 option DBIDIRECTEXEC; 211 212 proc datasets lib = work nolist nowarn memtype = (data view); 213 delete W5T0VJXB; 214 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 8 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 215 216 proc sql; 217 create view work.W5T0VJXB as 218 select 219 WCFMAST.customer_id length = 40 220 format = $40. 221 label = 'CFCIF_2', 222 WCFMAST.CFBIRD length = 8 223 format = ddmmyyyy10. 224 label = 'CFBIRD', 225 WCFMAST.CFSEX length = 1 226 format = $1. 227 informat = $1. 228 label = 'CFSEX', 229 WCFMAST.CFUIC1 length = 1 230 format = $1. 231 informat = $1. 232 label = 'CFUIC1', 233 WCFMAST.CFUIC2 length = 1 234 format = $1. 235 informat = $1. 236 label = 'CFUIC2', 237 WCFMAST.CFUIC4 length = 1 238 format = $1. 239 informat = $1. 240 label = 'CFUIC4', 241 WCFMAST.CFSNME length = 20 242 format = $20. 243 informat = $20. 244 label = 'CFSNME', 245 WCFMAST.CFCLAS || ' - ' || SAS_JHCLAS_V.jhcdes as CFCLAS length = 20 246 format = $20. 7 The SAS System 16:54 Tuesday, August 9, 2016 247 informat = $20. 248 label = 'CFCLAS', 249 WCFMAST.CFSSCD length = 4 250 format = $4. 251 informat = $4. 252 label = 'CFSSCD', 253 WCFMAST.CFSSNO length = 40 254 format = $40. 255 informat = $40. 256 label = 'CFSSNO' 257 from 258 work.WCFMAST left join 259 gpads.SAS_JHCLAS_V 260 on 261 ( 262 WCFMAST.CFCLAS = SAS_JHCLAS_V.jhccod 263 ) 264 ; NOTE: SQL view WORK.W5T0VJXB has been defined. 265 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 161.43k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 9 Switch Count 219 Page Faults 0 Page Reclaims 11 Page Swaps 0 Voluntary Context Switches 823 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 266 267 %global etls_sql_pushDown; 268 %let etls_sql_pushDown = &sys_sql_ip_all; 269 270 %rcSet(&sqlrc); 271 272 273 274 /** Step end SQL Join **/ 275 276 /*==========================================================================* 277 * Step: SAS Extract A59T10QB.BP0004U5 * 278 * Transform: Extract * 279 * Description: * 280 * * 281 * Source Table: SAS_LNMAST_V - gpads.SAS_LNMAST_V A59T10QB.BG0000WO * 282 * Target Table: Extract Target - work.W5O5KCIF A59T10QB.BK0001LT * 283 *==========================================================================*/ 284 285 %let transformID = %quote(A59T10QB.BP0004U5); 286 %let trans_rc = 0; 8 The SAS System 16:54 Tuesday, August 9, 2016 287 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 288 289 %let SYSLAST = %nrquote(gpads.SAS_LNMAST_V); 290 291 %global etls_sql_pushDown; 292 %let etls_sql_pushDown = -1; 293 option DBIDIRECTEXEC; 294 295 /*---- Map the columns ----*/ 296 proc datasets lib = work nolist nowarn memtype = (data view); 297 delete W5O5KCIF; 298 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 228.87k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 10 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 299 300 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 301 proc sql; 302 create view work.W5O5KCIF as 303 select distinct 304 (&qwcurr_date) as period length = 8 305 format = ddmmyy10. 306 label = 'Period', 307 (trim(left(put(cifno ,20.)))) as customer_id length = 20 308 format = $20. 309 label = 'Customer Number' 310 from &SYSLAST 311 ; NOTE: SQL view WORK.W5O5KCIF has been defined. 312 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 82.50k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 11 Switch Count 63 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 198 9 The SAS System 16:54 Tuesday, August 9, 2016 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 313 314 %let SYSLAST = work.W5O5KCIF; 315 316 %global etls_sql_pushDown; 317 %let etls_sql_pushDown = &sys_sql_ip_all; 318 319 %rcSet(&sqlrc); 320 321 322 323 /** Step end SAS Extract **/ 324 325 /*==========================================================================* 326 * Step: SQL Join A59T10QB.BP0004U6 * 327 * Transform: Join * 328 * Description: * 329 * * 330 * Source Tables: Extract Target - work.W5O5KCIF A59T10QB.BK0001LT * 331 * SQL Target - work.W5T0VJXB A59T10QB.BK0001LS * 332 * Target Table: SQL Target - work.W5O5KI14 A59T10QB.BK0001LU * 333 *==========================================================================*/ 334 335 %let transformID = %quote(A59T10QB.BP0004U6); 336 %let trans_rc = 0; 337 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 338 339 %global etls_sql_pushDown; 340 %let etls_sql_pushDown = -1; 341 option DBIDIRECTEXEC; 342 343 proc datasets lib = work nolist nowarn memtype = (data view); 344 delete W5O5KI14; 345 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 12 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 346 347 proc sql; 10 The SAS System 16:54 Tuesday, August 9, 2016 348 create view work.W5O5KI14 as 349 select 350 W5T0VJXB.customer_id length = 40 351 format = $40. 352 label = 'CFCIF_2', 353 W5T0VJXB.CFBIRD length = 8 354 format = 8. 355 informat = 8. 356 label = 'CFBIRD', 357 W5T0VJXB.CFSEX length = 1 358 format = $1. 359 informat = $1. 360 label = 'CFSEX', 361 W5T0VJXB.CFUIC1 length = 1 362 format = $1. 363 informat = $1. 364 label = 'CFUIC1', 365 W5T0VJXB.CFUIC2 length = 1 366 format = $1. 367 informat = $1. 368 label = 'CFUIC2', 369 W5T0VJXB.CFUIC4 length = 1 370 format = $1. 371 informat = $1. 372 label = 'CFUIC4', 373 W5O5KCIF.period length = 8 374 format = ddmmyy10. 375 label = 'Period', 376 W5T0VJXB.CFSNME length = 20 377 format = $20. 378 informat = $20. 379 label = 'CFSNME', 380 W5T0VJXB.CFCLAS length = 20 381 format = $20. 382 informat = $20. 383 label = 'CFCLAS', 384 W5T0VJXB.CFSSCD length = 4 385 format = $4. 386 informat = $4. 387 label = 'CFSSCD', 388 W5T0VJXB.CFSSNO length = 40 389 format = $40. 390 informat = $40. 391 label = 'CFSSNO' 392 from 393 work.W5O5KCIF, 394 work.W5T0VJXB 395 where 396 W5O5KCIF.customer_id = W5T0VJXB.customer_id 397 ; NOTE: SQL view WORK.W5O5KI14 has been defined. 398 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 155.68k OS Memory 23968.00k 11 The SAS System 16:54 Tuesday, August 9, 2016 Timestamp 08/09/2016 04:54:55 PM Step Count 13 Switch Count 231 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 855 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 399 400 %global etls_sql_pushDown; 401 %let etls_sql_pushDown = &sys_sql_ip_all; 402 403 %rcSet(&sqlrc); 404 405 406 407 /** Step end SQL Join **/ 408 409 /*==========================================================================* 410 * Step: SAS Extract A59T10QB.BP0004U7 * 411 * Transform: Extract * 412 * Description: * 413 * * 414 * Source Table: SQL Target - work.W5O5KI14 A59T10QB.BK0001LU * 415 * Target Table: Extract Target - work.W5O5KJH1 A59T10QB.BK0001LV * 416 *==========================================================================*/ 417 418 %let transformID = %quote(A59T10QB.BP0004U7); 419 %let trans_rc = 0; 420 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 421 422 %let SYSLAST = %nrquote(work.W5O5KI14); 423 424 %global etls_sql_pushDown; 425 %let etls_sql_pushDown = -1; 426 option DBIDIRECTEXEC; 427 428 /*---- Map the columns ----*/ 429 proc datasets lib = work nolist nowarn memtype = (data view); 430 delete W5O5KJH1; 431 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 229.87k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 14 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 12 The SAS System 16:54 Tuesday, August 9, 2016 Block Input Operations 0 Block Output Operations 8 432 433 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 434 proc sql; 435 create view work.W5O5KJH1 as 436 select 437 customer_id 438 label = 'Customer number', 439 CFUIC2 as marital_status 440 label = 'Marital Status Code', 441 (intck('YEAR', CFBIRD , period )) as customer_age length = 8 442 label = 'Customer Age', 443 period, 444 CFSEX as customer_gender 445 label = 'Customer Gender', 446 CFUIC4 as number_of_dependents_code 447 label = 'Number of Dependents Code', 448 CFUIC1 as job_status_code 449 label = 'Job Status Code', 450 CFSNME, 451 CFCLAS, 452 CFSSCD, 453 CFSSNO 454 from &SYSLAST 455 ; NOTE: SQL view WORK.W5O5KJH1 has been defined. 456 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 85.37k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 15 Switch Count 107 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 374 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 457 458 %let SYSLAST = work.W5O5KJH1; 459 460 %global etls_sql_pushDown; 461 %let etls_sql_pushDown = &sys_sql_ip_all; 462 463 %rcSet(&sqlrc); 464 465 466 13 The SAS System 16:54 Tuesday, August 9, 2016 467 /** Step end SAS Extract **/ 468 469 /*==========================================================================* 470 * Step: SAS Extract A59T10QB.BP0004U8 * 471 * Transform: Extract * 472 * Description: * 473 * * 474 * Source Table: SAS_CFZEMP_V - gpads.SAS_CFZEMP_V A59T10QB.BG0000TA * 475 * Target Table: Extract Target - work.W5OB6KLW A59T10QB.BK0001LW * 476 *==========================================================================*/ 477 478 %let transformID = %quote(A59T10QB.BP0004U8); 479 %let trans_rc = 0; 480 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 481 482 %let SYSLAST = %nrquote(gpads.SAS_CFZEMP_V); 483 484 %global etls_sql_pushDown; 485 %let etls_sql_pushDown = -1; 486 option DBIDIRECTEXEC; 487 488 /*---- Map the columns ----*/ 489 proc datasets lib = work nolist nowarn memtype = (data view); 490 delete W5OB6KLW; 491 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 229.87k OS Memory 23968.00k Timestamp 08/09/2016 04:54:55 PM Step Count 16 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 492 493 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 494 proc sql; 495 create table work.W5OB6KLW as 496 select 497 CFCIF_2 498 label = 'CFCIF_2', 499 (trim(left(cfoccd))) as CFOCCD length = 5 500 format = $5. 501 informat = $5. 502 label = 'CFOCCD', 503 CFESD7 504 format = 8. 505 informat = 8. 14 The SAS System 16:54 Tuesday, August 9, 2016 506 label = 'CFESD7', 507 CFEDR7 508 format = 8. 509 informat = 8. 510 label = 'CFEDR7', 511 CFECUR 512 label = 'CFECUR', 513 CFENIN 514 label = 'CFENIN', 515 CFCURR 516 label = 'CFCURR' 517 from &SYSLAST 518 ; NOTE: Compressing data set WORK.W5OB6KLW increased size by 33.18 percent. Compressed is 2284 pages; un-compressed would require 1715 pages. NOTE: Table WORK.W5OB6KLW created, with 2331378 rows and 7 columns. 519 quit; NOTE: PROCEDURE SQL used (Total process time): real time 17.34 seconds user cpu time 15.99 seconds system cpu time 0.25 seconds memory 5299.65k OS Memory 29092.00k Timestamp 08/09/2016 04:55:12 PM Step Count 17 Switch Count 129 Page Faults 0 Page Reclaims 416 Page Swaps 0 Voluntary Context Switches 477 Involuntary Context Switches 20 Block Input Operations 0 Block Output Operations 292480 520 521 %let SYSLAST = work.W5OB6KLW; 522 523 %global etls_sql_pushDown; 524 %let etls_sql_pushDown = &sys_sql_ip_all; 525 526 %rcSet(&sqlrc); 527 528 529 530 /** Step end SAS Extract **/ 531 532 /*==========================================================================* 533 * Step: SQL Join A59T10QB.BP0004U9 * 534 * Transform: Join * 535 * Description: * 536 * * 537 * Source Tables: Extract Target - work.W5OB6KLW A59T10QB.BK0001LW * 538 * SAS_BIPARE_V - gpads.SAS_BIPARE_V A59T10QB.BG0000S0 * 539 * Target Table: SQL Target - work.W5T0UYMD A59T10QB.BK0001LX * 540 *==========================================================================*/ 541 542 %let transformID = %quote(A59T10QB.BP0004U9); 15 The SAS System 16:54 Tuesday, August 9, 2016 543 %let trans_rc = 0; 544 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 545 546 %global etls_sql_pushDown; 547 %let etls_sql_pushDown = -1; 548 option DBIDIRECTEXEC; 549 550 proc datasets lib = work nolist nowarn memtype = (data view); 551 delete W5T0UYMD; 552 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 229.87k OS Memory 24224.00k Timestamp 08/09/2016 04:55:12 PM Step Count 18 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 553 554 proc sql; 555 create table work.W5T0UYMD as 556 select 557 W5OB6KLW.CFCIF_2 length = 8 558 format = 20. 559 informat = 20. 560 label = 'CFCIF#', 561 W5OB6KLW.CFOCCD length = 5 562 format = $5. 563 informat = $5. 564 label = 'CFOCCD', 565 W5OB6KLW.CFESD7 length = 8 566 format = 8. 567 informat = 8. 568 label = 'CFESD7', 569 W5OB6KLW.CFEDR7 length = 8 570 format = 8. 571 informat = 8. 572 label = 'CFEDR7', 573 W5OB6KLW.CFECUR length = 8 574 format = 19.2 575 informat = 19.2 576 label = 'CFECUR', 577 left(right(W5OB6KLW.CFENIN)) || ' - ' || left(right(SAS_BIPARE_V.biedes)) as CFENIN length = 70 578 format = $70. 579 informat = $70. 580 label = 'CFENIN', 581 W5OB6KLW.CFCURR length = 4 582 format = $4. 16 The SAS System 16:54 Tuesday, August 9, 2016 583 informat = $4. 584 label = 'CFCURR' 585 from 586 work.W5OB6KLW left join 587 gpads.SAS_BIPARE_V 588 on 589 ( 590 left(right(W5OB6KLW.CFENIN)) = left(right(put(SAS_BIPARE_V.bisekt, best.))) 591 ) 592 ; NOTE: Compressing data set WORK.W5T0UYMD decreased size by 4.38 percent. Compressed is 3818 pages; un-compressed would require 3993 pages. NOTE: Table WORK.W5T0UYMD created, with 2331378 rows and 7 columns. 593 quit; NOTE: PROCEDURE SQL used (Total process time): real time 16.08 seconds user cpu time 4.57 seconds system cpu time 0.47 seconds memory 188660.32k OS Memory 212556.00k Timestamp 08/09/2016 04:55:28 PM Step Count 19 Switch Count 193 Page Faults 0 Page Reclaims 21522 Page Swaps 0 Voluntary Context Switches 7990 Involuntary Context Switches 54 Block Input Operations 0 Block Output Operations 744584 594 595 %global etls_sql_pushDown; 596 %let etls_sql_pushDown = &sys_sql_ip_all; 597 598 %rcSet(&sqlrc); 599 600 601 602 /** Step end SQL Join **/ 603 604 /*==========================================================================* 605 * Step: SAS Splitter A59T10QB.BP0004UA * 606 * Transform: Splitter * 607 * Description: * 608 * * 609 * Source Table: SQL Target - work.W5T0UYMD A59T10QB.BK0001LX * 610 * Target Tables: Splitter Target 1 - work.W5OB6KOF A59T10QB.BK0001LY * 611 * Splitter Target 2 - work.W5OB6KOP A59T10QB.BK0001LZ * 612 *==========================================================================*/ 613 614 %let transformID = %quote(A59T10QB.BP0004UA); 615 %let trans_rc = 0; 616 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 617 618 %let SYSLAST = %nrquote(work.W5T0UYMD); 619 17 The SAS System 16:54 Tuesday, August 9, 2016 620 proc datasets lib=work nolist nowarn memtype = (data view); 621 delete W5OB6KOF; 622 delete W5OB6KOP; 623 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 229.87k OS Memory 27560.00k Timestamp 08/09/2016 04:55:28 PM Step Count 20 Switch Count 34 Page Faults 0 Page Reclaims 5 Page Swaps 0 Voluntary Context Switches 80 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 624 625 data 626 work.W5OB6KOF 627 work.W5OB6KOP ; 628 set &SYSLAST; 629 output work.W5OB6KOF; 630 output work.W5OB6KOP; 631 run; NOTE: There were 2331378 observations read from the data set WORK.W5T0UYMD. NOTE: The data set WORK.W5OB6KOF has 2331378 observations and 7 variables. NOTE: Compressing data set WORK.W5OB6KOF decreased size by 4.38 percent. Compressed is 3818 pages; un-compressed would require 3993 pages. NOTE: The data set WORK.W5OB6KOP has 2331378 observations and 7 variables. NOTE: Compressing data set WORK.W5OB6KOP decreased size by 4.38 percent. Compressed is 3818 pages; un-compressed would require 3993 pages. NOTE: DATA statement used (Total process time): real time 3.31 seconds user cpu time 2.96 seconds system cpu time 0.36 seconds memory 3884.40k OS Memory 29868.00k Timestamp 08/09/2016 04:55:32 PM Step Count 21 Switch Count 62 Page Faults 0 Page Reclaims 791 Page Swaps 0 Voluntary Context Switches 187 Involuntary Context Switches 106 Block Input Operations 0 Block Output Operations 977664 632 633 %rcSet(&syserr); 634 18 The SAS System 16:54 Tuesday, August 9, 2016 635 636 637 /** Step end SAS Splitter **/ 638 639 /*==========================================================================* 640 * Step: SAS Extract A59T10QB.BP0004UB * 641 * Transform: Extract * 642 * Description: * 643 * * 644 * Source Table: Splitter Target 2 - work.W5OB6KOP A59T10QB.BK0001LZ * 645 * Target Table: Extract Target - work.W5OB6KQ9 A59T10QB.BK0001M0 * 646 *==========================================================================*/ 647 648 %let transformID = %quote(A59T10QB.BP0004UB); 649 %let trans_rc = 0; 650 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 651 652 %let SYSLAST = %nrquote(work.W5OB6KOP); 653 654 %global etls_sql_pushDown; 655 %let etls_sql_pushDown = -1; 656 option DBIDIRECTEXEC; 657 658 /*---- Map the columns ----*/ 659 proc datasets lib = work nolist nowarn memtype = (data view); 660 delete W5OB6KQ9; 661 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 229.87k OS Memory 27304.00k Timestamp 08/09/2016 04:55:32 PM Step Count 22 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 662 663 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 664 proc sql; 665 create view work.W5OB6KQ9 as 666 select 667 CFCIF_2, 668 (max(CFOCCD)) as CFOCCD length = 5 669 format = $5. 670 informat = $5. 671 label = 'CFOCCD', 672 (max(CFESD7)) as CFESD7 length = 8 673 format = 8. 19 The SAS System 16:54 Tuesday, August 9, 2016 674 informat = 8. 675 label = 'CFESD7', 676 (max(CFEDR7)) as CFEDR7 length = 8 677 format = 8. 678 informat = 8. 679 label = 'CFEDR7', 680 (max(CFECUR)) as CFECUR length = 8 681 format = 19.2 682 informat = 19.2 683 label = 'CFECUR', 684 (max(CFENIN)) as CFENIN length = 70 685 format = $70. 686 label = 'CFENIN', 687 (max(CFCURR)) as CFCURR length = 4 688 format = $4. 689 informat = $4. 690 label = 'CFCURR' 691 from &SYSLAST 692 group by 693 CFCIF_2 694 ; NOTE: SQL view WORK.W5OB6KQ9 has been defined. 695 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 148.12k OS Memory 27048.00k Timestamp 08/09/2016 04:55:32 PM Step Count 23 Switch Count 147 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 541 Involuntary Context Switches 1 Block Input Operations 0 Block Output Operations 32 696 697 %let SYSLAST = work.W5OB6KQ9; 698 699 %global etls_sql_pushDown; 700 %let etls_sql_pushDown = &sys_sql_ip_all; 701 702 %rcSet(&sqlrc); 703 704 705 706 /** Step end SAS Extract **/ 707 708 /*==========================================================================* 709 * Step: SQL Join A59T10QB.BP0004UC * 710 * Transform: Join * 711 * Description: * 712 * * 713 * Source Tables: Extract Target - work.W5OB6KQ9 A59T10QB.BK0001M0 * 20 The SAS System 16:54 Tuesday, August 9, 2016 714 * Splitter Target 1 - work.W5OB6KOF A59T10QB.BK0001LY * 715 * Target Table: SQL Target - work.W5OB6KSG A59T10QB.BK0001M1 * 716 *==========================================================================*/ 717 718 %let transformID = %quote(A59T10QB.BP0004UC); 719 %let trans_rc = 0; 720 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 721 722 %global etls_sql_pushDown; 723 %let etls_sql_pushDown = -1; 724 option DBIDIRECTEXEC; 725 726 proc datasets lib = work nolist nowarn memtype = (data view); 727 delete W5OB6KSG; 728 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 229.87k OS Memory 27048.00k Timestamp 08/09/2016 04:55:32 PM Step Count 24 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 51 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 729 730 proc sql; 731 create table work.W5OB6KSG as 732 select distinct 733 compress(put(W5OB6KQ9.CFCIF_2 , 20.)) as CFCIF_2 length = 20 734 label = 'CFCIF#', 735 W5OB6KOF.CFOCCD length = 5 736 format = $5. 737 informat = $5. 738 label = 'CFOCCD', 739 case W5OB6KOF.CFESD7 when . then . when 0 then . when 1900001 then . when 1901001 then . else 739 ! datejul(W5OB6KOF.CFESD7 ) end as CFESD7 length = 8 740 format = ddmmyy10. 741 label = 'CFESD7', 742 W5OB6KOF.CFECUR length = 8 743 format = 19.2 744 informat = 19.2 745 label = 'CFECUR', 746 W5OB6KOF.CFENIN length = 70 747 format = $70. 748 informat = $70. 749 label = 'CFENIN', 750 W5OB6KOF.CFCURR length = 4 751 format = $4. 752 informat = $4. 21 The SAS System 16:54 Tuesday, August 9, 2016 753 label = 'CFCURR' 754 from 755 work.W5OB6KQ9, 756 work.W5OB6KOF 757 where 758 W5OB6KQ9.CFCIF_2 = W5OB6KOF.CFCIF_2 759 and W5OB6KQ9.CFEDR7 = W5OB6KOF.CFEDR7 760 and W5OB6KQ9.CFOCCD = W5OB6KOF.CFOCCD 761 and W5OB6KQ9.CFESD7 = W5OB6KOF.CFESD7 762 and W5OB6KQ9.CFECUR = W5OB6KOF.CFECUR 763 and W5OB6KQ9.CFENIN = W5OB6KOF.CFENIN 764 and W5OB6KQ9.CFCURR = W5OB6KOF.CFCURR 765 ; NOTE: Invalid argument to function DATEJUL. Missing values may be generated. NOTE: Compressing data set WORK.W5OB6KSG decreased size by 14.69 percent. Compressed is 3491 pages; un-compressed would require 4092 pages. NOTE: Table WORK.W5OB6KSG created, with 2229959 rows and 6 columns. 766 quit; NOTE: PROCEDURE SQL used (Total process time): real time 14.59 seconds user cpu time 13.92 seconds system cpu time 2.71 seconds memory 645034.01k OS Memory 668852.00k Timestamp 08/09/2016 04:55:46 PM Step Count 25 Switch Count 184 Page Faults 0 Page Reclaims 163369 Page Swaps 0 Voluntary Context Switches 33556 Involuntary Context Switches 68 Block Input Operations 0 Block Output Operations 2765968 767 768 %global etls_sql_pushDown; 769 %let etls_sql_pushDown = &sys_sql_ip_all; 770 771 %rcSet(&sqlrc); 772 773 774 775 /** Step end SQL Join **/ 776 777 /*==========================================================================* 778 * Step: SQL Join A59T10QB.BP0004UD * 779 * Transform: Join * 780 * Description: * 781 * * 782 * Source Tables: SQL Target - work.W5OB6KSG A59T10QB.BK0001M1 * 783 * DDS_P_JOB_OCCUPATION(1) - A59T10QB.BG000282 * 784 * ddsbase.DDS_P_JOB_OCCUPATION * 785 * Target Table: SQL Target - work.WCFZEMP A59T10QB.BK0001M2 * 786 *==========================================================================*/ 787 788 %let transformID = %quote(A59T10QB.BP0004UD); 22 The SAS System 16:54 Tuesday, August 9, 2016 789 %let trans_rc = 0; 790 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 791 792 /* Access the data for Detail Data Storage (ddsbase) */ 793 LIBNAME ddsbase BASE "/sasdata/dds"; NOTE: Libref DDSBASE was successfully assigned as follows: Engine: BASE Physical Name: /sasdata/dds 794 %rcSet(&syslibrc); 795 796 %global etls_sql_pushDown; 797 %let etls_sql_pushDown = -1; 798 option DBIDIRECTEXEC; 799 800 proc datasets lib = work nolist nowarn memtype = (data view); 801 delete WCFZEMP; 802 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27816.00k Timestamp 08/09/2016 04:55:46 PM Step Count 26 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 803 804 proc sql; 805 create view work.WCFZEMP as 806 select 807 W5OB6KSG.CFCIF_2 length = 20 808 label = 'CFCIF#', 809 W5OB6KSG.CFESD7 length = 8 810 format = ddmmyy10. 811 label = 'CFESD7', 812 W5OB6KSG.CFOCCD length = 5 813 format = $5. 814 informat = $5. 815 label = 'CFOCCD', 816 W5OB6KSG.CFECUR length = 8 817 format = 19.2 818 informat = 19.2 819 label = 'CFECUR', 820 DDS_P_JOB_OCCUPATION.occupation_desc length = 40 821 format = $40. 822 label = 'Job Occupation Description', 823 W5OB6KSG.CFENIN length = 70 824 format = $70. 825 informat = $70. 23 The SAS System 16:54 Tuesday, August 9, 2016 826 label = 'CFENIN', 827 W5OB6KSG.CFCURR length = 4 828 format = $4. 829 informat = $4. 830 label = 'CFCURR' 831 from 832 work.W5OB6KSG left join 833 ddsbase.DDS_P_JOB_OCCUPATION 834 on 835 ( 836 W5OB6KSG.CFOCCD = DDS_P_JOB_OCCUPATION.occupation_code 837 ) 838 ; NOTE: SQL view WORK.WCFZEMP has been defined. 839 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 155.31k OS Memory 27816.00k Timestamp 08/09/2016 04:55:46 PM Step Count 27 Switch Count 163 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 597 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 840 841 %global etls_sql_pushDown; 842 %let etls_sql_pushDown = &sys_sql_ip_all; 843 844 %rcSet(&sqlrc); 845 846 847 848 /** Step end SQL Join **/ 849 850 /*==========================================================================* 851 * Step: SAS Extract A59T10QB.BP0004UE * 852 * Transform: Extract * 853 * Description: * 854 * * 855 * Source Table: SAS_CFADDR_V - gpads.SAS_CFADDR_V A59T10QB.BG0000T0 * 856 * Target Table: Extract Target - work.W5OB6C0P A59T10QB.BK0001M3 * 857 *==========================================================================*/ 858 859 %let transformID = %quote(A59T10QB.BP0004UE); 860 %let trans_rc = 0; 861 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 862 863 %let SYSLAST = %nrquote(gpads.SAS_CFADDR_V); 864 865 %global etls_sql_pushDown; 24 The SAS System 16:54 Tuesday, August 9, 2016 866 %let etls_sql_pushDown = -1; 867 option DBIDIRECTEXEC; 868 869 /*---- Map the columns ----*/ 870 proc datasets lib = work nolist nowarn memtype = (data view); 871 delete W5OB6C0P; 872 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27816.00k Timestamp 08/09/2016 04:55:46 PM Step Count 28 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 873 874 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 875 proc sql; 876 create view work.W5OB6C0P as 877 select 878 CFCIF_2 879 label = 'CFCIF_2', 880 CFASEQ 881 label = 'CFASEQ', 882 (compress(cfapty)) as CFAPTY length = 5 883 format = $5. 884 informat = $5. 885 label = 'CFAPTY', 886 CF2DT2 887 label = 'CF2DT2', 888 CF2PRV 889 label = 'CF2PRV', 890 (case cfstsn when 0 then 0 891 when 1900 then 0 892 else YEAR(&curr_date)-cfstsn end) as CFSTSN length = 8 893 format = 5. 894 informat = 5. 895 label = 'CFSTSN' 896 from &SYSLAST 897 where cfuse = 'Y' and cfinvc = 'J' 898 ; NOTE: SQL view WORK.W5OB6C0P has been defined. 899 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds 25 The SAS System 16:54 Tuesday, August 9, 2016 memory 88.21k OS Memory 27816.00k Timestamp 08/09/2016 04:55:46 PM Step Count 29 Switch Count 119 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 406 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 900 901 %let SYSLAST = work.W5OB6C0P; 902 903 %global etls_sql_pushDown; 904 %let etls_sql_pushDown = &sys_sql_ip_all; 905 906 %rcSet(&sqlrc); 907 908 909 910 /** Step end SAS Extract **/ 911 912 /*==========================================================================* 913 * Step: SQL Join A59T10QB.BP0004UF * 914 * Transform: Join * 915 * Description: * 916 * * 917 * Source Tables: Extract Target - work.W5OB6C0P A59T10QB.BK0001M3 * 918 * SAS_CFZPPT_V - gpads.SAS_CFZPPT_V A59T10QB.BG0000TB * 919 * Target Table: SQL Target - work.W5T0VF2V A59T10QB.BK0001M4 * 920 *==========================================================================*/ 921 922 %let transformID = %quote(A59T10QB.BP0004UF); 923 %let trans_rc = 0; 924 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 925 926 %global etls_sql_pushDown; 927 %let etls_sql_pushDown = -1; 928 option DBIDIRECTEXEC; 929 930 proc datasets lib = work nolist nowarn memtype = (data view); 931 delete W5T0VF2V; 932 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27816.00k Timestamp 08/09/2016 04:55:46 PM Step Count 30 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 26 The SAS System 16:54 Tuesday, August 9, 2016 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 933 934 proc sql; 935 create view work.W5T0VF2V as 936 select 937 W5OB6C0P.CFCIF_2 length = 8 938 format = 20. 939 informat = 20. 940 label = 'CFCIF_2', 941 W5OB6C0P.CFASEQ length = 8 942 format = 4. 943 informat = 4. 944 label = 'CFASEQ', 945 W5OB6C0P.CFAPTY || ' - ' || SAS_CFZPPT_V.cfadpd as CFAPTY length = 50 946 format = $50. 947 informat = $50. 948 label = 'CFAPTY', 949 W5OB6C0P.CF2DT2 length = 40 950 format = $40. 951 informat = $40. 952 label = 'CF2DT2', 953 W5OB6C0P.CF2PRV length = 40 954 format = $40. 955 informat = $40. 956 label = 'CF2PRV', 957 W5OB6C0P.CFSTSN length = 8 958 format = 5. 959 informat = 5. 960 label = 'CFSTSN' 961 from 962 work.W5OB6C0P left join 963 gpads.SAS_CFZPPT_V 964 on 965 ( 966 W5OB6C0P.CFAPTY = SAS_CFZPPT_V.cfadpc 967 ) 968 ; NOTE: SQL view WORK.W5T0VF2V has been defined. 969 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 147.78k OS Memory 27816.00k Timestamp 08/09/2016 04:55:46 PM Step Count 31 Switch Count 159 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 582 Involuntary Context Switches 0 Block Input Operations 0 27 The SAS System 16:54 Tuesday, August 9, 2016 Block Output Operations 32 970 971 %global etls_sql_pushDown; 972 %let etls_sql_pushDown = &sys_sql_ip_all; 973 974 %rcSet(&sqlrc); 975 976 977 978 /** Step end SQL Join **/ 979 980 /*==========================================================================* 981 * Step: SAS Splitter A59T10QB.BP0004UG * 982 * Transform: Splitter * 983 * Description: * 984 * * 985 * Source Table: SQL Target - work.W5T0VF2V A59T10QB.BK0001M4 * 986 * Target Tables: Splitter Target 1 - work.W5OB6DH5 A59T10QB.BK0001M5 * 987 * Splitter Target 2 - work.W5OB6DHF A59T10QB.BK0001M6 * 988 *==========================================================================*/ 989 990 %let transformID = %quote(A59T10QB.BP0004UG); 991 %let trans_rc = 0; 992 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 993 994 %let SYSLAST = %nrquote(work.W5T0VF2V); 995 996 proc datasets lib=work nolist nowarn memtype = (data view); 997 delete W5OB6DH5; 998 delete W5OB6DHF; 999 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27816.00k Timestamp 08/09/2016 04:55:46 PM Step Count 32 Switch Count 30 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 64 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1000 1001 data 1002 work.W5OB6DH5 1003 work.W5OB6DHF; 1004 set &SYSLAST; 1005 output work.W5OB6DH5; 1006 output work.W5OB6DHF; 28 The SAS System 16:54 Tuesday, August 9, 2016 1007 run; NOTE: There were 2267900 observations read from the data set GPADS.SAS_CFADDR_V. WHERE (cfuse='Y') and (cfinvc='J'); NOTE: There were 13 observations read from the data set GPADS.SAS_CFZPPT_V. NOTE: There were 2267900 observations read from the data set WORK.W5T0VF2V. NOTE: The data set WORK.W5OB6DH5 has 2267900 observations and 6 variables. NOTE: Compressing data set WORK.W5OB6DH5 decreased size by 42.26 percent. Compressed is 3202 pages; un-compressed would require 5546 pages. NOTE: The data set WORK.W5OB6DHF has 2267900 observations and 6 variables. NOTE: Compressing data set WORK.W5OB6DHF decreased size by 42.26 percent. Compressed is 3202 pages; un-compressed would require 5546 pages. NOTE: DATA statement used (Total process time): real time 14.56 seconds user cpu time 10.69 seconds system cpu time 0.82 seconds memory 306576.56k OS Memory 331580.00k Timestamp 08/09/2016 04:56:01 PM Step Count 33 Switch Count 64 Page Faults 0 Page Reclaims 19005 Page Swaps 0 Voluntary Context Switches 15433 Involuntary Context Switches 29 Block Input Operations 0 Block Output Operations 1316480 1008 1009 %rcSet(&syserr); 1010 1011 /*---- Map the columns ----*/ 1012 proc datasets lib = work nolist nowarn memtype = (data view); 1013 delete etls_mapped; 1014 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27816.00k Timestamp 08/09/2016 04:56:01 PM Step Count 34 Switch Count 26 Page Faults 0 Page Reclaims 5 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1015 1016 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 1017 proc sql; 29 The SAS System 16:54 Tuesday, August 9, 2016 1018 create table work.etls_mapped as 1019 select 1020 CFCIF_2 1021 label = 'CFCIF#', 1022 CFASEQ, 1023 CFAPTY, 1024 CF2DT2, 1025 CF2PRV, 1026 CFSTSN 1027 from work.W5OB6DH5 1028 ; NOTE: Compressing data set WORK.ETLS_MAPPED decreased size by 42.26 percent. Compressed is 3202 pages; un-compressed would require 5546 pages. NOTE: Table WORK.ETLS_MAPPED created, with 2267900 rows and 6 columns. 1029 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.16 seconds user cpu time 2.00 seconds system cpu time 0.16 seconds memory 6423.96k OS Memory 33200.00k Timestamp 08/09/2016 04:56:03 PM Step Count 35 Switch Count 77 Page Faults 0 Page Reclaims 319 Page Swaps 0 Voluntary Context Switches 254 Involuntary Context Switches 3 Block Input Operations 0 Block Output Operations 409984 1030 1031 %let SYSLAST = work.etls_mapped; 1032 1033 data work.W5OB6DH5; 1034 set work.etls_mapped; 1035 run; NOTE: There were 2267900 observations read from the data set WORK.ETLS_MAPPED. NOTE: The data set WORK.W5OB6DH5 has 2267900 observations and 6 variables. NOTE: Compressing data set WORK.W5OB6DH5 decreased size by 42.26 percent. Compressed is 3202 pages; un-compressed would require 5546 pages. NOTE: DATA statement used (Total process time): real time 1.99 seconds user cpu time 1.78 seconds system cpu time 0.21 seconds memory 2629.37k OS Memory 29100.00k Timestamp 08/09/2016 04:56:05 PM Step Count 36 Switch Count 35 Page Faults 0 Page Reclaims 408 Page Swaps 0 Voluntary Context Switches 87 Involuntary Context Switches 5 Block Input Operations 0 30 The SAS System 16:54 Tuesday, August 9, 2016 Block Output Operations 409984 1036 1037 proc datasets lib = work nolist nowarn memtype = (data view); 1038 delete etls_mapped; 1039 quit; NOTE: Deleting WORK.ETLS_MAPPED (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.03 seconds user cpu time 0.00 seconds system cpu time 0.03 seconds memory 228.87k OS Memory 28072.00k Timestamp 08/09/2016 04:56:05 PM Step Count 37 Switch Count 29 Page Faults 0 Page Reclaims 6 Page Swaps 0 Voluntary Context Switches 61 Involuntary Context Switches 4 Block Input Operations 0 Block Output Operations 8 1040 1041 %rcSet(&syscc); 1042 1043 /*---- Map the columns ----*/ 1044 proc datasets lib = work nolist nowarn memtype = (data view); 1045 delete etls_mapped; 1046 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27816.00k Timestamp 08/09/2016 04:56:05 PM Step Count 38 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1047 1048 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 1049 proc sql; 1050 create table work.etls_mapped as 1051 select 1052 CFCIF_2 31 The SAS System 16:54 Tuesday, August 9, 2016 1053 label = 'CFCIF#', 1054 CFASEQ, 1055 CFAPTY, 1056 CF2DT2, 1057 CF2PRV, 1058 CFSTSN 1059 from work.W5OB6DHF 1060 ; NOTE: Compressing data set WORK.ETLS_MAPPED decreased size by 42.26 percent. Compressed is 3202 pages; un-compressed would require 5546 pages. NOTE: Table WORK.ETLS_MAPPED created, with 2267900 rows and 6 columns. 1061 quit; NOTE: PROCEDURE SQL used (Total process time): real time 2.17 seconds user cpu time 2.01 seconds system cpu time 0.16 seconds memory 6423.96k OS Memory 33200.00k Timestamp 08/09/2016 04:56:07 PM Step Count 39 Switch Count 85 Page Faults 0 Page Reclaims 319 Page Swaps 0 Voluntary Context Switches 275 Involuntary Context Switches 2 Block Input Operations 0 Block Output Operations 409984 1062 1063 %let SYSLAST = work.etls_mapped; 1064 1065 data work.W5OB6DHF; 1066 set work.etls_mapped; 1067 run; NOTE: There were 2267900 observations read from the data set WORK.ETLS_MAPPED. NOTE: The data set WORK.W5OB6DHF has 2267900 observations and 6 variables. NOTE: Compressing data set WORK.W5OB6DHF decreased size by 42.26 percent. Compressed is 3202 pages; un-compressed would require 5546 pages. NOTE: DATA statement used (Total process time): real time 1.98 seconds user cpu time 1.79 seconds system cpu time 0.20 seconds memory 2629.12k OS Memory 29100.00k Timestamp 08/09/2016 04:56:09 PM Step Count 40 Switch Count 35 Page Faults 0 Page Reclaims 400 Page Swaps 0 Voluntary Context Switches 86 Involuntary Context Switches 4 Block Input Operations 0 Block Output Operations 409984 32 The SAS System 16:54 Tuesday, August 9, 2016 1068 1069 proc datasets lib = work nolist nowarn memtype = (data view); 1070 delete etls_mapped; 1071 quit; NOTE: Deleting WORK.ETLS_MAPPED (memtype=DATA). NOTE: PROCEDURE DATASETS used (Total process time): real time 0.03 seconds user cpu time 0.00 seconds system cpu time 0.03 seconds memory 229.87k OS Memory 28072.00k Timestamp 08/09/2016 04:56:09 PM Step Count 41 Switch Count 29 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 61 Involuntary Context Switches 4 Block Input Operations 0 Block Output Operations 8 1072 1073 %rcSet(&syscc); 1074 1075 1076 1077 /** Step end SAS Splitter **/ 1078 1079 /*==========================================================================* 1080 * Step: SAS Extract A59T10QB.BP0004UH * 1081 * Transform: Extract * 1082 * Description: * 1083 * * 1084 * Source Table: Splitter Target 1 - work.W5OB6DH5 A59T10QB.BK0001M5 * 1085 * Target Table: Extract Target - work.W5OB6DLG A59T10QB.BK0001M7 * 1086 *==========================================================================*/ 1087 1088 %let transformID = %quote(A59T10QB.BP0004UH); 1089 %let trans_rc = 0; 1090 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1091 1092 %let SYSLAST = %nrquote(work.W5OB6DH5); 1093 1094 %global etls_sql_pushDown; 1095 %let etls_sql_pushDown = -1; 1096 option DBIDIRECTEXEC; 1097 1098 /*---- Map the columns ----*/ 1099 proc datasets lib = work nolist nowarn memtype = (data view); 1100 delete W5OB6DLG; 1101 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds 33 The SAS System 16:54 Tuesday, August 9, 2016 memory 228.87k OS Memory 27816.00k Timestamp 08/09/2016 04:56:09 PM Step Count 42 Switch Count 30 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1102 1103 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 1104 proc sql; 1105 create view work.W5OB6DLG as 1106 select 1107 CFCIF_2, 1108 (max(CFASEQ )) as CFASEQ length = 8 1109 format = 4. 1110 informat = 4. 1111 label = 'CFASEQ' 1112 from &SYSLAST 1113 group by 1114 CFCIF_2 1115 ; NOTE: SQL view WORK.W5OB6DLG has been defined. 1116 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 83.59k OS Memory 27560.00k Timestamp 08/09/2016 04:56:09 PM Step Count 43 Switch Count 67 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 213 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1117 1118 %let SYSLAST = work.W5OB6DLG; 1119 1120 %global etls_sql_pushDown; 1121 %let etls_sql_pushDown = &sys_sql_ip_all; 1122 1123 %rcSet(&sqlrc); 1124 1125 1126 1127 /** Step end SAS Extract **/ 34 The SAS System 16:54 Tuesday, August 9, 2016 1128 1129 /*==========================================================================* 1130 * Step: SQL Join A59T10QB.BP0004UI * 1131 * Transform: Join * 1132 * Description: * 1133 * * 1134 * Source Tables: Extract Target - work.W5OB6DLG A59T10QB.BK0001M7 * 1135 * Splitter Target 2 - work.W5OB6DHF A59T10QB.BK0001M6 * 1136 * Target Table: SQL Target - work.WCFADDR A59T10QB.BK0001M8 * 1137 *==========================================================================*/ 1138 1139 %let transformID = %quote(A59T10QB.BP0004UI); 1140 %let trans_rc = 0; 1141 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1142 1143 %global etls_sql_pushDown; 1144 %let etls_sql_pushDown = -1; 1145 option DBIDIRECTEXEC; 1146 1147 proc datasets lib = work nolist nowarn memtype = (data view); 1148 delete WCFADDR; 1149 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 229.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:09 PM Step Count 44 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1150 1151 proc sql; 1152 create view work.WCFADDR as 1153 select 1154 W5OB6DHF.CFAPTY length = 50 1155 format = $50. 1156 informat = $50. 1157 label = 'CFAPTY', 1158 compress(put(W5OB6DLG.CFCIF_2 , 20.)) as CFCIF_2 length = 20 1159 format = $20. 1160 label = 'CFCIF_2', 1161 W5OB6DHF.CF2DT2 length = 40 1162 format = $40. 1163 informat = $40. 1164 label = 'CF2DT2', 1165 W5OB6DHF.CF2PRV length = 40 1166 format = $40. 1167 informat = $40. 35 The SAS System 16:54 Tuesday, August 9, 2016 1168 label = 'CF2PRV', 1169 W5OB6DHF.CFSTSN length = 8 1170 format = 5. 1171 informat = 5. 1172 label = 'CFSTSN' 1173 from 1174 work.W5OB6DLG, 1175 work.W5OB6DHF 1176 where 1177 W5OB6DLG.CFCIF_2 = W5OB6DHF.CFCIF_2 1178 and W5OB6DLG.CFASEQ = W5OB6DHF.CFASEQ 1179 ; NOTE: SQL view WORK.WCFADDR has been defined. 1180 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 154.90k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 45 Switch Count 139 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 504 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1181 1182 %global etls_sql_pushDown; 1183 %let etls_sql_pushDown = &sys_sql_ip_all; 1184 1185 %rcSet(&sqlrc); 1186 1187 1188 1189 /** Step end SQL Join **/ 1190 1191 /*==========================================================================* 1192 * Step: SAS Splitter A59T10QB.BP0004UJ * 1193 * Transform: Splitter * 1194 * Description: * 1195 * * 1196 * Source Table: SAS_CFPAR3_V - gpads.SAS_CFPAR3_V A59T10QB.BG0000T7 * 1197 * Target Tables: Splitter Target 1 - work.W5O7ZDVI A59T10QB.BK0001M9 * 1198 * Splitter Target 2 - work.W5O7ZDVS A59T10QB.BK0001MA * 1199 * SAS Splitter - work.W5OB570T A59T10QB.BK0001MB * 1200 *==========================================================================*/ 1201 1202 %let transformID = %quote(A59T10QB.BP0004UJ); 1203 %let trans_rc = 0; 1204 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1205 1206 %let SYSLAST = %nrquote(gpads.SAS_CFPAR3_V); 1207 36 The SAS System 16:54 Tuesday, August 9, 2016 1208 proc datasets lib=work nolist nowarn memtype = (data view); 1209 delete W5O7ZDVI; 1210 delete W5O7ZDVS; 1211 delete W5OB570T; 1212 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 229.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 46 Switch Count 38 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 96 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1213 1214 data 1215 work.W5O7ZDVI 1216 work.W5O7ZDVS 1217 work.W5OB570T ; 1218 set &SYSLAST; 1219 output work.W5O7ZDVI; 1220 output work.W5O7ZDVS; 1221 output work.W5OB570T; 1222 run; NOTE: There were 63 observations read from the data set GPADS.SAS_CFPAR3_V. NOTE: The data set WORK.W5O7ZDVI has 63 observations and 5 variables. NOTE: Compressing data set WORK.W5O7ZDVI decreased size by 0.00 percent. Compressed is 1 pages; un-compressed would require 1 pages. NOTE: The data set WORK.W5O7ZDVS has 63 observations and 5 variables. NOTE: Compressing data set WORK.W5O7ZDVS decreased size by 0.00 percent. Compressed is 1 pages; un-compressed would require 1 pages. NOTE: The data set WORK.W5OB570T has 63 observations and 5 variables. NOTE: Compressing data set WORK.W5OB570T decreased size by 0.00 percent. Compressed is 1 pages; un-compressed would require 1 pages. NOTE: DATA statement used (Total process time): real time 0.19 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 855.93k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 47 Switch Count 69 Page Faults 0 Page Reclaims 204 Page Swaps 0 Voluntary Context Switches 231 Involuntary Context Switches 0 Block Input Operations 0 37 The SAS System 16:54 Tuesday, August 9, 2016 Block Output Operations 768 1223 1224 %rcSet(&syserr); 1225 1226 1227 1228 /** Step end SAS Splitter **/ 1229 1230 /*==========================================================================* 1231 * Step: SAS Extract A59T10QB.BP0004UK * 1232 * Transform: Extract * 1233 * Description: * 1234 * * 1235 * Source Table: SAS Splitter - work.W5OB570T A59T10QB.BK0001MB * 1236 * Target Table: Extract Target - work.W5OB57RL A59T10QB.BK0001MC * 1237 *==========================================================================*/ 1238 1239 %let transformID = %quote(A59T10QB.BP0004UK); 1240 %let trans_rc = 0; 1241 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1242 1243 %let SYSLAST = %nrquote(work.W5OB570T); 1244 1245 %global etls_sql_pushDown; 1246 %let etls_sql_pushDown = -1; 1247 option DBIDIRECTEXEC; 1248 1249 /*---- Map the columns ----*/ 1250 proc datasets lib = work nolist nowarn memtype = (data view); 1251 delete W5OB57RL; 1252 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 48 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 49 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1253 1254 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 1255 proc sql; 1256 create view work.W5OB57RL as 1257 select 1258 CP3UCD 38 The SAS System 16:54 Tuesday, August 9, 2016 1259 label = 'CP3UCD', 1260 CP3DSC 1261 label = 'CP3DSC' 1262 from &SYSLAST 1263 where trim(left(cp3uic)) = '4' 1264 ; NOTE: SQL view WORK.W5OB57RL has been defined. 1265 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 93.68k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 49 Switch Count 63 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 199 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1266 1267 %let SYSLAST = work.W5OB57RL; 1268 1269 %global etls_sql_pushDown; 1270 %let etls_sql_pushDown = &sys_sql_ip_all; 1271 1272 %rcSet(&sqlrc); 1273 1274 1275 1276 /** Step end SAS Extract **/ 1277 1278 /*==========================================================================* 1279 * Step: SAS Extract A59T10QB.BP0004UL * 1280 * Transform: Extract * 1281 * Description: * 1282 * * 1283 * Source Table: Splitter Target 2 - work.W5O7ZDVS A59T10QB.BK0001MA * 1284 * Target Table: Extract Target - work.W5O7ZH2B A59T10QB.BK0001MD * 1285 *==========================================================================*/ 1286 1287 %let transformID = %quote(A59T10QB.BP0004UL); 1288 %let trans_rc = 0; 1289 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1290 1291 %let SYSLAST = %nrquote(work.W5O7ZDVS); 1292 1293 %global etls_sql_pushDown; 1294 %let etls_sql_pushDown = -1; 1295 option DBIDIRECTEXEC; 1296 1297 /*---- Map the columns ----*/ 1298 proc datasets lib = work nolist nowarn memtype = (data view); 39 The SAS System 16:54 Tuesday, August 9, 2016 1299 delete W5O7ZH2B; 1300 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 50 Switch Count 30 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 65 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1301 1302 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 1303 proc sql; 1304 create view work.W5O7ZH2B as 1305 select 1306 CP3UCD 1307 label = 'CP3UCD', 1308 CP3DSC 1309 label = 'CP3DSC', 1310 cp3rid, 1311 cp3uic, 1312 cp3uid 1313 from &SYSLAST 1314 where trim(left(cp3uic)) = '4' 1315 ; NOTE: SQL view WORK.W5O7ZH2B has been defined. 1316 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 83.03k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 51 Switch Count 71 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 229 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1317 1318 %let SYSLAST = work.W5O7ZH2B; 1319 40 The SAS System 16:54 Tuesday, August 9, 2016 1320 %global etls_sql_pushDown; 1321 %let etls_sql_pushDown = &sys_sql_ip_all; 1322 1323 %rcSet(&sqlrc); 1324 1325 1326 1327 /** Step end SAS Extract **/ 1328 1329 /*==========================================================================* 1330 * Step: SAS Extract A59T10QB.BP0004UM * 1331 * Transform: Extract * 1332 * Description: * 1333 * * 1334 * Source Table: Splitter Target 1 - work.W5O7ZDVI A59T10QB.BK0001M9 * 1335 * Target Table: Extract Target - work.W5O7ZFT3 A59T10QB.BK0001ME * 1336 *==========================================================================*/ 1337 1338 %let transformID = %quote(A59T10QB.BP0004UM); 1339 %let trans_rc = 0; 1340 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1341 1342 %let SYSLAST = %nrquote(work.W5O7ZDVI); 1343 1344 %global etls_sql_pushDown; 1345 %let etls_sql_pushDown = -1; 1346 option DBIDIRECTEXEC; 1347 1348 /*---- Map the columns ----*/ 1349 proc datasets lib = work nolist nowarn memtype = (data view); 1350 delete W5O7ZFT3; 1351 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 52 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1352 1353 %put %str(NOTE: Mapping columns ...); NOTE: Mapping columns ... 1354 proc sql; 1355 create view work.W5O7ZFT3 as 1356 select 1357 CP3UCD 1358 label = 'CP3UCD', 41 The SAS System 16:54 Tuesday, August 9, 2016 1359 CP3DSC 1360 label = 'CP3DSC', 1361 cp3rid, 1362 cp3uic, 1363 cp3uid 1364 from &SYSLAST 1365 where trim(left(cp3uic)) = '2' 1366 ; NOTE: SQL view WORK.W5O7ZFT3 has been defined. 1367 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 92.65k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 53 Switch Count 75 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 245 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1368 1369 %let SYSLAST = work.W5O7ZFT3; 1370 1371 %global etls_sql_pushDown; 1372 %let etls_sql_pushDown = &sys_sql_ip_all; 1373 1374 %rcSet(&sqlrc); 1375 1376 1377 1378 /** Step end SAS Extract **/ 1379 1380 /*==========================================================================* 1381 * Step: SQL Join A59T10QB.BP0004UN * 1382 * Transform: Join * 1383 * Description: * 1384 * * 1385 * Source Tables: Extract Target - work.W5O5KJH1 A59T10QB.BK0001LV * 1386 * Extract Target - work.W5O7ZFT3 A59T10QB.BK0001ME * 1387 * Target Table: SQL Target - work.W5O7ZEO6 A59T10QB.BK0001MF * 1388 *==========================================================================*/ 1389 1390 %let transformID = %quote(A59T10QB.BP0004UN); 1391 %let trans_rc = 0; 1392 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1393 1394 %global etls_sql_pushDown; 1395 %let etls_sql_pushDown = -1; 1396 option DBIDIRECTEXEC; 1397 1398 proc datasets lib = work nolist nowarn memtype = (data view); 42 The SAS System 16:54 Tuesday, August 9, 2016 1399 delete W5O7ZEO6; 1400 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 54 Switch Count 30 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 64 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1401 1402 proc sql; 1403 create view work.W5O7ZEO6 as 1404 select 1405 W5O5KJH1.customer_id length = 40 1406 format = $40. 1407 label = 'Customer number', 1408 W5O5KJH1.marital_status as marital_status_code length = 1 1409 format = $1. 1410 label = 'Marital Status Code', 1411 W5O5KJH1.customer_age length = 8 1412 label = 'Customer Age', 1413 W5O5KJH1.period length = 8 1414 format = ddmmyy10. 1415 label = 'Period', 1416 W5O5KJH1.customer_gender length = 1 1417 format = $1. 1418 label = 'Customer Gender', 1419 W5O5KJH1.number_of_dependents_code length = 1 1420 format = $1. 1421 label = 'Number of Dependents Code', 1422 W5O7ZFT3.CP3DSC as marital_status_desc length = 20 1423 label = 'Marital Status Description', 1424 W5O5KJH1.job_status_code length = 1 1425 label = 'Job Status Code', 1426 W5O5KJH1.CFSNME length = 20 1427 format = $20. 1428 informat = $20. 1429 label = 'CFSNME', 1430 W5O5KJH1.CFCLAS length = 20 1431 format = $20. 1432 informat = $20. 1433 label = 'CFCLAS', 1434 W5O5KJH1.CFSSCD length = 4 1435 format = $4. 1436 informat = $4. 1437 label = 'CFSSCD', 1438 W5O5KJH1.CFSSNO length = 40 43 The SAS System 16:54 Tuesday, August 9, 2016 1439 format = $40. 1440 informat = $40. 1441 label = 'CFSSNO' 1442 from 1443 work.W5O5KJH1 left join 1444 work.W5O7ZFT3 1445 on 1446 ( 1447 W5O5KJH1.marital_status = W5O7ZFT3.CP3UCD 1448 ) 1449 ; NOTE: SQL view WORK.W5O7ZEO6 has been defined. 1450 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 164.34k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 55 Switch Count 215 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 811 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1451 1452 %global etls_sql_pushDown; 1453 %let etls_sql_pushDown = &sys_sql_ip_all; 1454 1455 %rcSet(&sqlrc); 1456 1457 1458 1459 /** Step end SQL Join **/ 1460 1461 /*==========================================================================* 1462 * Step: SQL Join A59T10QB.BP0004UO * 1463 * Transform: Join * 1464 * Description: * 1465 * * 1466 * Source Tables: SQL Target - work.W5O7ZEO6 A59T10QB.BK0001MF * 1467 * Extract Target - work.W5O7ZH2B A59T10QB.BK0001MD * 1468 * Target Table: SQL Target - work.WCUST60 A59T10QB.BK0001MG * 1469 *==========================================================================*/ 1470 1471 %let transformID = %quote(A59T10QB.BP0004UO); 1472 %let trans_rc = 0; 1473 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1474 1475 %global etls_sql_pushDown; 1476 %let etls_sql_pushDown = -1; 1477 option DBIDIRECTEXEC; 1478 44 The SAS System 16:54 Tuesday, August 9, 2016 1479 proc datasets lib = work nolist nowarn memtype = (data view); 1480 delete WCUST60; 1481 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 56 Switch Count 30 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 64 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1482 1483 proc sql; 1484 create view work.WCUST60 as 1485 select 1486 W5O7ZEO6.customer_id length = 40 1487 format = $40. 1488 label = 'Customer number', 1489 W5O7ZEO6.marital_status_code length = 1 1490 format = $1. 1491 label = 'Marital Status Code', 1492 W5O7ZEO6.customer_age length = 8 1493 label = 'Customer Age', 1494 W5O7ZEO6.period length = 8 1495 format = ddmmyy10. 1496 label = 'Period', 1497 W5O7ZEO6.customer_gender length = 1 1498 format = $1. 1499 label = 'Customer Gender', 1500 W5O7ZEO6.number_of_dependents_code length = 1 1501 format = $1. 1502 label = 'Number of Dependents Code', 1503 W5O7ZEO6.marital_status_desc length = 20 1504 label = 'Marital Status Description', 1505 W5O7ZH2B.CP3DSC as number_of_dependents_desc length = 20 1506 label = 'Number of Dependents Description', 1507 W5O7ZEO6.job_status_code length = 1 1508 label = 'Job Status Code', 1509 W5O7ZEO6.CFSNME length = 20 1510 format = $20. 1511 informat = $20. 1512 label = 'CFSNME', 1513 W5O7ZEO6.CFCLAS length = 20 1514 format = $20. 1515 informat = $20. 1516 label = 'CFCLAS', 1517 W5O7ZEO6.CFSSCD length = 4 1518 format = $4. 45 The SAS System 16:54 Tuesday, August 9, 2016 1519 informat = $4. 1520 label = 'CFSSCD', 1521 W5O7ZEO6.CFSSNO length = 40 1522 format = $40. 1523 informat = $40. 1524 label = 'CFSSNO' 1525 from 1526 work.W5O7ZEO6 left join 1527 work.W5O7ZH2B 1528 on 1529 ( 1530 W5O7ZEO6.number_of_dependents_code = W5O7ZH2B.CP3UCD 1531 ) 1532 ; NOTE: SQL view WORK.WCUST60 has been defined. 1533 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 151.15k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 57 Switch Count 223 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 840 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1534 1535 %global etls_sql_pushDown; 1536 %let etls_sql_pushDown = &sys_sql_ip_all; 1537 1538 %rcSet(&sqlrc); 1539 1540 1541 1542 /** Step end SQL Join **/ 1543 1544 /*==========================================================================* 1545 * Step: SQL Join A59T10QB.BP0004UP * 1546 * Transform: Join * 1547 * Description: * 1548 * * 1549 * Source Tables: SQL Target - work.WCUST60 A59T10QB.BK0001MG * 1550 * Extract Target - work.W5OB57RL A59T10QB.BK0001MC * 1551 * Target Table: SQL Target - work.WCUST70 A59T10QB.BK0001MH * 1552 *==========================================================================*/ 1553 1554 %let transformID = %quote(A59T10QB.BP0004UP); 1555 %let trans_rc = 0; 1556 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1557 1558 %global etls_sql_pushDown; 46 The SAS System 16:54 Tuesday, August 9, 2016 1559 %let etls_sql_pushDown = -1; 1560 option DBIDIRECTEXEC; 1561 1562 proc datasets lib = work nolist nowarn memtype = (data view); 1563 delete WCUST70; 1564 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 58 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1565 1566 proc sql; 1567 create view work.WCUST70 as 1568 select 1569 WCUST60.customer_id length = 40 1570 format = $40. 1571 label = 'Customer number', 1572 WCUST60.marital_status_code length = 1 1573 format = $1. 1574 label = 'Marital Status Code', 1575 WCUST60.customer_age length = 8 1576 label = 'Customer Age', 1577 WCUST60.period length = 8 1578 format = ddmmyy10. 1579 label = 'Period', 1580 WCUST60.customer_gender length = 1 1581 format = $1. 1582 label = 'Customer Gender', 1583 WCUST60.number_of_dependents_code length = 1 1584 format = $1. 1585 label = 'Number of Dependents Code', 1586 WCUST60.marital_status_desc length = 20 1587 label = 'Marital Status Description', 1588 WCUST60.number_of_dependents_desc length = 20 1589 label = 'Number of Dependents Description', 1590 WCUST60.job_status_code length = 1 1591 format = $5. 1592 informat = $5. 1593 label = 'Job Status Code', 1594 W5OB57RL.CP3DSC as job_status_desc length = 20 1595 label = 'Job Status Description', 1596 WCUST60.CFSNME length = 20 1597 format = $20. 1598 informat = $20. 47 The SAS System 16:54 Tuesday, August 9, 2016 1599 label = 'CFSNME', 1600 WCUST60.CFCLAS length = 20 1601 format = $20. 1602 informat = $20. 1603 label = 'CFCLAS', 1604 WCUST60.CFSSCD length = 4 1605 format = $4. 1606 informat = $4. 1607 label = 'CFSSCD', 1608 WCUST60.CFSSNO length = 40 1609 format = $40. 1610 informat = $40. 1611 label = 'CFSSNO' 1612 from 1613 work.WCUST60 left join 1614 work.W5OB57RL 1615 on 1616 ( 1617 WCUST60.job_status_code = W5OB57RL.CP3UCD 1618 ) 1619 ; NOTE: SQL view WORK.WCUST70 has been defined. 1620 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.02 seconds system cpu time 0.00 seconds memory 157.25k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 59 Switch Count 239 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 902 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 32 1621 1622 %global etls_sql_pushDown; 1623 %let etls_sql_pushDown = &sys_sql_ip_all; 1624 1625 %rcSet(&sqlrc); 1626 1627 1628 1629 /** Step end SQL Join **/ 1630 1631 /*==========================================================================* 1632 * Step: SQL Join A59T10QB.BP0004UQ * 1633 * Transform: Join * 1634 * Description: * 1635 * * 1636 * Source Tables: SQL Target - work.WCUST70 A59T10QB.BK0001MH * 1637 * SQL Target - work.WCFADDR A59T10QB.BK0001M8 * 1638 * Target Table: SQL Target - work.WCUST80 A59T10QB.BK0001MI * 48 The SAS System 16:54 Tuesday, August 9, 2016 1639 *==========================================================================*/ 1640 1641 %let transformID = %quote(A59T10QB.BP0004UQ); 1642 %let trans_rc = 0; 1643 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1644 1645 %global etls_sql_pushDown; 1646 %let etls_sql_pushDown = -1; 1647 option DBIDIRECTEXEC; 1648 1649 proc datasets lib = work nolist nowarn memtype = (data view); 1650 delete WCUST80; 1651 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 60 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1652 1653 proc sql; 1654 create view work.WCUST80 as 1655 select 1656 WCUST70.customer_id length = 40 1657 format = $40. 1658 label = 'Customer number', 1659 WCUST70.marital_status_code length = 1 1660 format = $1. 1661 label = 'Marital Status Code', 1662 WCUST70.customer_age length = 8 1663 label = 'Customer Age', 1664 WCUST70.period length = 8 1665 format = ddmmyy10. 1666 label = 'Period', 1667 WCUST70.customer_gender length = 1 1668 format = $1. 1669 label = 'Customer Gender', 1670 WCUST70.number_of_dependents_code length = 1 1671 format = $1. 1672 label = 'Number of Dependents Code', 1673 WCUST70.marital_status_desc length = 20 1674 label = 'Marital Status Description', 1675 WCUST70.number_of_dependents_desc length = 20 1676 label = 'Number of Dependents Description', 1677 WCUST70.job_status_code length = 1 1678 format = $5. 49 The SAS System 16:54 Tuesday, August 9, 2016 1679 label = 'Job Status Code', 1680 WCUST70.job_status_desc length = 20 1681 label = 'Job Status Description', 1682 WCFADDR.CFAPTY as resident_status length = 50 1683 format = $50. 1684 label = 'Resident Status', 1685 WCFADDR.CF2DT2 as dati_2 length = 40 1686 format = $40. 1687 informat = $40. 1688 label = 'DATI II', 1689 WCFADDR.CF2PRV as province length = 40 1690 format = $40. 1691 informat = $40. 1692 label = 'Province', 1693 WCFADDR.CFSTSN as year_live_in_resident length = 8 1694 format = 5. 1695 informat = 5. 1696 label = 'Year Live In Resident', 1697 WCUST70.CFSNME as customer_name length = 20 1698 format = $20. 1699 informat = $20. 1700 label = 'customer_name', 1701 WCUST70.CFCLAS as customer_classification length = 20 1702 format = $20. 1703 informat = $20. 1704 label = 'customer_classification', 1705 WCUST70.CFSSCD as identity_type length = 4 1706 format = $4. 1707 informat = $4. 1708 label = 'identity_type', 1709 WCUST70.CFSSNO as identity_id length = 40 1710 format = $40. 1711 informat = $40. 1712 label = 'identity_id' 1713 from 1714 work.WCUST70 left join 1715 work.WCFADDR 1716 on 1717 ( 1718 WCUST70.customer_id = WCFADDR.CFCIF_2 1719 ) 1720 ; NOTE: SQL view WORK.WCUST80 has been defined. 1721 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.01 seconds memory 246.71k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 61 Switch Count 295 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 1128 Involuntary Context Switches 0 Block Input Operations 0 50 The SAS System 16:54 Tuesday, August 9, 2016 Block Output Operations 48 1722 1723 %global etls_sql_pushDown; 1724 %let etls_sql_pushDown = &sys_sql_ip_all; 1725 1726 %rcSet(&sqlrc); 1727 1728 1729 1730 /** Step end SQL Join **/ 1731 1732 /*==========================================================================* 1733 * Step: SQL Join A59T10QB.BP0004UR * 1734 * Transform: Join * 1735 * Description: * 1736 * * 1737 * Source Tables: SQL Target - work.WCUST80 A59T10QB.BK0001MI * 1738 * SQL Target - work.WCFZEMP A59T10QB.BK0001M2 * 1739 * Target Table: SQL Target - work.WCUST90 A59T10QB.BK0001MJ * 1740 *==========================================================================*/ 1741 1742 %let transformID = %quote(A59T10QB.BP0004UR); 1743 %let trans_rc = 0; 1744 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1745 1746 %global etls_sql_pushDown; 1747 %let etls_sql_pushDown = -1; 1748 option DBIDIRECTEXEC; 1749 1750 proc datasets lib = work nolist nowarn memtype = (data view); 1751 delete WCUST90; 1752 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 228.87k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 62 Switch Count 26 Page Faults 0 Page Reclaims 2 Page Swaps 0 Voluntary Context Switches 48 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 8 1753 1754 proc sql; 1755 create view work.WCUST90 as 1756 select 1757 WCUST80.customer_id length = 40 1758 format = $40. 51 The SAS System 16:54 Tuesday, August 9, 2016 1759 label = 'Customer number', 1760 WCUST80.marital_status_code length = 1 1761 format = $1. 1762 label = 'Marital Status Code', 1763 WCUST80.customer_age length = 8 1764 label = 'Customer Age', 1765 WCUST80.period length = 8 1766 format = ddmmyy10. 1767 label = 'Period', 1768 WCUST80.customer_gender length = 1 1769 format = $1. 1770 label = 'Customer Gender', 1771 WCUST80.number_of_dependents_code length = 1 1772 format = $1. 1773 label = 'Number of Dependents Code', 1774 WCUST80.marital_status_desc length = 20 1775 label = 'Marital Status Description', 1776 WCUST80.number_of_dependents_desc length = 20 1777 label = 'Number of Dependents Description', 1778 WCUST80.job_status_code length = 1 1779 format = $5. 1780 label = 'Job Status Code', 1781 WCUST80.job_status_desc length = 20 1782 label = 'Job Status Description', 1783 WCUST80.resident_status length = 50 1784 format = $50. 1785 label = 'Resident Status', 1786 WCFZEMP.CFOCCD as occupation_code length = 5 1787 format = $5. 1788 label = 'Job Occupation Code', 1789 WCFZEMP.occupation_desc length = 40 1790 format = $40. 1791 label = 'Job Occupation Description', 1792 case WCFZEMP.CFESD7 when . then . else intck('MONTH', WCFZEMP.CFESD7 , WCUST80.period ) end as year_on_job length 1792 ! = 8 1793 label = 'Year on Job', 1794 WCFZEMP.CFECUR as current_salary length = 8 1795 format = comma20.2 1796 label = 'Current Salary', 1797 WCUST80.dati_2 length = 40 1798 format = $40. 1799 informat = $40. 1800 label = 'DATI II', 1801 WCUST80.province length = 40 1802 format = $40. 1803 informat = $40. 1804 label = 'Province', 1805 WCUST80.year_live_in_resident length = 8 1806 format = 5. 1807 informat = 5. 1808 label = 'Year Live In Resident', 1809 WCFZEMP.CFENIN as economic_sector length = 70 1810 format = $70. 1811 informat = $70. 1812 label = 'economic_sector', 1813 WCFZEMP.CFCURR as current_salary_currency_code length = 4 1814 format = $4. 1815 informat = $4. 52 The SAS System 16:54 Tuesday, August 9, 2016 1816 label = 'current_salary_currency_code', 1817 WCUST80.customer_name length = 20 1818 format = $20. 1819 informat = $20. 1820 label = 'customer_name', 1821 WCUST80.customer_classification length = 20 1822 format = $20. 1823 informat = $20. 1824 label = 'customer_classification', 1825 WCUST80.identity_type length = 4 1826 format = $4. 1827 informat = $4. 1828 label = 'identity_type', 1829 WCUST80.identity_id length = 40 1830 format = $40. 1831 informat = $40. 1832 label = 'identity_id' 1833 from 1834 work.WCUST80 left join 1835 work.WCFZEMP 1836 on 1837 ( 1838 WCUST80.customer_id = WCFZEMP.CFCIF_2 1839 ) 1840 ; NOTE: SQL view WORK.WCUST90 has been defined. 1841 quit; NOTE: PROCEDURE SQL used (Total process time): real time 0.02 seconds user cpu time 0.02 seconds system cpu time 0.02 seconds memory 257.46k OS Memory 27560.00k Timestamp 08/09/2016 04:56:10 PM Step Count 63 Switch Count 377 Page Faults 0 Page Reclaims 3 Page Swaps 0 Voluntary Context Switches 1456 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 48 1842 1843 %global etls_sql_pushDown; 1844 %let etls_sql_pushDown = &sys_sql_ip_all; 1845 1846 %rcSet(&sqlrc); 1847 1848 1849 1850 /** Step end SQL Join **/ 1851 proc sql; 1852 select count(1) from work.WCUST90; NOTE: The execution of this query involves performing one or more Cartesian product joins that can not be optimized. 1853 quit; NOTE: PROCEDURE SQL used (Total process time): 53 The SAS System 16:54 Tuesday, August 9, 2016 real time 9:08:10.91 user cpu time 9:07:16.98 system cpu time 5.11 seconds memory 221974.43k OS Memory 250136.00k Timestamp 08/10/2016 02:04:21 AM Step Count 64 Switch Count 47 Page Faults 46 Page Reclaims 145895 Page Swaps 0 Voluntary Context Switches 33402 Involuntary Context Switches 63021 Block Input Operations 1403104 Block Output Operations 1074400 1854 1855 %LET _CLIENTTASKLABEL=; 1856 %LET _CLIENTPROCESSFLOWNAME=; 1857 %LET _CLIENTPROJECTPATH=; 1858 %LET _CLIENTPROJECTNAME=; 1859 %LET _SASPROGRAMFILE=; 1860 1861 ;*';*";*/;quit;run; 1862 ODS _ALL_ CLOSE; 1863 1864 1865 QUIT; RUN; 1866