Hi LinusH, Please see the log below. We've not modified the source and target tables since our job not executed successfully so far. 1 The SAS System 01:35 Sunday, April 7, 2013 NOTE: Copyright (c) 2002-2010 by SAS Institute Inc., Cary, NC, USA. NOTE: SAS (r) Proprietary Software 9.3 (TS1M1) Licensed to Nelson Kew How Loon-E11605, Site 70068130. NOTE: This session is executing on the X64_S08R2 platform. NOTE: Updated analytical products: SAS/STAT 9.3_M1, SAS/ETS 9.3_M1, SAS/OR 9.3_M1 NOTE: SAS Initialization used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: The autoexec file, C:\SAS\EBIEDIEG\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization. 1 data work.sasver; 2 ver= "&sysvlong"; 3 run; NOTE: The data set WORK.SASVER has 1 observations and 1 variables. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds 4 2 The SAS System 01:35 Sunday, April 7, 2013 5 %let etls_resetRestart = 1; 6 /**************************************************************************** 7 * Job: SCD1 A5XNF4E5.AG00067J * 8 * Description: * 9 * * 10 * Metadata Server: SASEDU * 11 * Port: 8561 * 12 * Location: /SK_REP/SK Jobs * 13 * * 14 * Server: SASApp A509OXYP.AR000002 * 15 * * 16 * Source Table: SCD1 - sk_lib.SCD1 A5XNF4E5.AC0006C2 * 17 * Target Table: SCD_1 - sk_tar.SCD_1 A5XNF4E5.AC0006C4 * 18 * * 19 * Generated on: Sunday, April 7, 2013 1:35:49 AM IST * 20 * Generated by: sasdemo@SASEDU * 21 * Version: SAS Data Integration Studio 4.3 * 22 ****************************************************************************/ 23 24 /* General macro variables */ 25 %let jobID = %quote(A5XNF4E5.AG00067J); 26 %let etls_jobName = %nrquote(SCD1); 27 %let etls_userID = %nrquote(sasdemo@SASEDU); 28 29 /* Performance Statistics require ARM_PROC sub-system */ 30 %macro etls_startPerformanceStats; 31 %log4sas(); 32 %log4sas_logger(Perf.ARM, 'level=info'); 33 options armagent=log4sas armsubsys=(ARM_PROC); 34 %global _armexec; 35 %let _armexec = 1; 36 %perfinit(applname="SAS Data Integration Studio"); 37 %global etls_recnt; 38 %let etls_recnt=-1; 39 %mend; 40 %etls_startPerformanceStats; NOTE: PROCEDURE| _DISARM| REGISTER| _DISARM| 2013-04-07T01:35:49,467+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _DISARM| | _ENDDISARM 41 42 %macro etls_setArmagent; 43 %let armagentLength = %length(%sysfunc(getoption(armagent))); 44 %if (&armagentLength eq 0) %then 45 %do; 46 %log4sas(); 47 %log4sas_logger(Perf.ARM, 'level=info'); 48 options armagent=log4sas armsubsys=(ARM_PROC); 49 %end; 50 %mend etls_setArmagent; 51 52 %macro etls_setPerfInit; 53 %if "&_perfinit" eq "0" %then 54 %do; 55 %etls_setArmagent; 56 %global _armexec; 57 %let _armexec = 1; 58 %perfinit(applname="SAS Data Integration Studio"); 59 %end; 3 The SAS System 01:35 Sunday, April 7, 2013 60 %mend etls_setPerfInit; 61 62 /* Setup to capture return codes */ 63 %global job_rc trans_rc sqlrc; 64 %let sysrc = 0; 65 %let job_rc = 0; 66 %let trans_rc = 0; 67 %let sqlrc = 0; 68 %global etls_stepStartTime; 69 /* initialize syserr to 0 */ 70 data _null_; run; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,476+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 6332416| _DISARM| 6328320| _DISARM| 10| _DISARM| 10| _DISARM| 0| _DISARM| 934564| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.476000| _DISARM| 1680897949.477000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 71 72 %macro rcSet(error); 73 %if (&error gt &trans_rc) %then 74 %let trans_rc = &error; 75 %if (&error gt &job_rc) %then 76 %let job_rc = &error; 77 %mend rcSet; 78 79 %macro rcSetDS(error); 80 if &error gt input(symget('trans_rc'),12.) then 81 call symput('trans_rc',trim(left(put(&error,12.)))); 82 if &error gt input(symget('job_rc'),12.) then 83 call symput('job_rc',trim(left(put(&error,12.)))); 84 %mend rcSetDS; 85 86 /* Create metadata macro variables */ 87 %let IOMServer = %nrquote(SASApp); 88 %let metaPort = %nrquote(8561); 89 %let metaServer = %nrquote(SASEDU); 90 91 /* Set metadata options */ 92 options metaport = &metaPort 93 metaserver = "&metaServer"; 94 95 /* Setup for capturing job status */ 96 %let etls_startTime = %sysfunc(datetime(),datetime.); 97 %let etls_recordsBefore = 0; 98 %let etls_recordsAfter = 0; 99 %let etls_lib = 0; 100 %let etls_table = 0; 101 102 %global etls_debug; 103 %macro etls_setDebug; 104 %if %str(&etls_debug) ne 0 %then 105 OPTIONS MPRINT%str(;); 106 %mend; 107 %etls_setDebug; 108 4 The SAS System 01:35 Sunday, April 7, 2013 109 5 The SAS System 01:35 Sunday, April 7, 2013 110 /*==========================================================================* 111 * Step: SCD Type 1 A5XNF4E5.AJ00068U * 112 * Transform: SCD Type 1 * 113 * Description: * 114 * * 115 * Source Table: SCD1 - sk_lib.SCD1 A5XNF4E5.AC0006C2 * 116 * Target Tables: SCD_1 - sk_tar.SCD_1 A5XNF4E5.AC0006C4 * 117 * Cross reference - work.WEYJRLSG A5XNF4E5.AS00034S * 118 * Changed records - work.WEYJRMS0 A5XNF4E5.AS00034T * 119 * New records - work.WEYJRNSF A5XNF4E5.AS00034U * 120 *==========================================================================*/ 121 122 %let transformID = %quote(A5XNF4E5.AJ00068U); 123 %let trans_rc = 0; 124 %let etls_stepStartTime = %sysfunc(datetime(), datetime20.); 125 126 /* Access the data for Sk_dm_s */ 127 LIBNAME sk_lib BASE "C:\sk_dm\Sk_source"; NOTE: Libref SK_LIB was successfully assigned as follows: Engine: BASE Physical Name: C:\sk_dm\Sk_source 128 %rcSet(&syslibrc); 129 130 /* Access the data for Sk_dm_tar */ 131 LIBNAME sk_tar BASE "C:\sk_dm\sk_target"; NOTE: Libref SK_TAR was successfully assigned as follows: Engine: BASE Physical Name: C:\sk_dm\sk_target 132 %rcSet(&syslibrc); 133 134 %let etls_recCheckExist = 0; 135 %let etls_recnt = 0; 136 %macro etls_recordCheck; 137 %let etls_recCheckExist = %eval(%sysfunc(exist(sk_lib.SCD1, DATA)) or 138 %sysfunc(exist(sk_lib.SCD1, VIEW))); 139 140 %if (&etls_recCheckExist) %then 141 %do; 142 %local etls_syntaxcheck; 143 %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 144 /* Turn off syntaxcheck option to perform following steps */ 145 options nosyntaxcheck; 146 147 proc contents data = sk_lib.SCD1 out = work.etls_contents(keep = nobs) noprint; 148 run; 149 150 data _null_; 151 set work.etls_contents (obs = 1); 152 call symput("etls_recnt", left(put(nobs,32.))); 153 run; 154 155 proc datasets lib = work nolist nowarn memtype = (data view); 156 delete etls_contents; 157 quit; 158 159 /* Reset syntaxcheck option to previous setting */ 160 options &etls_syntaxcheck; 161 %end; 6 The SAS System 01:35 Sunday, April 7, 2013 162 %mend etls_recordCheck; 163 %etls_recordCheck; MPRINT(ETLS_RECORDCHECK): options nosyntaxcheck; MPRINT(ETLS_RECORDCHECK): proc contents data = sk_lib.SCD1 out = work.etls_contents(keep = nobs) noprint; MPRINT(ETLS_RECORDCHECK): run; NOTE: The data set WORK.ETLS_CONTENTS has 4 observations and 1 variables. NOTE: PROCEDURE CONTENTS used (Total process time): real time 0.03 seconds cpu time 0.03 seconds NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,523+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 7901184| _DISARM| 7901184| _DISARM| 10| _DISARM| 10| _DISARM| 141316| _DISARM| 1102504| _DISARM| 0.031200| _DISARM| 0.034000| _DISARM| 1680897949.490000| _DISARM| 1680897949.524000| _DISARM| 0.015600| _DISARM| | _ENDDISARM MPRINT(ETLS_RECORDCHECK): data _null_; MPRINT(ETLS_RECORDCHECK): set work.etls_contents (obs = 1); MPRINT(ETLS_RECORDCHECK): call symput("etls_recnt", left(put(nobs,32.))); MPRINT(ETLS_RECORDCHECK): run; NOTE: There were 1 observations read from the data set WORK.ETLS_CONTENTS. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,526+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 7901184| _DISARM| 7901184| _DISARM| 10| _DISARM| 10| _DISARM| 5120| _DISARM| 1108136| _DISARM| 0.000000| _DISARM| 0.002000| _DISARM| 1680897949.525000| _DISARM| 1680897949.527000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_RECORDCHECK): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_RECORDCHECK): delete etls_contents; MPRINT(ETLS_RECORDCHECK): quit; NOTE: Deleting WORK.ETLS_CONTENTS (memtype=DATA). NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,529+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 7901184| _DISARM| 7901184| _DISARM| 10| _DISARM| 10| _DISARM| 7168| _DISARM| 1115816| _DISARM| 0.000000| _DISARM| 0.002000| _DISARM| 1680897949.528000| _DISARM| 1680897949.530000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_RECORDCHECK): options SYNTAXCHECK; 164 165 %let SYSLAST = %nrquote(sk_lib.SCD1); 166 167 /* Runtime statistics macros */ 168 %etls_setPerfInit; 169 %perfstrt(txnname=%BQUOTE(_DISARM|&transformID|&syshostname|SCDType1), metrNam6=_DISROWCNT, metrDef6=Count32) ; MPRINT(PERFSTRT): options notes nosource nosource2 nosymbolgen nomprint nomlogic MPRINT(PERFSTRT): NOSYMBOLGEN ; MPRINT(PERFSTRT): ; MPRINT(PERFSTRT): ; 170 171 %macro etls_scd_type_1; 172 173 /*---- Delete any pre-existing work tables ----*/ 174 proc datasets lib = work nolist nowarn memtype = (data view); 7 The SAS System 01:35 Sunday, April 7, 2013 175 delete etls_xref; 176 quit; 177 178 proc datasets lib = work nolist nowarn memtype = (data view); 179 delete WEYJRMS0; 180 quit; 181 182 proc datasets lib = work nolist nowarn memtype = (data view); 183 delete WEYJRNSF; 184 quit; 185 186 /* Determine if the table exists */ 187 %let etls_tableExist = %eval(%sysfunc(exist(sk_tar.SCD_1, DATA)) or 188 %sysfunc(exist(sk_tar.SCD_1, VIEW))); 189 190 /*---- Create a new table ----*/ 191 %if (&etls_tableExist eq 0) %then 192 %do; /* if table does not exist */ 193 194 %put %str(NOTE: Creating table ...); 195 196 data sk_tar.SCD_1; 197 attrib SRLNO length = 8; 198 attrib RollNo length = 8 199 label = 'RollNo'; 200 attrib NAME length = $2 201 format = $2. 202 informat = $2. 203 label = 'NAME'; 204 attrib CITY length = $10 205 format = $10. 206 informat = $10. 207 label = 'CITY'; 208 attrib STATE length = $6 209 format = $6. 210 informat = $6. 211 label = 'STATE'; 212 attrib Last_Update length = 8 213 format = Datetime. 214 informat = Datetime.; 215 call missing(of _all_); 216 stop; 217 run; 218 219 %rcSet(&syserr); 220 221 %end; /* if table does not exist */ 222 223 /* Determine if the table exists */ 224 %let etls_tableExist = %eval(%sysfunc(exist(work.WEYJRLSG, DATA)) or 225 %sysfunc(exist(work.WEYJRLSG, VIEW))); 226 227 /*---- Create a new table ----*/ 228 %if (&etls_tableExist eq 0) %then 229 %do; /* if table does not exist */ 230 231 %put %str(NOTE: Creating table ...); 232 8 The SAS System 01:35 Sunday, April 7, 2013 233 data work.WEYJRLSG; 234 attrib SRLNO length = 8; 235 attrib RollNo length = 8 236 label = 'RollNo'; 237 attrib compare_digest length = $32 238 format = $32. 239 informat = $32.; 240 call missing(of _all_); 241 stop; 242 run; 243 244 %rcSet(&syserr); 245 246 %end; /* if table does not exist */ 247 248 proc datasets lib = work nolist nowarn memtype = (data view); 249 delete WF2W8456; 250 quit; 251 252 proc sql; 253 create view work.WF2W8456 as 254 select 255 . as SRLNO length = 8, 256 RollNo, 257 NAME, 258 CITY, 259 STATE, 260 . as Last_Update length = 8 261 format = Datetime. 262 informat = Datetime. 263 from sk_lib.SCD1 264 ; 265 quit; 266 267 %let SYSLAST = work.WF2W8456; 268 269 /* create work xref table */ 270 data work.etls_xref(keep = SRLNO RollNo compare_digest); 271 272 length compare_digest $ 32; 273 274 set sk_tar.SCD_1( keep = SRLNO RollNo NAME CITY STATE); 275 276 /* create digest */ 277 compare_digest = put(md5(catq(' ', NAME, CITY, STATE)), $hex32.); 278 279 run; 280 %rcSet(&syscc); 281 282 /*---- get the maximum surrogate key value ----*/ 283 %let etls_maxkey = -1; 284 285 proc sql noprint; 286 select compress(put(max(SRLNO), best32.)) 287 into :etls_maxkey 288 from sk_tar.SCD_1; 289 quit; 290 9 The SAS System 01:35 Sunday, April 7, 2013 291 /* if etls_maxkey value is missing then set it to zero. */ 292 %if (&etls_maxkey eq .) 293 %then %let etls_maxkey = 0; 294 295 /*---- SCD Type 1 processing: hash lookup method ----*/ 296 data 297 /* Changed Records Table */ 298 work.WEYJRMS0( keep = SRLNO RollNo NAME CITY STATE Last_Update compare_digest) 299 300 /* New Records Table */ 301 work.WEYJRNSF; 302 303 drop source_digest NewMaxKey; 304 305 retain NewMaxKey &etls_maxkey; 306 307 length source_digest $ 32; 308 309 if 0 then 310 do; 311 set work.etls_xref; 312 set sk_tar.SCD_1(keep = Last_Update); 313 end; 314 315 if _N_ eq 1 then 316 do; 317 declare hash hct(dataset: 'work.etls_xref', hashexp: 10); 318 hct.defineKey("RollNo"); 319 hct.defineData("SRLNO", "compare_digest"); 320 hct.defineDone(); 321 end; 322 323 set work.WF2W8456; 324 325 /* create digest */ 326 source_digest = put(md5(catq(' ', NAME, CITY, STATE)), $hex32.); 327 328 /* match */ 329 if hct.find() eq 0 then 330 do; 331 /* scd type 1 records */ 332 if source_digest ne compare_digest then 333 do; 334 Last_Update = DATETIME(); 335 compare_digest = source_digest; 336 output work.WEYJRMS0; 337 end; 338 end; 339 /* new records */ 340 else if hct.find() ne 0 then 341 do; 342 NewMaxKey = sum(NewMaxKey, 1); 343 SRLNO = NewMaxKey; 344 Last_Update = DATETIME(); 345 compare_digest = source_digest; 346 output work.WEYJRNSF; 347 end; 348 10 The SAS System 01:35 Sunday, April 7, 2013 349 run; 350 %rcSet(&syscc); 351 352 /* changed records table - record check */ 353 %let etls_recCheckExist = 0; 354 %let etls_change_rows = 0; 355 %macro etls_recordCheck; 356 %let etls_recCheckExist = %eval(%sysfunc(exist(work.WEYJRMS0, DATA)) or 357 %sysfunc(exist(work.WEYJRMS0, VIEW))); 358 359 %if (&etls_recCheckExist) %then 360 %do; 361 %local etls_syntaxcheck; 362 %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 363 /* Turn off syntaxcheck option to perform following steps */ 364 options nosyntaxcheck; 365 366 data _null_; 367 set work.WEYJRMS0( obs=1 ); 368 call symput("etls_change_rows",'1'); 369 run; 370 /* Reset syntaxcheck option to previous setting */ 371 options &etls_syntaxcheck; 372 %end; 373 %mend etls_recordCheck; 374 %etls_recordCheck; 375 376 %rcSet(&syscc); 377 378 /* new records table - record check */ 379 %let etls_recCheckExist = 0; 380 %let etls_new_rows = 0; 381 %macro etls_recordCheck; 382 %let etls_recCheckExist = %eval(%sysfunc(exist(work.WEYJRNSF, DATA)) or 383 %sysfunc(exist(work.WEYJRNSF, VIEW))); 384 385 %if (&etls_recCheckExist) %then 386 %do; 387 %local etls_syntaxcheck; 388 %let etls_syntaxcheck = %sysfunc(getoption(syntaxcheck)); 389 /* Turn off syntaxcheck option to perform following steps */ 390 options nosyntaxcheck; 391 392 data _null_; 393 set work.WEYJRNSF( obs=1 ); 394 call symput("etls_new_rows",'1'); 395 run; 396 /* Reset syntaxcheck option to previous setting */ 397 options &etls_syntaxcheck; 398 %end; 399 %mend etls_recordCheck; 400 %etls_recordCheck; 401 402 %rcSet(&syscc); 403 404 /*---- target table: update/append rows ----*/ 405 406 /* target table: update change rows */ 11 The SAS System 01:35 Sunday, April 7, 2013 407 %if &etls_change_rows ge 1 %then 408 %do; 409 proc sql; 410 update sk_tar.SCD_1 as m 411 set NAME = (select NAME from work.WEYJRMS0 as t 412 where m.SRLNO = t.SRLNO), 413 CITY = (select CITY from work.WEYJRMS0 as t 414 where m.SRLNO = t.SRLNO), 415 STATE = (select STATE from work.WEYJRMS0 as t 416 where m.SRLNO = t.SRLNO), 417 Last_Update = (select Last_Update from work.WEYJRMS0 as t 418 where m.SRLNO = t.SRLNO) 419 where SRLNO in (select distinct SRLNO from work.WEYJRMS0); 420 quit; 421 %rcSet(&syscc); 422 %end; 423 424 /* target table - append new rows */ 425 %if &etls_new_rows ge 1 %then 426 %do; 427 proc append base = sk_tar.SCD_1 428 data = work.WEYJRNSF force nowarn; 429 run; 430 %rcSet(&syscc); 431 %end; 432 433 /*---- cross reference table: update/append rows ----*/ 434 435 /* cross reference table: update change rows */ 436 %if &etls_change_rows ge 1 %then 437 %do; 438 proc sql; 439 update work.WEYJRLSG as m 440 set compare_digest = (select compare_digest from work.WEYJRMS0 as t 441 where m.SRLNO = t.SRLNO) 442 where SRLNO in (select distinct SRLNO from work.WEYJRMS0); 443 quit; 444 %rcSet(&syscc); 445 %end; 446 447 /* cross reference table - append new rows */ 448 %if &etls_new_rows ge 1 %then 449 %do; 450 proc append base = work.WEYJRLSG 451 data = work.WEYJRNSF( keep = SRLNO RollNo compare_digest) 452 force nowarn; 453 run; 454 %rcSet(&syscc); 455 %end; 456 457 /* Delete work.etls_xref table */ 458 proc datasets lib = work nolist nowarn memtype = (data view); 459 delete etls_xref; 460 quit; 461 462 %mend etls_scd_type_1; 463 464 /* execute etls_scd_type_1 */ 12 The SAS System 01:35 Sunday, April 7, 2013 465 %etls_scd_type_1 MPRINT(ETLS_SCD_TYPE_1): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_SCD_TYPE_1): delete etls_xref; MPRINT(ETLS_SCD_TYPE_1): quit; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,540+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 7901184| _DISARM| 10| _DISARM| 10| _DISARM| 1024| _DISARM| 1317353| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.540000| _DISARM| 1680897949.541000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_SCD_TYPE_1): delete WEYJRMS0; MPRINT(ETLS_SCD_TYPE_1): quit; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,542+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 7901184| _DISARM| 10| _DISARM| 10| _DISARM| 1024| _DISARM| 1318889| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.542000| _DISARM| 1680897949.543000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_SCD_TYPE_1): delete WEYJRNSF; MPRINT(ETLS_SCD_TYPE_1): quit; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,544+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 7901184| _DISARM| 10| _DISARM| 10| _DISARM| 1024| _DISARM| 1320425| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.544000| _DISARM| 1680897949.545000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Creating table ... MPRINT(ETLS_SCD_TYPE_1): data work.WEYJRLSG; MPRINT(ETLS_SCD_TYPE_1): attrib SRLNO length = 8; MPRINT(ETLS_SCD_TYPE_1): attrib RollNo length = 8 label = 'RollNo'; MPRINT(ETLS_SCD_TYPE_1): attrib compare_digest length = $32 format = $32. informat = $32.; MPRINT(ETLS_SCD_TYPE_1): call missing(of _all_); MPRINT(ETLS_SCD_TYPE_1): stop; MPRINT(ETLS_SCD_TYPE_1): run; NOTE: The data set WORK.WEYJRLSG has 0 observations and 3 variables. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,548+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 5120| _DISARM| 1326057| _DISARM| 0.000000| _DISARM| 0.002000| _DISARM| 1680897949.547000| _DISARM| 1680897949.549000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): ; MPRINT(ETLS_SCD_TYPE_1): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_SCD_TYPE_1): delete WF2W8456; 13 The SAS System 01:35 Sunday, April 7, 2013 MPRINT(ETLS_SCD_TYPE_1): quit; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,550+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 1024| _DISARM| 1331689| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.550000| _DISARM| 1680897949.551000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): proc sql; MPRINT(ETLS_SCD_TYPE_1): create view work.WF2W8456 as select . as SRLNO length = 8, RollNo, NAME, CITY, STATE, . as Last_Update length = 8 format = Datetime. informat = Datetime. from sk_lib.SCD1 ; NOTE: SQL view WORK.WF2W8456 has been defined. MPRINT(ETLS_SCD_TYPE_1): quit; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,554+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 8196| _DISARM| 1340397| _DISARM| 0.000000| _DISARM| 0.002000| _DISARM| 1680897949.553000| _DISARM| 1680897949.555000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): data work.etls_xref(keep = SRLNO RollNo compare_digest); MPRINT(ETLS_SCD_TYPE_1): length compare_digest $ 32; MPRINT(ETLS_SCD_TYPE_1): set sk_tar.SCD_1( keep = SRLNO RollNo NAME CITY STATE); ERROR: The variable SRLNO in the DROP, KEEP, or RENAME list has never been referenced. ERROR: The variable RollNo in the DROP, KEEP, or RENAME list has never been referenced. ERROR: The variable NAME in the DROP, KEEP, or RENAME list has never been referenced. ERROR: The variable CITY in the DROP, KEEP, or RENAME list has never been referenced. ERROR: The variable STATE in the DROP, KEEP, or RENAME list has never been referenced. MPRINT(ETLS_SCD_TYPE_1): compare_digest = put(md5(catq(' ', NAME, CITY, STATE)), $hex32.); MPRINT(ETLS_SCD_TYPE_1): run; WARNING: The variable SRLNO in the DROP, KEEP, or RENAME list has never been referenced. WARNING: The variable RollNo in the DROP, KEEP, or RENAME list has never been referenced. 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.ETLS_XREF may be incomplete. When this step was stopped there were 0 observations and 1 variables. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,557+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 13824| _DISARM| 1354221| _DISARM| 0.000000| _DISARM| 0.003000| _DISARM| 1680897949.555000| _DISARM| 1680897949.558000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): ; MPRINT(ETLS_SCD_TYPE_1): proc sql noprint; MPRINT(ETLS_SCD_TYPE_1): select compress(put(max(SRLNO), best32.)) into :etls_maxkey from sk_tar.SCD_1; ERROR: Numeric format BEST in PUT function requires a numeric argument. ERROR: The following columns were not found in the contributing tables: SRLNO. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. MPRINT(ETLS_SCD_TYPE_1): quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,559+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 6656| _DISARM| 1361389| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.559000| _DISARM| 1680897949.560000| _DISARM| 0.000000| _DISARM| | _ENDDISARM 14 The SAS System 01:35 Sunday, April 7, 2013 NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): data work.WEYJRMS0( keep = SRLNO RollNo NAME CITY STATE Last_Update compare_digest) work.WEYJRNSF; MPRINT(ETLS_SCD_TYPE_1): drop source_digest NewMaxKey; MPRINT(ETLS_SCD_TYPE_1): retain NewMaxKey -1; MPRINT(ETLS_SCD_TYPE_1): length source_digest $ 32; MPRINT(ETLS_SCD_TYPE_1): if 0 then do; MPRINT(ETLS_SCD_TYPE_1): set work.etls_xref; MPRINT(ETLS_SCD_TYPE_1): set sk_tar.SCD_1(keep = Last_Update); ERROR: The variable Last_Update in the DROP, KEEP, or RENAME list has never been referenced. MPRINT(ETLS_SCD_TYPE_1): end; MPRINT(ETLS_SCD_TYPE_1): if _N_ eq 1 then do; MPRINT(ETLS_SCD_TYPE_1): declare hash hct(dataset: 'work.etls_xref', hashexp: 10); MPRINT(ETLS_SCD_TYPE_1): hct.defineKey("RollNo"); MPRINT(ETLS_SCD_TYPE_1): hct.defineData("SRLNO", "compare_digest"); MPRINT(ETLS_SCD_TYPE_1): hct.defineDone(); MPRINT(ETLS_SCD_TYPE_1): end; MPRINT(ETLS_SCD_TYPE_1): set work.WF2W8456; MPRINT(ETLS_SCD_TYPE_1): source_digest = put(md5(catq(' ', NAME, CITY, STATE)), $hex32.); MPRINT(ETLS_SCD_TYPE_1): if hct.find() eq 0 then do; MPRINT(ETLS_SCD_TYPE_1): if source_digest ne compare_digest then do; MPRINT(ETLS_SCD_TYPE_1): Last_Update = DATETIME(); MPRINT(ETLS_SCD_TYPE_1): compare_digest = source_digest; MPRINT(ETLS_SCD_TYPE_1): output work.WEYJRMS0; MPRINT(ETLS_SCD_TYPE_1): end; MPRINT(ETLS_SCD_TYPE_1): end; MPRINT(ETLS_SCD_TYPE_1): else if hct.find() ne 0 then do; MPRINT(ETLS_SCD_TYPE_1): NewMaxKey = sum(NewMaxKey, 1); MPRINT(ETLS_SCD_TYPE_1): SRLNO = NewMaxKey; MPRINT(ETLS_SCD_TYPE_1): Last_Update = DATETIME(); MPRINT(ETLS_SCD_TYPE_1): compare_digest = source_digest; MPRINT(ETLS_SCD_TYPE_1): output work.WEYJRNSF; MPRINT(ETLS_SCD_TYPE_1): end; MPRINT(ETLS_SCD_TYPE_1): run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.WEYJRMS0 may be incomplete. When this step was stopped there were 0 observations and 7 variables. WARNING: The data set WORK.WEYJRNSF may be incomplete. When this step was stopped there were 0 observations and 7 variables. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,566+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 36864| _DISARM| 1398765| _DISARM| 0.000000| _DISARM| 0.006000| _DISARM| 1680897949.561000| _DISARM| 1680897949.567000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_SCD_TYPE_1): ; MPRINT(ETLS_RECORDCHECK): options nosyntaxcheck; MPRINT(ETLS_RECORDCHECK): data _null_; MPRINT(ETLS_RECORDCHECK): set work.WEYJRMS0( obs=1 ); MPRINT(ETLS_RECORDCHECK): call symput("etls_change_rows",'1'); MPRINT(ETLS_RECORDCHECK): run; NOTE: There were 0 observations read from the data set WORK.WEYJRMS0. 15 The SAS System 01:35 Sunday, April 7, 2013 NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,569+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 9216| _DISARM| 1437165| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.569000| _DISARM| 1680897949.570000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_RECORDCHECK): options SYNTAXCHECK; MPRINT(ETLS_SCD_TYPE_1): ; MPRINT(ETLS_SCD_TYPE_1): ; MPRINT(ETLS_RECORDCHECK): options nosyntaxcheck; MPRINT(ETLS_RECORDCHECK): data _null_; MPRINT(ETLS_RECORDCHECK): set work.WEYJRNSF( obs=1 ); MPRINT(ETLS_RECORDCHECK): call symput("etls_new_rows",'1'); MPRINT(ETLS_RECORDCHECK): run; NOTE: There were 0 observations read from the data set WORK.WEYJRNSF. NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,572+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 8704| _DISARM| 1450477| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.572000| _DISARM| 1680897949.573000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds MPRINT(ETLS_RECORDCHECK): options SYNTAXCHECK; MPRINT(ETLS_SCD_TYPE_1): ; MPRINT(ETLS_SCD_TYPE_1): ; MPRINT(ETLS_SCD_TYPE_1): proc datasets lib = work nolist nowarn memtype = (data view); MPRINT(ETLS_SCD_TYPE_1): delete etls_xref; MPRINT(ETLS_SCD_TYPE_1): quit; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,575+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8167424| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 1024| _DISARM| 1452013| _DISARM| 0.000000| _DISARM| 0.002000| _DISARM| 1680897949.574000| _DISARM| 1680897949.576000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: PROCEDURE DATASETS used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 466 467 %perfstop(metrVal6=%sysfunc(max(&etls_recnt,-1))); MPRINT(PERFSTOP): options notes nosource nosource2 nosymbolgen nomprint nomlogic NOTE: _DISARM|A5XNF4E5.AJ00068U|SASEDU|SCDType1| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,578+05:1800| _DISARM| SAS_Data_Integration_Studio| _DISARM| SAS| _DISARM| 9| _DISARM| 8429568| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 298621| _DISARM| 1483366| _DISARM| 0.062401| _DISARM| 0.044000| _DISARM| 1680897949.535000| _DISARM| 1680897949.579000| _DISARM| 0.062401| _DISARM| | _ENDDISARM MPRINT(PERFSTOP): NOSYMBOLGEN ; MPRINT(PERFSTOP): ; MPRINT(PERFSTOP): ; 468 %let etls_recnt=-1; 469 470 471 472 /** Step end SCD Type 1 **/ 473 474 16 The SAS System 01:35 Sunday, April 7, 2013 475 %let etls_endTime = %sysfunc(datetime(),datetime.); 476 477 /* Turn off performance statistics collection */ 478 data _null_; 479 if "&_perfinit" eq "1" then 480 call execute('%perfend;'); 481 482 run; NOTE: PROCEDURE| _DISARM| STOP| _DISARM| 2013-04-07T01:35:49,585+05:1800| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | _DISARM| 8429568| _DISARM| 8163328| _DISARM| 10| _DISARM| 10| _DISARM| 0| _DISARM| 1483878| _DISARM| 0.000000| _DISARM| 0.001000| _DISARM| 1680897949.585000| _DISARM| 1680897949.586000| _DISARM| 0.000000| _DISARM| | _ENDDISARM NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 483
... View more