SCD Type-1 Transfomation

[lll]

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]

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]

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?

[lll]

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       

[Patrick]

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.