1 The SAS System 15:00 Tuesday, January 23, 2018 NOTE: Unable to open SASUSER.REGSTRY. WORK.REGSTRY will be opened instead. NOTE: All registry changes will be lost at the end of the session. WARNING: Unable to copy SASUSER registry to WORK registry. Because of this, you will not see registry customizations during this session. NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead. NOTE: All profile changes will be lost at the end of the session. NOTE: Copyright (c) 2002-2012 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.4 (TS1M4) Licensed to ORLANDO MAGIC LTD, Site 70095169. NOTE: This session is executing on the X64_SR12R2 platform. NOTE: Updated analytical products: SAS/STAT 14.2 SAS/ETS 14.2 SAS/OR 14.2 SAS/IML 14.2 NOTE: Additional host information: X64_SR12R2 WIN 6.3.9600 Server NOTE: SAS initialization used: real time 0.39 seconds cpu time 0.28 seconds NOTE: AUTOEXEC processing beginning; file is \\12SASAPP3\DI_Deployed\AMP_autoexec_batch.sas. NOTE: Libref STGCON_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_Concessions NOTE: Libref STGET_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_ExactTarget NOTE: Libref SPVO was successfully assigned as follows: Engine: ODBC Physical Name: iQ_SPVO NOTE: Libref STGLA_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_LiveAnalytics NOTE: Libref SRCRT_1 was successfully assigned as follows: Engine: ODBC Physical Name: LocationActivity NOTE: Libref STGRT_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_Retail NOTE: Libref STGTM_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_Ticketmaster NOTE: Libref SRCWRT_1 was successfully assigned as follows: Engine: ODBC Physical Name: WinRetail NOTE: Libref STGWRT_1 was successfully assigned as follows: Engine: ODBC 2 The SAS System 15:00 Tuesday, January 23, 2018 Physical Name: STG_WinRetail NOTE: Libref STGQP_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_QuickPivot NOTE: Libref SRCCRM_1 was successfully assigned as follows: Engine: ODBC Physical Name: Legends NOTE: Libref STGCRM_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_CRM NOTE: Libref STGEXP_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_ExpApp NOTE: Libref STGSM_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_SecondaryMarket NOTE: Libref STGAMP_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_Ampthink NOTE: Libref MATRYX was successfully assigned as follows: Engine: ODBC Physical Name: Matryx NOTE: Libref STGVN_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_VenueNext NOTE: Libref SPVO_VA was successfully assigned as follows: Engine: ODBC Physical Name: SPVO_VA NOTE: Libref STGGA_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_GoogleAnalytics NOTE: Libref CI_CDM was successfully assigned as follows: Engine: OLEDB Physical Name: SQLOLEDB.1 NOTE: Libref WCLIENTS was successfully assigned as follows: Engine: BASE Physical Name: \\12SASAPP1\Work-Client Services NOTE: Libref ICARE was successfully assigned as follows: Engine: BASE Physical Name: F:\Micros_API_Program NOTE: Libref WBSTRAT was successfully assigned as follows: Engine: BASE Physical Name: \\12SASAPP1\Work-Business Strategy NOTE: Libref WTICKETS was successfully assigned as follows: Engine: BASE Physical Name: \\12SASAPP1\Work-Ticket Sales NOTE: Libref SCRATCH was successfully assigned as follows: Engine: BASE Physical Name: \\12SASAPP1\Work-Scratch Area NOTE: Libref MAOUTPUT was successfully assigned as follows: Engine: BASE Physical Name: \\12SASAPP1\Email_Campaign_Export NOTE: AUTOEXEC processing completed. 1 /**************************************************************************** 2 * Job: device_report_1 A5CJ6MER.C10002IQ * 3 * Description: * 3 The SAS System 15:00 Tuesday, January 23, 2018 4 * * 5 * Metadata Server: 12sasmeta1.rdvs.com * 6 * Port: 8561 * 7 * Location: /SPVO/DI_Jobs/Production/AMP/Ampthink/Stage_Loaders_1 * 8 * * 9 * Server: SASApp3 A5CJ6MER.AS0000RU * 10 * * 11 * Source Tables: stg_device_report - A5CJ6MER.BH0002BS * 12 * stgamp_1.stg_device_report * 13 * stg_device_report - A5CJ6MER.BH0002BS * 14 * stgamp_1.stg_device_report * 15 * Target Table: stg_device_report - A5CJ6MER.BH0002BS * 16 * stgamp_1.stg_device_report * 17 * * 18 * Generated on: Tuesday, August 22, 2017 3:19:38 PM EDT * 19 * Generated by: jauter@rdvs * 20 * Version: SAS Data Integration Studio 4.902 * 21 ****************************************************************************/ 22 23 /* Generate the process id for job */ 24 %put Process ID: &SYSJOBID; Process ID: 27468 25 26 /* General macro variables */ 27 %let jobID = %quote(A5CJ6MER.C10002IQ); 28 %let etls_jobName = %nrquote(device_report_1); 29 %let etls_userID = %nrquote(jauter@rdvs); 30 31 /* Setup to capture return codes */ 32 %global job_rc trans_rc sqlrc; 33 %let sysrc = 0; 34 %let job_rc = 0; 35 %let trans_rc = 0; 36 %let sqlrc = 0; 37 %global etls_stepStartTime; 38 /* initialize syserr to 0 */ 39 data _null_; run; NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 40 41 %macro rcSet(error); 42 %if (&error gt &trans_rc) %then 43 %let trans_rc = &error; 44 %if (&error gt &job_rc) %then 45 %let job_rc = &error; 46 %mend rcSet; 47 48 %macro rcSetDS(error); 49 if &error gt input(symget('trans_rc'),12.) then 50 call symput('trans_rc',trim(left(put(&error,12.)))); 51 if &error gt input(symget('job_rc'),12.) then 52 call symput('job_rc',trim(left(put(&error,12.)))); 53 %mend rcSetDS; 54 4 The SAS System 15:00 Tuesday, January 23, 2018 55 /* Create metadata macro variables */ 56 %let IOMServer = %nrquote(SASApp3); 57 %let metaPort = %nrquote(8561); 58 %let metaServer = %nrquote(12sasmeta1.rdvs.com); 59 60 /* Setup for capturing job status */ 61 %let etls_startTime = %sysfunc(datetime(),datetime.); 62 %let etls_recordsBefore = 0; 63 %let etls_recordsAfter = 0; 64 %let etls_lib = 0; 65 %let etls_table = 0; 66 67 %global etls_debug; 68 %macro etls_setDebug; 69 %if %str(&etls_debug) ne 0 %then 70 OPTIONS MPRINT%str(;); 71 %mend; 72 %etls_setDebug; 73 74 /*==========================================================================* 75 * Step: Ampthink FTP Text Import A5CJ6MER.C30004BS * 76 * Transform: Ampthink FTP Text Import * 77 * Description: * 78 * * 79 * Target Table: Ampthink FTP Text Import_importtable A5CJ6MER.C40004D4 * 80 * - work.WCE2YIWC * 81 *==========================================================================*/ 82 83 %let transformID = %quote(A5CJ6MER.C30004BS); 84 %let trans_rc = 0; 85 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 86 87 %let _INPUT_count = 0; 88 %let _OUTPUT_count = 1; 89 %let _OUTPUT = work.WCE2YIWC; 90 %let _OUTPUT_connect = ; 91 %let _OUTPUT_engine = ; 92 %let _OUTPUT_memtype = DATA; 93 %let _OUTPUT_options = %nrquote(); 94 %let _OUTPUT_alter = %nrquote(); 95 %let _OUTPUT_path = %nrquote(/Ampthink FTP Text Import_importtable_A5CJ6MER.C40004D4%(WorkTable%)); 96 %let _OUTPUT_type = 1; 97 %let _OUTPUT_label = %nrquote(); 98 /* List of target columns to keep */ 99 %let _OUTPUT_keep = wifi_date mac_address Portaled Registered first_name last_name 100 email_addr Email_Domain_Valid Facebook_First_Name Facebook_Last_Name 101 Facebook_Email_Addr Facebook_Verified Facebook_Gender Facebook_Link 102 Twitter_Name Twitter_Screen_Name Twitter_Verified Twitter_Location 103 Twitter_Num_Friends Twitter_Num_Followers Twitter_Num_Statuses 104 Twitter_Link Opted_In_Magic Opted_In_Amway Repeat_Visit User_Agent 105 InsertTime LoadTime; 106 107 %let _importtable = work.WCE2YIWC; 108 %let _importtable_connect = ; 109 %let _importtable_engine = ; 110 %let _importtable_memtype = DATA; 111 %let _importtable_options = %nrquote(); 112 %let _importtable_alter = %nrquote(); 5 The SAS System 15:00 Tuesday, January 23, 2018 113 %let _importtable_path = %nrquote(/Ampthink FTP Text Import_importtable_A5CJ6MER.C40004D4%(WorkTable%)); 114 %let _importtable_type = 1; 115 %let _importtable_label = %nrquote(); 116 /* List of target columns to keep */ 117 %let _importtable_keep = wifi_date mac_address Portaled Registered first_name last_name 118 email_addr Email_Domain_Valid Facebook_First_Name Facebook_Last_Name 119 Facebook_Email_Addr Facebook_Verified Facebook_Gender Facebook_Link 120 Twitter_Name Twitter_Screen_Name Twitter_Verified Twitter_Location 121 Twitter_Num_Friends Twitter_Num_Followers Twitter_Num_Statuses 122 Twitter_Link Opted_In_Magic Opted_In_Amway Repeat_Visit User_Agent 123 InsertTime LoadTime; 124 125 126 proc datasets lib=work nolist nowarn memtype = (data view); 127 delete WCE2YIWC; 128 quit; NOTE: PROCEDURE DATASETS used (Total process time): real time 0.04 seconds cpu time 0.04 seconds 129 130 %let stg_libname = %nrquote(stgamp_1); 131 %let tablename = %nrquote(device_report); 132 133 /* List of target columns to keep */ 134 %let _keep = wifi_date mac_address Portaled Registered first_name last_name 135 email_addr Email_Domain_Valid Facebook_First_Name Facebook_Last_Name 136 Facebook_Email_Addr Facebook_Verified Facebook_Gender Facebook_Link 137 Twitter_Name Twitter_Screen_Name Twitter_Verified Twitter_Location 138 Twitter_Num_Friends Twitter_Num_Followers Twitter_Num_Statuses 139 Twitter_Link Opted_In_Magic Opted_In_Amway Repeat_Visit User_Agent 140 InsertTime LoadTime; 141 /* List of target columns to keep */ 142 %let keep = wifi_date mac_address Portaled Registered first_name last_name 143 email_addr Email_Domain_Valid Facebook_First_Name Facebook_Last_Name 144 Facebook_Email_Addr Facebook_Verified Facebook_Gender Facebook_Link 145 Twitter_Name Twitter_Screen_Name Twitter_Verified Twitter_Location 146 Twitter_Num_Friends Twitter_Num_Followers Twitter_Num_Statuses 147 Twitter_Link Opted_In_Magic Opted_In_Amway Repeat_Visit User_Agent 148 InsertTime LoadTime; 149 150 LIBNAME stgamp_1 ODBC REREAD_EXPOSURE=YES READ_LOCK_TYPE=NOLOCK READBUFF=1000 DATASRC=STG_Ampthink SCHEMA=dbo 150 ! USER=sassql PASSWORD=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX ; NOTE: Libref STGAMP_1 was successfully assigned as follows: Engine: ODBC Physical Name: STG_Ampthink 151 152 /* 153 %let stg_libname = stgamp_1 ; 154 %let tablename = device_report ; 155 %let _importtable = scratch ; 156 */ 157 /* 158 options nosource nonotes errors=0; 159 */ 160 6 The SAS System 15:00 Tuesday, January 23, 2018 161 /*---- DON’T SHOW THESE CODES IN THE LOG - START ----*/ 162 options nosource; 187 /*---- DON’T SHOW THESE CODES IN THE LOG - END ----*/ 188 189 options nomprint ; /*mlogic symbolgen;*/ 190 191 %macro fullprogramwrap; 192 193 proc sql noprint; 194 select last_process format=12. 195 into :last_process 196 from &stg_libname..Process_timestamp 197 where file="&tablename" ; 198 quit ; 199 200 201 %macro get_filenames(location); 202 filename _dir_ "%bquote(&location.)"; 203 data filenames(keep=memname); 204 handle=dopen( '_dir_' ); 205 if handle > 0 then do; 206 count=dnum(handle); 207 do i=1 to count; 208 memname=dread(handle,i); 209 output filenames; 210 end; 211 end; 212 rc=dclose(handle); 213 run; 214 filename _dir_ clear; 215 %mend; 216 217 *%get_filenames(\\192.168.103.2\tm\live_analytics); 218 %get_filenames(ftp://ud4b1:password@orlandomagic.sharefileftp.com/Ampthink); 219 220 proc sql ; 221 create table filenames_parse as 222 select 222 ! input(((substr(memname,length(memname)-13,4))||(substr(memname,length(memname)-8,2))||(substr(memname,length(memname)-5,2 222 ! ))),best12.) as dateinfo, 223 tranwrd((substr(trim(left(memname)),1,length(trim(left(memname)))-15)),'-','_') as filename, 224 monotonic() as _n_ 225 from filenames 226 where calculated filename = "&tablename" 227 and substr(memname,length(memname)-3,4) = '.csv' 228 and 228 ! input(((substr(memname,length(memname)-13,4))||(substr(memname,length(memname)-8,2))||(substr(memname,length(memname)-5,2 228 ! ))),best12.) > &last_process 229 order by 1; 230 quit ; 231 232 /*check to see if any files to be processed and if not then reprocess last file*/ 233 proc sql noprint; 234 select count(*) into :obs_count from filenames_parse; 235 quit; 236 237 %macro empty_run; 238 %if (&obs_count eq 0) %then %do; 7 The SAS System 15:00 Tuesday, January 23, 2018 239 proc sql ; 240 create table filenames_parse as 241 select 241 ! input(((substr(memname,length(memname)-13,4))||(substr(memname,length(memname)-8,2))||(substr(memname,length(memname)-5,2 241 ! ))),best12.) as dateinfo, 242 tranwrd((substr(trim(left(memname)),1,length(trim(left(memname)))-15)),'-','_') as filename, 243 monotonic() as _n_ 244 from filenames 245 where calculated filename = "&tablename" 246 and substr(memname,length(memname)-3,4) = '.csv' 247 and 247 ! input(((substr(memname,length(memname)-13,4))||(substr(memname,length(memname)-8,2))||(substr(memname,length(memname)-5,2 247 ! ))),best12.) = &last_process 248 order by 1; 249 quit ; 250 %end; 251 %mend empty_run; 252 %empty_run; 253 254 255 256 257 /*============================new code====================*/ 258 259 %timegap_check ; 260 261 proc sql noprint ; 262 select 263 (select max(dateinfo) from Filenames_parse)-(select last_process from &stg_libname..Process_timestamp where 263 ! file="&tablename") into :timegap 264 from &stg_libname..Process_timestamp; 265 quit ; 266 267 %if &timegap=0 %then %goto EXIT; 268 269 270 /*============================new code====================*/ 271 272 data _null_ ; 273 CALL SYMPUT("stagetable",'stg_'||trim(left("&tablename"))) ; 274 run ; 275 276 /* 277 proc sql outobs=0; 278 create table &_importtable as 279 select * from &stg_libname..&stagetable ; 280 quit ; 281 */ 282 283 284 data _null_ ; 285 CALL SYMPUT("loopmacro",'import_'||trim(left("&tablename"))) ; 286 run ; 287 288 289 290 291 proc sql ; 8 The SAS System 15:00 Tuesday, January 23, 2018 292 create table layout1 as 293 select *, 294 case when data_type="Char" then " $" else " " end as data_type_pre, 295 case when data_type="Char" then ". ;" else " ;" end as data_type_post 296 from &stg_libname..file_layout_lkp 297 where file="&tablename" ; 298 quit ; 299 300 proc sql ; 301 create table layout2 as 302 select variable_order, 303 "informat "||trim(left(variable_name))||data_type_pre||trim(left(informat_def))||data_type_post as informat_var, 304 "format "||trim(left(variable_name))||data_type_pre||trim(left(format_def))||data_type_post as format_var, 305 trim(left(variable_name))||data_type_pre as type_var 306 from layout1 ; 307 quit ; 308 309 310 311 %macro informat_loop; 312 313 proc sql noprint; 314 select max(variable_order) 315 into :importcount 316 from layout1; 317 quit ; 318 319 %if &importcount>255 %then %do ; 320 %let i=1; 321 proc sql outobs=1; 322 create table statements1 as 323 select 324 325 " " 326 %do %while (&i <= 255); 327 ||(select informat_var from layout2 where variable_order=&i) 328 %let i=%eval(&i+1); 329 %end; as informatstring 330 331 from layout2 ; 332 quit ; 333 334 %let i=256; 335 336 proc sql outobs=1; 337 create table statements2 as 338 select 339 " " 340 %do %while (&i <= &importcount); 341 ||(select informat_var from layout2 where variable_order=&i) 342 %let i=%eval(&i+1); 343 %end; as informatstring 344 345 from layout2 ; 346 quit ; 347 348 proc sql ; 349 create table informat_statement as select 9 The SAS System 15:00 Tuesday, January 23, 2018 350 (select informatstring from statements1)||" "||(select informatstring from statements2) as informatstring 351 from statements1 ; 352 quit ; 353 354 %end ; 355 %else %do; 356 357 %let i=1; 358 359 proc sql outobs=1; 360 create table informat_statement as 361 select 362 " " 363 %do %while (&i <= &importcount); 364 ||(select informat_var from layout2 where variable_order=&i) 365 %let i=%eval(&i+1); 366 %end; as informatstring 367 368 from layout2 ; 369 quit ; 370 %end ; 371 %mend ; 372 373 374 375 %macro format_loop; 376 377 proc sql noprint; 378 select max(variable_order) 379 into :importcount 380 from layout1; 381 quit ; 382 383 %if &importcount>255 %then %do ; 384 385 %let f=1; 386 387 proc sql outobs=1; 388 create table statements1 as 389 select 390 391 " " 392 %do %while (&f <= 255); 393 ||(select format_var from layout2 where variable_order=&f) 394 %let f=%eval(&f+1); 395 %end; as formatstring 396 397 from layout2 ; 398 quit ; 399 400 %let f=256; 401 402 proc sql outobs=1; 403 create table statements2 as 404 select 405 " " 406 %do %while (&f <= &importcount); 407 ||(select format_var from layout2 where variable_order=&f) 10 The SAS System 15:00 Tuesday, January 23, 2018 408 %let f=%eval(&f+1); 409 %end; as formatstring 410 411 from layout2 ; 412 quit ; 413 414 proc sql ; 415 create table format_statement as select 416 (select formatstring from statements1)||" "||(select formatstring from statements2) as formatstring 417 from statements1 ; 418 quit ; 419 420 %end ; 421 %else %do; 422 423 %let f=1; 424 425 proc sql outobs=1; 426 create table format_statement as 427 select 428 " " 429 %do %while (&f <= &importcount); 430 ||(select format_var from layout2 where variable_order=&f) 431 %let f=%eval(&f+1); 432 %end; as formatstring 433 from layout2 ; 434 quit ; 435 %end ; 436 %mend ; 437 438 439 440 441 442 %macro type_loop; 443 444 proc sql noprint; 445 select max(variable_order) 446 into :importcount 447 from layout1; 448 quit ; 449 450 %if &importcount>255 %then %do ; 451 %let t=1; 452 proc sql outobs=1; 453 create table statements1 as 454 select 455 456 " " 457 %do %while (&t <= 255); 458 ||(select type_var from layout2 where variable_order=&t) 459 %let t=%eval(&t+1); 460 %end; as typestring 461 462 from layout2 ; 463 quit ; 464 465 %let t=256; 11 The SAS System 15:00 Tuesday, January 23, 2018 466 proc sql outobs=1; 467 create table statements2 as 468 select 469 " " 470 %do %while (&t <= &importcount); 471 ||(select type_var from layout2 where variable_order=&t) 472 %let t=%eval(&t+1); 473 %end; as typestring 474 475 from layout2 ; 476 quit ; 477 478 proc sql ; 479 create table type_statement as select 480 (select typestring from statements1)||" "||(select typestring from statements2) as typestring 481 from statements1 ; 482 quit ; 483 484 %end ; 485 %else %do; 486 487 %let t=1; 488 proc sql outobs=1; 489 create table type_statement as 490 select 491 " " 492 %do %while (&t <= &importcount); 493 ||(select type_var from layout2 where variable_order=&t) 494 %let t=%eval(&t+1); 495 %end; as typestring 496 497 from layout2 ; 498 quit ; 499 %end ; 500 %mend ; 501 502 %informat_loop ; 503 %format_loop ; 504 %type_loop ; 505 506 507 %macro build_table; 508 509 510 proc sql noprint; 511 select 511 ! trim(left(tranwrd("&tablename.",'_','-')||'-'||substr(put(dateinfo,8.0),1,4)||'-'||substr(put(dateinfo,8.0),5,2)||'-'||su 511 ! bstr(put(dateinfo,8.0),7,2)))||'.csv' 512 into :importfilename 513 from Filenames_parse 514 where _n_ = 1 ; 515 quit ; 516 517 518 /*%&loopmacro. ;*/ 519 520 521 12 The SAS System 15:00 Tuesday, January 23, 2018 522 proc sql noprint; 523 select informatstring 524 into :informatstring 525 from informat_statement; 526 quit ; 527 528 529 proc sql noprint; 530 select formatstring 531 into :formatstring 532 from Format_statement; 533 quit ; 534 535 proc sql noprint; 536 select typestring 537 into :typestring 538 from Type_statement; 539 quit ; 540 541 filename a "ftp://ud4b1:password@orlandomagic.sharefileftp.com/Ampthink/&importfilename."; 542 filename b "ftp://ud4b1:password@orlandomagic.sharefileftp.com/Ampthink/translate_temp.csv"; 543 544 data _null_; 545 infile a truncover length=len encoding='ANY' lrecl=32767; 546 file b lrecl=32767; 547 length line $32767; 548 input line $char32767.; 549 len=length(line); 550 do i=1 to len; 551 c=substr(line,i,1); 552 rank=rank(c); 553 if not (32<=rank<=126) then substr(line,i,1)=' '; 554 end; 555 put line; 556 run; 557 558 559 data WORK.retention ; 560 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 561 infile "ftp://ud4b1:password@orlandomagic.sharefileftp.com/Ampthink/translate_temp.csv" delimiter=',' 561 ! MISSOVER DSD lrecl=32767 firstobs=2; 562 &informatstring 563 &formatstring 564 input &typestring ; 565 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable*/ 566 run; 567 568 569 570 proc sql; 571 create table &_importtable as 572 select *, 573 datetime() FORMAT=datetime22.3 as InsertTime , 574 datetime() FORMAT=datetime22.3 as LoadTime 575 from retention ; 576 577 delete * from &_importtable ; 578 quit ; 13 The SAS System 15:00 Tuesday, January 23, 2018 579 580 581 %mend; 582 583 %build_table ; 584 585 586 %macro import_loop; 587 %let n=1; 588 589 proc sql noprint; 590 select count(*) format=12. 591 into :varcount 592 from Filenames_parse; 593 quit ; 594 595 %do %while (&n <= &varcount); 596 proc sql noprint; 597 select 597 ! trim(left(tranwrd("&tablename.",'_','-')||'-'||substr(put(dateinfo,8.0),1,4)||'-'||substr(put(dateinfo,8.0),5,2)||'-'||su 597 ! bstr(put(dateinfo,8.0),7,2)))||'.csv' 598 into :importfilename 599 from Filenames_parse 600 where _n_ = &n ; 601 quit ; 602 603 604 605 /*%&loopmacro. ;*/ 606 607 608 609 proc sql noprint; 610 select informatstring 611 into :informatstring 612 from informat_statement; 613 quit ; 614 615 616 proc sql noprint; 617 select formatstring 618 into :formatstring 619 from Format_statement; 620 quit ; 621 622 proc sql noprint; 623 select typestring 624 into :typestring 625 from Type_statement; 626 quit ; 627 628 629 630 filename a "ftp://ud4b1:password@orlandomagic.sharefileftp.com/Ampthink/&importfilename."; 631 filename b "ftp://ud4b1:password@orlandomagic.sharefileftp.com/Ampthink/translate_temp.csv"; 632 633 data _null_; 634 infile a truncover length=len encoding='ANY' lrecl=32767; 14 The SAS System 15:00 Tuesday, January 23, 2018 635 file b lrecl=32767; 636 length line $32767; 637 input line $char32767.; 638 len=length(line); 639 do i=1 to len; 640 c=substr(line,i,1); 641 rank=rank(c); 642 if not (32<=rank<=126) then substr(line,i,1)=' '; 643 end; 644 put line; 645 run; 646 647 648 data WORK.retention ; 649 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */ 650 infile "ftp://ud4b1:password@orlandomagic.sharefileftp.com/Ampthink/translate_temp.csv" delimiter=',' 650 ! MISSOVER DSD lrecl=32767 firstobs=2; 651 &informatstring 652 &formatstring 653 input &typestring ; 654 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable*/ 655 run; 656 657 proc sql; 658 insert into &_importtable 659 select *, 660 datetime() FORMAT=datetime22.3 as InsertTime , 661 datetime() FORMAT=datetime22.3 as LoadTime 662 from retention ; 663 quit ; 664 665 %let n=%eval(&n+1); 666 %end; 667 %mend; 668 669 %import_loop ; 670 671 672 673 674 675 /*========================================new code=======================*/ 676 677 proc sql noprint; 678 select count(*) into :obs_count_new from &_importtable ; 679 quit; 680 681 %macro empty_run_timestamp; 682 %if (&obs_count_new eq 0) %then %do; 683 684 685 686 687 proc sql; 688 update &stg_libname..Process_timestamp 689 set last_process=case when (select max(dateinfo) from Filenames_parse)=. then last_process else (select 689 ! max(dateinfo) from Filenames_parse) end 690 where file="&tablename" ; 15 The SAS System 15:00 Tuesday, January 23, 2018 691 quit ; 692 %end; 693 %mend empty_run_timestamp; 694 %empty_run_timestamp; 695 696 697 698 %if (&obs_count_new eq 0) %then %goto EXIT; 699 700 701 /* 702 %EXIT: 703 704 %mend fullprogramwrap; 705 706 %fullprogramwrap ; 707 */ 708 709 %rcSet(&syserr); 710 %rcSet(&sysrc); 711 %rcSet(&sqlrc); 712 713 714 715 /** Step end Ampthink FTP Text Import **/ 716 717 /*==========================================================================* 718 * Step: Extract A5CJ6MER.C30004BT * 719 * Transform: Extract * 720 * Description: * 721 * * 722 * Source Table: Ampthink FTP Text Import_importtable A5CJ6MER.C40004D4 * 723 * - work.WCE2YIWC * 724 * Target Table: Extract - work.WCE3H085 A5CJ6MER.C40004D5 * 725 *==========================================================================*/ 726 727 %let transformID = %quote(A5CJ6MER.C30004BT); 728 %let trans_rc = 0; 729 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 730 731 %let etls_recCheckExist = 0; 732 %let etls_recnt = 0; 733 %macro etls_recordCheck; 734 %let etls_recCheckExist = %eval(%sysfunc(exist(work.WCE2YIWC, DATA)) or 735 %sysfunc(exist(work.WCE2YIWC, VIEW))); 736 737 %if (&etls_recCheckExist) %then 738 %do; 739 %local etls_syntaxcheck; 740 %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 741 /* Turn off syntaxcheck option to perform following steps */ 742 options nosyntaxcheck; 743 744 proc contents data = work.WCE2YIWC out = work.etls_contents(keep = nobs) noprint; 745 run; 746 747 data _null_; 748 set work.etls_contents (obs = 1); 16 The SAS System 15:00 Tuesday, January 23, 2018 749 call symput("etls_recnt", left(put(nobs,32.))); 750 run; 751 752 proc datasets lib = work nolist nowarn memtype = (data view); 753 delete etls_contents; 754 quit; 755 756 /* Reset syntaxcheck option to previous setting */ 757 options &etls_syntaxcheck; 758 %end; 759 %mend etls_recordCheck; 760 %etls_recordCheck; 761 762 %let SYSLAST = %nrquote(work.WCE2YIWC); 763 764 %global etls_sql_pushDown; 765 %let etls_sql_pushDown = -1; 766 option DBIDIRECTEXEC; 767 768 /*---- Map the columns ----*/ 769 proc datasets lib = work nolist nowarn memtype = (data view); 770 delete WCE3H085; 771 quit; 772 773 %put %str(NOTE: Mapping columns ...); 774 proc sql; 775 create view work.WCE3H085 as 776 select 777 wifi_date, 778 mac_address, 779 Portaled, 780 Registered, 781 first_name, 782 last_name, 783 email_addr, 784 Email_Domain_Valid, 785 Facebook_First_Name, 786 Facebook_Last_Name, 787 Facebook_Email_Addr, 788 Facebook_Verified, 789 Facebook_Gender, 790 Facebook_Link, 791 Twitter_Name, 792 Twitter_Screen_Name, 793 Twitter_Verified, 794 Twitter_Location, 795 Twitter_Num_Friends, 796 Twitter_Num_Followers, 797 Twitter_Num_Statuses, 798 Twitter_Link, 799 Opted_In_Magic, 800 Opted_In_Amway, 801 Repeat_Visit, 802 User_Agent, 803 InsertTime, 804 LoadTime 805 from &SYSLAST 806 where email_addr ^= '' 17 The SAS System 15:00 Tuesday, January 23, 2018 807 ; 808 quit; 809 810 %let SYSLAST = work.WCE3H085; 811 812 %global etls_sql_pushDown; 813 %let etls_sql_pushDown = &sys_sql_ip_all; 814 815 %rcSet(&sqlrc); 816 817 818 819 /** Step end Extract **/ 820 821 /*==========================================================================* 822 * Step: Compare Tables A5CJ6MER.C30004BU * 823 * Transform: Compare Tables * 824 * Description: * 825 * * 826 * Source Tables: Extract - work.WCE3H085 A5CJ6MER.C40004D5 * 827 * stg_device_report - A5CJ6MER.BH0002BS * 828 * stgamp_1.stg_device_report * 829 * Target Tables: Changed records - work.WCE3LNXH A5CJ6MER.C40004D6 * 830 * New records - work.WCE3LPBD A5CJ6MER.C40004D7 * 831 * Unchanged records - work.WCE3LQKY A5CJ6MER.C40004D8 * 832 * Missing records - work.WCE3LRWQ A5CJ6MER.C40004D9 * 833 *==========================================================================*/ 834 835 %let transformID = %quote(A5CJ6MER.C30004BU); 836 %let trans_rc = 0; 837 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 838 839 %let etls_recnt = -1; 840 /*---- Delete pre-existing tables ----*/ 841 proc datasets lib=work nolist nowarn memtype = (data view); 842 delete WCE3LNXH; 843 delete WCE3LPBD; 844 delete WCE3LQKY; 845 delete WCE3LRWQ; 846 quit; 847 848 proc datasets lib = work nolist nowarn memtype = (data view); 849 delete etls_compare_digest; 850 quit; 851 852 proc datasets lib = work nolist nowarn memtype = (data view); 853 delete etls_missing_records; 854 quit; 855 856 /* create compare digest data set */ 857 data work.etls_compare_digest(keep = wifi_date mac_address compare_digest); 858 859 length compare_digest $ 32; 860 861 set stgamp_1.stg_device_report( keep = wifi_date mac_address Portaled Registered first_name last_name email_addr 861 ! Email_Domain_Valid 862 Facebook_First_Name Facebook_Last_Name Facebook_Email_Addr Facebook_Verified 863 Facebook_Gender Facebook_Link Twitter_Name Twitter_Screen_Name 18 The SAS System 15:00 Tuesday, January 23, 2018 864 Twitter_Verified Twitter_Location Twitter_Num_Friends Twitter_Num_Followers 865 Twitter_Num_Statuses Twitter_Link Opted_In_Magic Opted_In_Amway Repeat_Visit 866 User_Agent); 867 868 /* create digest - version 2.1 */ 869 compare_digest = put(md5(catq(' ', Portaled, Registered, first_name, last_name, email_addr, Email_Domain_Valid, 870 Facebook_First_Name, Facebook_Last_Name, Facebook_Email_Addr, 871 Facebook_Verified, Facebook_Gender, Facebook_Link, Twitter_Name, 872 Twitter_Screen_Name, Twitter_Verified, Twitter_Location, Twitter_Num_Friends, 873 Twitter_Num_Followers, Twitter_Num_Statuses, Twitter_Link, Opted_In_Magic, 874 Opted_In_Amway, Repeat_Visit, User_Agent)), $hex32.); 875 876 run; 877 878 /* source/compare lookup: hash lookup method */ 879 data 880 work.WCE3LNXH 881 work.WCE3LPBD 882 work.WCE3LQKY; 883 884 length source_digest $ 32; 885 886 drop source_digest compare_digest rc; 887 888 if 0 then 889 set work.etls_compare_digest; 890 891 if _N_ eq 1 then 892 do; 893 declare hash hct(dataset: 'work.etls_compare_digest', hashexp: 10); 894 hct.defineKey("wifi_date", "mac_address"); 895 hct.defineData("wifi_date", "mac_address", "compare_digest"); 896 hct.defineDone(); 897 end; 898 899 set work.WCE3H085 end = eof; 900 901 /* create digest - version 2.1 */ 902 source_digest = put(md5(catq(' ', Portaled, Registered, first_name, last_name, email_addr, Email_Domain_Valid, 903 Facebook_First_Name, Facebook_Last_Name, Facebook_Email_Addr, 904 Facebook_Verified, Facebook_Gender, Facebook_Link, Twitter_Name, 905 Twitter_Screen_Name, Twitter_Verified, Twitter_Location, Twitter_Num_Friends, 906 Twitter_Num_Followers, Twitter_Num_Statuses, Twitter_Link, Opted_In_Magic, 907 Opted_In_Amway, Repeat_Visit, User_Agent)), $hex32.); 908 909 /* source/compare match */ 910 if hct.find() eq 0 then 911 do; 912 /* source/compare match: Unchanged records */ 913 if source_digest eq compare_digest then 914 output work.WCE3LQKY; 915 916 /* source/compare match: Changed records */ 917 else if source_digest ne compare_digest then 918 output work.WCE3LNXH; 919 920 /* remove found key + data */ 921 hct.remove(); 19 The SAS System 15:00 Tuesday, January 23, 2018 922 end; 923 /* source table: New records */ 924 else if hct.find() ne 0 then 925 output work.WCE3LPBD; 926 927 /* Missing records */ 928 if eof then 929 rc = hct.output(dataset: 'work.WCE3LRWQ(drop = compare_digest)'); 930 931 run; 932 %rcSet(&syscc); 933 934 935 proc datasets lib = work nolist nowarn memtype = (data view); 936 delete etls_compare_digest; 937 quit; 938 939 940 941 /** Step end Compare Tables **/ 942 943 /*==========================================================================* 944 * Step: Join A5CJ6MER.C30004BV * 945 * Transform: Join * 946 * Description: * 947 * * 948 * Source Tables: Changed records - work.WCE3LNXH A5CJ6MER.C40004D6 * 949 * stg_device_report - A5CJ6MER.BH0002BS * 950 * stgamp_1.stg_device_report * 951 * Target Table: Join - work.changed_records A5CJ6MER.C40004DA * 952 *==========================================================================*/ 953 954 %let transformID = %quote(A5CJ6MER.C30004BV); 955 %let trans_rc = 0; 956 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 957 958 %let etls_recCheckExist = 0; 959 %let etls_recnt = 0; 960 %macro etls_recordCheck; 961 %let etls_recCheckExist = %eval(%sysfunc(exist(work.WCE3LNXH, DATA)) or 962 %sysfunc(exist(work.WCE3LNXH, VIEW))); 963 964 %if (&etls_recCheckExist) %then 965 %do; 966 %local etls_syntaxcheck; 967 %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 968 /* Turn off syntaxcheck option to perform following steps */ 969 options nosyntaxcheck; 970 971 proc contents data = work.WCE3LNXH out = work.etls_contents(keep = nobs) noprint; 972 run; 973 974 data _null_; 975 set work.etls_contents (obs = 1); 976 call symput("etls_recnt", left(put(nobs,32.))); 977 run; 978 979 proc datasets lib = work nolist nowarn memtype = (data view); 20 The SAS System 15:00 Tuesday, January 23, 2018 980 delete etls_contents; 981 quit; 982 983 /* Reset syntaxcheck option to previous setting */ 984 options &etls_syntaxcheck; 985 %end; 986 %mend etls_recordCheck; 987 %etls_recordCheck; 988 989 %global etls_sql_pushDown; 990 %let etls_sql_pushDown = -1; 991 option DBIDIRECTEXEC; 992 993 proc datasets lib = work nolist nowarn memtype = (data view); 994 delete changed_records; 995 quit; 996 997 proc sql; 998 create view work.changed_records as 999 select 1000 WCE3LNXH.wifi_date length = 10 1001 format = $10. 1002 informat = $10. 1003 label = 'wifi_date', 1004 WCE3LNXH.mac_address length = 50 1005 format = $50. 1006 informat = $50. 1007 label = 'mac_address', 1008 WCE3LNXH.Portaled length = 5 1009 format = $5. 1010 informat = $5. 1011 label = 'Portaled', 1012 WCE3LNXH.Registered length = 5 1013 format = $5. 1014 informat = $5. 1015 label = 'Registered', 1016 WCE3LNXH.first_name length = 100 1017 format = $100. 1018 informat = $100. 1019 label = 'first_name', 1020 WCE3LNXH.last_name length = 100 1021 format = $100. 1022 informat = $100. 1023 label = 'last_name', 1024 WCE3LNXH.email_addr length = 100 1025 format = $100. 1026 informat = $100. 1027 label = 'email_addr', 1028 WCE3LNXH.Email_Domain_Valid length = 5 1029 format = $5. 1030 informat = $5. 1031 label = 'Email_Domain_Valid', 1032 WCE3LNXH.Facebook_First_Name length = 100 1033 format = $100. 1034 informat = $100. 1035 label = 'Facebook_First_Name', 1036 WCE3LNXH.Facebook_Last_Name length = 100 1037 format = $100. 21 The SAS System 15:00 Tuesday, January 23, 2018 1038 informat = $100. 1039 label = 'Facebook_Last_Name', 1040 WCE3LNXH.Facebook_Email_Addr length = 100 1041 format = $100. 1042 informat = $100. 1043 label = 'Facebook_Email_Addr', 1044 WCE3LNXH.Facebook_Verified length = 5 1045 format = $5. 1046 informat = $5. 1047 label = 'Facebook_Verified', 1048 WCE3LNXH.Facebook_Gender length = 10 1049 format = $10. 1050 informat = $10. 1051 label = 'Facebook_Gender', 1052 WCE3LNXH.Facebook_Link length = 100 1053 format = $100. 1054 informat = $100. 1055 label = 'Facebook_Link', 1056 WCE3LNXH.Twitter_Name length = 100 1057 format = $100. 1058 informat = $100. 1059 label = 'Twitter_Name', 1060 WCE3LNXH.Twitter_Screen_Name length = 100 1061 format = $100. 1062 informat = $100. 1063 label = 'Twitter_Screen_Name', 1064 WCE3LNXH.Twitter_Verified length = 5 1065 format = $5. 1066 informat = $5. 1067 label = 'Twitter_Verified', 1068 WCE3LNXH.Twitter_Location length = 100 1069 format = $100. 1070 informat = $100. 1071 label = 'Twitter_Location', 1072 WCE3LNXH.Twitter_Num_Friends length = 8 1073 format = 12. 1074 informat = 12. 1075 label = 'Twitter_Num_Friends', 1076 WCE3LNXH.Twitter_Num_Followers length = 8 1077 format = 12. 1078 informat = 12. 1079 label = 'Twitter_Num_Followers', 1080 WCE3LNXH.Twitter_Num_Statuses length = 8 1081 format = 12. 1082 informat = 12. 1083 label = 'Twitter_Num_Statuses', 1084 WCE3LNXH.Twitter_Link length = 100 1085 format = $100. 1086 informat = $100. 1087 label = 'Twitter_Link', 1088 WCE3LNXH.Opted_In_Magic length = 5 1089 format = $5. 1090 informat = $5. 1091 label = 'Opted_In_Magic', 1092 WCE3LNXH.Opted_In_Amway length = 5 1093 format = $5. 1094 informat = $5. 1095 label = 'Opted_In_Amway', 22 The SAS System 15:00 Tuesday, January 23, 2018 1096 WCE3LNXH.Repeat_Visit length = 5 1097 format = $5. 1098 informat = $5. 1099 label = 'Repeat_Visit', 1100 WCE3LNXH.User_Agent length = 100 1101 format = $100. 1102 informat = $100. 1103 label = 'User_Agent', 1104 stg_device_report.InsertTime length = 8 1105 format = DATETIME22.3 1106 informat = DATETIME22.3 1107 label = 'InsertTime', 1108 WCE3LNXH.LoadTime length = 8 1109 format = DATETIME22.3 1110 informat = DATETIME22.3 1111 label = 'LoadTime' 1112 from 1113 work.WCE3LNXH as WCE3LNXH left join 1114 stgamp_1.stg_device_report as stg_device_report 1115 on 1116 ( 1117 WCE3LNXH.wifi_date = stg_device_report.wifi_date 1118 and WCE3LNXH.mac_address = stg_device_report.mac_address 1119 ) 1120 ; 1121 quit; 1122 1123 %global etls_sql_pushDown; 1124 %let etls_sql_pushDown = &sys_sql_ip_all; 1125 1126 %rcSet(&sqlrc); 1127 1128 1129 1130 /** Step end Join **/ 1131 1132 /*==========================================================================* 1133 * Step: Set Operators A5CJ6MER.C30004BW * 1134 * Transform: Set Operators * 1135 * Description: * 1136 * * 1137 * Source Tables: Join - work.changed_records A5CJ6MER.C40004DA * 1138 * New records - work.WCE3LPBD A5CJ6MER.C40004D7 * 1139 * Target Table: Set Operators - work.WCE4DHT6 A5CJ6MER.C40004DB * 1140 *==========================================================================*/ 1141 1142 %let transformID = %quote(A5CJ6MER.C30004BW); 1143 %let trans_rc = 0; 1144 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1145 1146 %let etls_recnt = -1; 1147 %global etls_sql_pushDown; 1148 %let etls_sql_pushDown = -1; 1149 option DBIDIRECTEXEC; 1150 1151 proc datasets lib = work nolist nowarn memtype = (data view); 1152 delete WCE4DHT6; 1153 quit; 23 The SAS System 15:00 Tuesday, January 23, 2018 1154 1155 proc sql; 1156 create view work.WCE4DHT6 as 1157 /* Returns all unique rows from the two query results. */ 1158 select 1159 changed_records.wifi_date length = 10 1160 format = $10. 1161 informat = $10. 1162 label = 'wifi_date', 1163 changed_records.mac_address length = 50 1164 format = $50. 1165 informat = $50. 1166 label = 'mac_address', 1167 changed_records.Portaled length = 5 1168 format = $5. 1169 informat = $5. 1170 label = 'Portaled', 1171 changed_records.Registered length = 5 1172 format = $5. 1173 informat = $5. 1174 label = 'Registered', 1175 changed_records.first_name length = 100 1176 format = $100. 1177 informat = $100. 1178 label = 'first_name', 1179 changed_records.last_name length = 100 1180 format = $100. 1181 informat = $100. 1182 label = 'last_name', 1183 changed_records.email_addr length = 100 1184 format = $100. 1185 informat = $100. 1186 label = 'email_addr', 1187 changed_records.Email_Domain_Valid length = 5 1188 format = $5. 1189 informat = $5. 1190 label = 'Email_Domain_Valid', 1191 changed_records.Facebook_First_Name length = 100 1192 format = $100. 1193 informat = $100. 1194 label = 'Facebook_First_Name', 1195 changed_records.Facebook_Last_Name length = 100 1196 format = $100. 1197 informat = $100. 1198 label = 'Facebook_Last_Name', 1199 changed_records.Facebook_Email_Addr length = 100 1200 format = $100. 1201 informat = $100. 1202 label = 'Facebook_Email_Addr', 1203 changed_records.Facebook_Verified length = 5 1204 format = $5. 1205 informat = $5. 1206 label = 'Facebook_Verified', 1207 changed_records.Facebook_Gender length = 10 1208 format = $10. 1209 informat = $10. 1210 label = 'Facebook_Gender', 1211 changed_records.Facebook_Link length = 100 24 The SAS System 15:00 Tuesday, January 23, 2018 1212 format = $100. 1213 informat = $100. 1214 label = 'Facebook_Link', 1215 changed_records.Twitter_Name length = 100 1216 format = $100. 1217 informat = $100. 1218 label = 'Twitter_Name', 1219 changed_records.Twitter_Screen_Name length = 100 1220 format = $100. 1221 informat = $100. 1222 label = 'Twitter_Screen_Name', 1223 changed_records.Twitter_Verified length = 5 1224 format = $5. 1225 informat = $5. 1226 label = 'Twitter_Verified', 1227 changed_records.Twitter_Location length = 100 1228 format = $100. 1229 informat = $100. 1230 label = 'Twitter_Location', 1231 changed_records.Twitter_Num_Friends length = 8 1232 format = 12. 1233 informat = 12. 1234 label = 'Twitter_Num_Friends', 1235 changed_records.Twitter_Num_Followers length = 8 1236 format = 12. 1237 informat = 12. 1238 label = 'Twitter_Num_Followers', 1239 changed_records.Twitter_Num_Statuses length = 8 1240 format = 12. 1241 informat = 12. 1242 label = 'Twitter_Num_Statuses', 1243 changed_records.Twitter_Link length = 100 1244 format = $100. 1245 informat = $100. 1246 label = 'Twitter_Link', 1247 changed_records.Opted_In_Magic length = 5 1248 format = $5. 1249 informat = $5. 1250 label = 'Opted_In_Magic', 1251 changed_records.Opted_In_Amway length = 5 1252 format = $5. 1253 informat = $5. 1254 label = 'Opted_In_Amway', 1255 changed_records.Repeat_Visit length = 5 1256 format = $5. 1257 informat = $5. 1258 label = 'Repeat_Visit', 1259 changed_records.User_Agent length = 100 1260 format = $100. 1261 informat = $100. 1262 label = 'User_Agent', 1263 changed_records.InsertTime length = 8 1264 format = DATETIME22.3 1265 informat = DATETIME22.3 1266 label = 'InsertTime', 1267 datetime() as LoadTime length = 8 1268 format = DATETIME22.3 1269 informat = DATETIME22.3 25 The SAS System 15:00 Tuesday, January 23, 2018 1270 label = 'LoadTime' 1271 from 1272 work.changed_records 1273 union all 1274 select 1275 WCE3LPBD.wifi_date length = 10 1276 format = $10. 1277 informat = $10. 1278 label = 'wifi_date', 1279 WCE3LPBD.mac_address length = 50 1280 format = $50. 1281 informat = $50. 1282 label = 'mac_address', 1283 WCE3LPBD.Portaled length = 5 1284 format = $5. 1285 informat = $5. 1286 label = 'Portaled', 1287 WCE3LPBD.Registered length = 5 1288 format = $5. 1289 informat = $5. 1290 label = 'Registered', 1291 WCE3LPBD.first_name length = 100 1292 format = $100. 1293 informat = $100. 1294 label = 'first_name', 1295 WCE3LPBD.last_name length = 100 1296 format = $100. 1297 informat = $100. 1298 label = 'last_name', 1299 WCE3LPBD.email_addr length = 100 1300 format = $100. 1301 informat = $100. 1302 label = 'email_addr', 1303 WCE3LPBD.Email_Domain_Valid length = 5 1304 format = $5. 1305 informat = $5. 1306 label = 'Email_Domain_Valid', 1307 WCE3LPBD.Facebook_First_Name length = 100 1308 format = $100. 1309 informat = $100. 1310 label = 'Facebook_First_Name', 1311 WCE3LPBD.Facebook_Last_Name length = 100 1312 format = $100. 1313 informat = $100. 1314 label = 'Facebook_Last_Name', 1315 WCE3LPBD.Facebook_Email_Addr length = 100 1316 format = $100. 1317 informat = $100. 1318 label = 'Facebook_Email_Addr', 1319 WCE3LPBD.Facebook_Verified length = 5 1320 format = $5. 1321 informat = $5. 1322 label = 'Facebook_Verified', 1323 WCE3LPBD.Facebook_Gender length = 10 1324 format = $10. 1325 informat = $10. 1326 label = 'Facebook_Gender', 1327 WCE3LPBD.Facebook_Link length = 100 26 The SAS System 15:00 Tuesday, January 23, 2018 1328 format = $100. 1329 informat = $100. 1330 label = 'Facebook_Link', 1331 WCE3LPBD.Twitter_Name length = 100 1332 format = $100. 1333 informat = $100. 1334 label = 'Twitter_Name', 1335 WCE3LPBD.Twitter_Screen_Name length = 100 1336 format = $100. 1337 informat = $100. 1338 label = 'Twitter_Screen_Name', 1339 WCE3LPBD.Twitter_Verified length = 5 1340 format = $5. 1341 informat = $5. 1342 label = 'Twitter_Verified', 1343 WCE3LPBD.Twitter_Location length = 100 1344 format = $100. 1345 informat = $100. 1346 label = 'Twitter_Location', 1347 WCE3LPBD.Twitter_Num_Friends length = 8 1348 format = 12. 1349 informat = 12. 1350 label = 'Twitter_Num_Friends', 1351 WCE3LPBD.Twitter_Num_Followers length = 8 1352 format = 12. 1353 informat = 12. 1354 label = 'Twitter_Num_Followers', 1355 WCE3LPBD.Twitter_Num_Statuses length = 8 1356 format = 12. 1357 informat = 12. 1358 label = 'Twitter_Num_Statuses', 1359 WCE3LPBD.Twitter_Link length = 100 1360 format = $100. 1361 informat = $100. 1362 label = 'Twitter_Link', 1363 WCE3LPBD.Opted_In_Magic length = 5 1364 format = $5. 1365 informat = $5. 1366 label = 'Opted_In_Magic', 1367 WCE3LPBD.Opted_In_Amway length = 5 1368 format = $5. 1369 informat = $5. 1370 label = 'Opted_In_Amway', 1371 WCE3LPBD.Repeat_Visit length = 5 1372 format = $5. 1373 informat = $5. 1374 label = 'Repeat_Visit', 1375 WCE3LPBD.User_Agent length = 100 1376 format = $100. 1377 informat = $100. 1378 label = 'User_Agent', 1379 datetime() as InsertTime length = 8 1380 format = DATETIME22.3 1381 informat = DATETIME22.3 1382 label = 'InsertTime', 1383 datetime() as LoadTime length = 8 1384 format = DATETIME22.3 1385 informat = DATETIME22.3 27 The SAS System 15:00 Tuesday, January 23, 2018 1386 label = 'LoadTime' 1387 from 1388 work.WCE3LPBD 1389 ; 1390 quit; 1391 1392 %global etls_sql_pushDown; 1393 %let etls_sql_pushDown = &sys_sql_ip_all; 1394 1395 %rcSet(&sqlrc); 1396 1397 1398 1399 /** Step end Set Operators **/ 1400 1401 /*==========================================================================* 1402 * Step: Table Loader A5CJ6MER.C30004BX * 1403 * Transform: Table Loader (Version 2.1) * 1404 * Description: * 1405 * * 1406 * Source Table: Set Operators - work.WCE4DHT6 A5CJ6MER.C40004DB * 1407 * Target Table: stg_device_report - A5CJ6MER.BH0002BS * 1408 * stgamp_1.stg_device_report * 1409 *==========================================================================*/ 1410 1411 %let transformID = %quote(A5CJ6MER.C30004BX); 1412 %let trans_rc = 0; 1413 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1414 1415 %let SYSLAST = %nrquote(work.WCE4DHT6); 1416 1417 %global etls_sql_pushDown; 1418 %let etls_sql_pushDown = -1; 1419 option DBIDIRECTEXEC; 1420 1421 %global etls_tableExist; 1422 %global etls_numIndex; 1423 %global etls_lastTable; 1424 %let etls_tableExist = -1; 1425 %let etls_numIndex = -1; 1426 %let etls_lastTable = &SYSLAST; 1427 1428 /*---- Define load data macro ----*/ 1429 1430 /* -------------------------------------------------------------- 1431 Load Technique Selection: Update - Matching=ModifyUsingIndex, New=ModifyUsingIndex 1432 Blanks may replace non-blanks: true 1433 Constraint and index action selections: 'ASIS','ASIS','ASIS','ASIS' 1434 Additional options selections... 1435 Set unmapped to missing on updates: false 1436 -------------------------------------------------------------- */ 1437 %macro etls_loader; 1438 1439 %let etls_tableOptions = ; 1440 1441 /* Determine if the target table exists */ 1442 %let etls_tableExist = %eval(%sysfunc(exist(stgamp_1.stg_device_report, DATA)) or 1443 %sysfunc(exist(stgamp_1.stg_device_report, VIEW))); 28 The SAS System 15:00 Tuesday, January 23, 2018 1444 1445 %let etls_hasUpdateIndex=0; 1446 1447 /*---- Create a new table ----*/ 1448 %if (&etls_tableExist eq 0) %then 1449 %do; /* if table does not exist */ 1450 1451 %put %str(NOTE: Creating table ...); 1452 1453 data stgamp_1.stg_device_report 1454 (dbnull = ( 1455 wifi_date = NO 1456 mac_address = NO 1457 Portaled = YES 1458 Registered = YES 1459 first_name = YES 1460 last_name = YES 1461 email_addr = YES 1462 Email_Domain_Valid = YES 1463 Facebook_First_Name = YES 1464 Facebook_Last_Name = YES 1465 Facebook_Email_Addr = YES 1466 Facebook_Verified = YES 1467 Facebook_Gender = YES 1468 Facebook_Link = YES 1469 Twitter_Name = YES 1470 Twitter_Screen_Name = YES 1471 Twitter_Verified = YES 1472 Twitter_Location = YES 1473 Twitter_Num_Friends = YES 1474 Twitter_Num_Followers = YES 1475 Twitter_Num_Statuses = YES 1476 Twitter_Link = YES 1477 Opted_In_Magic = YES 1478 Opted_In_Amway = YES 1479 Repeat_Visit = YES 1480 User_Agent = YES 1481 InsertTime = NO 1482 LoadTime = YES)); 1483 attrib wifi_date length = $10 1484 format = $10. 1485 informat = $10. 1486 label = 'wifi_date'; 1487 attrib mac_address length = $50 1488 format = $50. 1489 informat = $50. 1490 label = 'mac_address'; 1491 attrib Portaled length = $5 1492 format = $5. 1493 informat = $5. 1494 label = 'Portaled'; 1495 attrib Registered length = $5 1496 format = $5. 1497 informat = $5. 1498 label = 'Registered'; 1499 attrib first_name length = $100 1500 format = $100. 1501 informat = $100. 29 The SAS System 15:00 Tuesday, January 23, 2018 1502 label = 'first_name'; 1503 attrib last_name length = $100 1504 format = $100. 1505 informat = $100. 1506 label = 'last_name'; 1507 attrib email_addr length = $100 1508 format = $100. 1509 informat = $100. 1510 label = 'email_addr'; 1511 attrib Email_Domain_Valid length = $5 1512 format = $5. 1513 informat = $5. 1514 label = 'Email_Domain_Valid'; 1515 attrib Facebook_First_Name length = $100 1516 format = $100. 1517 informat = $100. 1518 label = 'Facebook_First_Name'; 1519 attrib Facebook_Last_Name length = $100 1520 format = $100. 1521 informat = $100. 1522 label = 'Facebook_Last_Name'; 1523 attrib Facebook_Email_Addr length = $100 1524 format = $100. 1525 informat = $100. 1526 label = 'Facebook_Email_Addr'; 1527 attrib Facebook_Verified length = $5 1528 format = $5. 1529 informat = $5. 1530 label = 'Facebook_Verified'; 1531 attrib Facebook_Gender length = $10 1532 format = $10. 1533 informat = $10. 1534 label = 'Facebook_Gender'; 1535 attrib Facebook_Link length = $100 1536 format = $100. 1537 informat = $100. 1538 label = 'Facebook_Link'; 1539 attrib Twitter_Name length = $100 1540 format = $100. 1541 informat = $100. 1542 label = 'Twitter_Name'; 1543 attrib Twitter_Screen_Name length = $100 1544 format = $100. 1545 informat = $100. 1546 label = 'Twitter_Screen_Name'; 1547 attrib Twitter_Verified length = $5 1548 format = $5. 1549 informat = $5. 1550 label = 'Twitter_Verified'; 1551 attrib Twitter_Location length = $100 1552 format = $100. 1553 informat = $100. 1554 label = 'Twitter_Location'; 1555 attrib Twitter_Num_Friends length = 8 1556 format = 12. 1557 informat = 12. 1558 label = 'Twitter_Num_Friends'; 1559 attrib Twitter_Num_Followers length = 8 30 The SAS System 15:00 Tuesday, January 23, 2018 1560 format = 12. 1561 informat = 12. 1562 label = 'Twitter_Num_Followers'; 1563 attrib Twitter_Num_Statuses length = 8 1564 format = 12. 1565 informat = 12. 1566 label = 'Twitter_Num_Statuses'; 1567 attrib Twitter_Link length = $100 1568 format = $100. 1569 informat = $100. 1570 label = 'Twitter_Link'; 1571 attrib Opted_In_Magic length = $5 1572 format = $5. 1573 informat = $5. 1574 label = 'Opted_In_Magic'; 1575 attrib Opted_In_Amway length = $5 1576 format = $5. 1577 informat = $5. 1578 label = 'Opted_In_Amway'; 1579 attrib Repeat_Visit length = $5 1580 format = $5. 1581 informat = $5. 1582 label = 'Repeat_Visit'; 1583 attrib User_Agent length = $100 1584 format = $100. 1585 informat = $100. 1586 label = 'User_Agent'; 1587 attrib InsertTime length = 8 1588 format = DATETIME22.3 1589 informat = DATETIME22.3 1590 label = 'InsertTime'; 1591 attrib LoadTime length = 8 1592 format = DATETIME22.3 1593 informat = DATETIME22.3 1594 label = 'LoadTime'; 1595 call missing(of _all_); 1596 stop; 1597 run; 1598 1599 %rcSet(&syserr); 1600 1601 %end; /* if table does not exist */ 1602 1603 data stgamp_1.stg_device_report; 1604 1605 set &etls_lastTable 1606 (rename=(Portaled = etls_temp0 1607 Registered = etls_temp1 1608 first_name = etls_temp2 1609 last_name = etls_temp3 1610 email_addr = etls_temp4 1611 Email_Domain_Valid = etls_temp5 1612 Facebook_First_Name = etls_temp6 1613 Facebook_Last_Name = etls_temp7 1614 Facebook_Email_Addr = etls_temp8 1615 Facebook_Verified = etls_temp9 1616 Facebook_Gender = etls_temp10 1617 Facebook_Link = etls_temp11 31 The SAS System 15:00 Tuesday, January 23, 2018 1618 Twitter_Name = etls_temp12 1619 Twitter_Screen_Name = etls_temp13 1620 Twitter_Verified = etls_temp14 1621 Twitter_Location = etls_temp15 1622 Twitter_Num_Friends = etls_temp16 1623 Twitter_Num_Followers = etls_temp17 1624 Twitter_Num_Statuses = etls_temp18 1625 Twitter_Link = etls_temp19 1626 Opted_In_Magic = etls_temp20 1627 Opted_In_Amway = etls_temp21 1628 Repeat_Visit = etls_temp22 1629 User_Agent = etls_temp23 1630 InsertTime = etls_temp24 1631 LoadTime = etls_temp25)) 1632 ; 1633 1634 modify stgamp_1.stg_device_report 1635 ( 1636 cntllev = rec 1637 dbkey = (wifi_date mac_address) 1638 ) key = dbkey; 1639 1640 if _iorc_ in( %sysrc(_DSENMR),%sysrc(_DSENOM),%sysrc(_DSEMTR)) or 1641 _iorc_ eq %sysrc(_SOK) then 1642 do; 1643 Portaled = etls_temp0; 1644 Registered = etls_temp1; 1645 first_name = etls_temp2; 1646 last_name = etls_temp3; 1647 email_addr = etls_temp4; 1648 Email_Domain_Valid = etls_temp5; 1649 Facebook_First_Name = etls_temp6; 1650 Facebook_Last_Name = etls_temp7; 1651 Facebook_Email_Addr = etls_temp8; 1652 Facebook_Verified = etls_temp9; 1653 Facebook_Gender = etls_temp10; 1654 Facebook_Link = etls_temp11; 1655 Twitter_Name = etls_temp12; 1656 Twitter_Screen_Name = etls_temp13; 1657 Twitter_Verified = etls_temp14; 1658 Twitter_Location = etls_temp15; 1659 Twitter_Num_Friends = etls_temp16; 1660 Twitter_Num_Followers = etls_temp17; 1661 Twitter_Num_Statuses = etls_temp18; 1662 Twitter_Link = etls_temp19; 1663 Opted_In_Magic = etls_temp20; 1664 Opted_In_Amway = etls_temp21; 1665 Repeat_Visit = etls_temp22; 1666 User_Agent = etls_temp23; 1667 InsertTime = etls_temp24; 1668 LoadTime = etls_temp25; 1669 1670 /* if the record exists in the master, then replace it */ 1671 if _iorc_ eq %sysrc(_SOK) then 1672 replace; 1673 1674 /* if the record does not exist in the master, then add it */ 1675 else 32 The SAS System 15:00 Tuesday, January 23, 2018 1676 output; 1677 end; 1678 1679 _iorc_ = 0; 1680 _error_ = 0; 1681 1682 run; 1683 1684 %rcSet(&syserr); 1685 1686 %mend etls_loader; 1687 %etls_loader; 1688 1689 %let etls_recnt = 0; 1690 %macro etls_recordCheck; 1691 %let etls_recCheckExist = %eval(%sysfunc(exist(stgamp_1.stg_device_report, DATA)) or 1692 %sysfunc(exist(stgamp_1.stg_device_report, VIEW))); 1693 1694 %if (&etls_recCheckExist) %then 1695 %do; 1696 proc sql noprint; 1697 select count(*) into :etls_recnt from stgamp_1.stg_device_report; 1698 quit; 1699 %end; 1700 %mend etls_recordCheck; 1701 %etls_recordCheck; 1702 1703 1704 1705 /** Step end Table Loader **/ 1706 1707 /*==========================================================================* 1708 * Step: Process Stamp Update AMP A5CJ6MER.C30004BY * 1709 * Transform: Process Stamp Update AMP * 1710 * Description: * 1711 *==========================================================================*/ 1712 1713 %let transformID = %quote(A5CJ6MER.C30004BY); 1714 %let trans_rc = 0; 1715 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 1716 1717 %let _INPUT_count = 0; 1718 %let _OUTPUT_count = 0; 1719 1720 1721 1722 1723 1724 1725 proc sql ; 1726 create table timegap as 1727 select distinct 1728 (select max(input(put(dateinfo,8.),YYMMdd8.)) from Filenames_parse)-(select input(put(last_process,8.),YYMMdd8.) from 1728 ! &stg_libname..Process_timestamp where file="&tablename") as timegapvar 1729 from &stg_libname..Process_timestamp; 1730 quit ; 1731 1732 33 The SAS System 15:00 Tuesday, January 23, 2018 1733 %timegap_second_check ; 1734 1735 1736 1737 proc sql; 1738 update &stg_libname..Process_timestamp 1739 set last_process=case when (select max(dateinfo) from Filenames_parse)=. then last_process else (select 1739 ! max(dateinfo) from Filenames_parse) end 1740 where file="&tablename" ; 1741 quit ; 1742 1743 1744 %EXIT: 1745 1746 %mend fullprogramwrap; 1747 1748 %fullprogramwrap ; NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds NOTE: The data set WORK.FILENAMES has 0 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Fileref _DIR_ has been deassigned. NOTE: Table WORK.FILENAMES_PARSE created, with 0 rows and 3 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: Table WORK.FILENAMES_PARSE created, with 0 rows and 3 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.04 seconds cpu time 0.03 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 34 The SAS System 15:00 Tuesday, January 23, 2018 NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: Table WORK.LAYOUT1 created, with 26 rows and 8 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.00 seconds NOTE: Table WORK.LAYOUT2 created, with 26 rows and 4 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Statement terminated early due to OUTOBS=1 option. NOTE: Table WORK.INFORMAT_STATEMENT created, with 1 rows and 1 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Statement terminated early due to OUTOBS=1 option. NOTE: Table WORK.FORMAT_STATEMENT created, with 1 rows and 1 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 35 The SAS System 15:00 Tuesday, January 23, 2018 WARNING: Statement terminated early due to OUTOBS=1 option. NOTE: Table WORK.TYPE_STATEMENT created, with 1 rows and 1 columns. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.03 seconds NOTE: No rows were selected. NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds WARNING: Apparent symbolic reference IMPORTFILENAME not resolved. NOTE: Variable "c" was given a default length of 32767 as the result of a function call. If you do not like this, please use a LENGTH statement to declare "c". ERROR: A component of C:\Users\sassupport\AppData\Local\Temp\ftp://ud4b1:password@orlandomagic.sharefileftp.com\Ampthink\&importfilename. is not a directory. ERROR: A component of C:\Users\sassupport\AppData\Local\Temp\ftp://ud4b1:password@orlandomagic.sharefileftp.com\Ampthink\translate_temp.csv is not a directory. NOTE: The SAS System stopped processing this step because of errors. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ERROR: A component of C:\Users\sassupport\AppData\Local\Temp\ftp://ud4b1:password@orlandomagic.sharefileftp.com\Ampthink\translate_temp.csv is not a directory. NOTE: The SAS System stopped processing this step because of errors. NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set. WARNING: The data set WORK.RETENTION may be incomplete. When this step was stopped there were 0 observations and 26 variables. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.01 seconds 36 The SAS System 15:00 Tuesday, January 23, 2018 NOTE: Table WORK.WCE2YIWC created, with 0 rows and 28 columns. NOTE: No rows were deleted from WORK.WCE2YIWC. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.01 seconds cpu time 0.01 seconds NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: 1 row was updated in STGAMP_1.Process_timestamp. NOTE: PROCEDURE SQL used (Total process time): real time 0.03 seconds cpu time 0.00 seconds 1749 1750 %rcSet(&syserr); 1751 %rcSet(&sysrc); 1752 %rcSet(&sqlrc); 1753 1754 1755 1756 /** Step end Process Stamp Update AMP **/ 1757 1758 %let etls_endTime = %sysfunc(datetime(),datetime.); 1759 ERROR: Errors printed on page 35. NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414 NOTE: The SAS System used: real time 1.40 seconds cpu time 0.88 seconds