topic SAS DI : LookUp Transformation Log in SAS Data Management

SAS DI : LookUp Transformation Log

JJP1 — Wed, 28 Aug 2019 10:59:10 GMT

Hi Team,

Would you please help on i ran the lookup transformation in SAS DI.the job log is showing below note.

would you please suggest on below note in log .... what does it mean please

Query over :
NOTE: There were 39432 observations read from the data set test
Source records with errors: 27360
Total lookup exceptions: 27360

Why are these suggesting source record errors?please help

Re: SAS DI : LookUp Transformation Log

Patrick — Wed, 28 Aug 2019 11:27:51 GMT

@JJP1

Look into the generated SAS code and things will become clear.

These are the number of rows in your source table where there hasn't been a matching record in the lookup table.

 set <source table> end = eof;
.....
/* Examine success of lookups  */
   if ( rc0=0 ) then
   do;
      /* Write row to target  */
      output ...;
      return;
   end;
   else
   do;
      error_total + 1;
      ....
   end; /* One or more lookups failed */

   if (eof = 1) then
   do;
      put "Source records with errors: " error_total ;
      put "Total lookup exceptions: " exception_total ;
   end;

If you also define an error and exception table then that's where such things get captured.

Re: SAS DI : LookUp Transformation Log

Kurt_Bremser — Wed, 28 Aug 2019 11:27:18 GMT

I take it that these are the observations for which no lookup value was found.

Re: SAS DI : LookUp Transformation Log

JJP1 — Wed, 28 Aug 2019 11:31:12 GMT

Thanks @Patrick & @Kurt_Bremser for guiding me