Re: SCD Type-1 Transfomation

lll — Tue, 22 Jul 2014 14:31:41 GMT

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: 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

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 ****************************************************************************/

24 /* General macro variables */

25 %let jobID = %quote(A5XNF4E5.AG00067J);

26 %let etls_jobName = %nrquote(SCD1);

27 %let etls_userID = %nrquote(sasdemo@SASEDU);

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;

_DISARM| | _ENDDISARM

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;

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;

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;

| _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

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;

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;

86 /* Create metadata macro variables */

87 %let IOMServer = %nrquote(SASApp);

88 %let metaPort = %nrquote(8561);

89 %let metaServer = %nrquote(SASEDU);

91 /* Set metadata options */

92 options metaport = &metaPort

93 metaserver = "&metaServer";

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

| _DISARM| 7901184| _DISARM| 7901184| _DISARM| 10| _DISARM| 10| _DISARM| 141316| _DISARM| 1102504| _DISARM| 0.031200|

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.

| _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).

| _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): ;

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;

| _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;

| _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;

| _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.

| _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;

| _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;

| _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.

| _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.

| _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): 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.

| _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

| _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_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.

| _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): proc datasets lib = work nolist nowarn memtype = (data view);

MPRINT(ETLS_SCD_TYPE_1): delete etls_xref;

MPRINT(ETLS_SCD_TYPE_1): quit;

| _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

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): ;

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;

| _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

topic Re: SCD Type-1 Transfomation in SAS Data Management

SCD Type-1 Transfomation

Re: SCD Type-1 Transfomation

Re: SCD Type-1 Transfomation

Re: SCD Type-1 Transfomation

Re: SCD Type-1 Transfomation