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.
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.
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?
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
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.