Re: SCD Type-1 Transfomation

lll · Posted 07-14-2014 08:56 AM

Hi Folks,

Can anyone of you advise me on the attached error which i got while implementing SCD Type-1 in SAS DI? Appreciate if you could explain the SCD Type-1 with simple example as support sas document was not fully helpful achieve my target.

Thanks.

MikeFrost · Posted 07-15-2014 10:46 AM

Hi,

Opening a support track with SAS technical support is the best route to get guidance on how to implement SCD-1 and to get feedback on the error you are experiencing. Please start there.

Regards,

Mike F.

LinusH · Posted 07-16-2014 05:18 AM

Attach the full log.

Can it be so that you have changed the meta data after the job executed for the first time, so that physical table structure of the target table and meta data differs?

Data never sleeps

lll · Posted 07-22-2014 10:31 AM

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

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;

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

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

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

Patrick · Posted 07-22-2014 11:12 PM

Check your physical table "sk_tar.SCD_1" if the columns you're using are actually in this table. What can happen: You change table metadata for a table which already exists as permanent physical table. Depending on the transformation/load process the physical table doesn't get updated/recreated and is out of sync.

The SAS code in DI gets generated based on metadata so there it can happen that for example a keep statement gets generated for columns which don't exist in the physical table.

Easiest way to get around this: Delete the permanent physical table and then re-run your DI job.

SCD Type-1 Transfomation