topic Re: How to capture rejected records? in SAS Programming

How to capture rejected records?

Patrick — Wed, 14 Jul 2010 13:30:18 GMT

Hi all

Loading data into a SAS table with integrity constraints (SAS 9.2, Windows 64bit):
Is there a way how I could capture rejected records and write them to an error table?

In the example below: How would I capture the rejected record and write it to an error table?

data have;
do id=1 to 3;
var='A';
if id=2 then var='';
output;
end;
run;

data want;
stop;
set have;
run;

proc datasets lib=work nolist;
modify want;
ic create not null(var);
run;
quit;

proc append base=want data=have;
run;

Real world:
The amount of data I'm dealing with is considerable so I try to avoid merging source and target data after loading in order to get the rejected delta.

Any suggestions appreciated.

Thanks
Patrick

Re: How to capture rejected records?

Paige — Wed, 14 Jul 2010 13:42:41 GMT

I have no idea what your data step creating data set HAVE is doing. it looks like you are using a loop over three ids to create a one record where id=2 and var='' and the other records have var='A'. Totally meaningless example to me, and I can't understand what the rejected records are that you are discussing.

Clarifications would be helpful.

Re: How to capture rejected records?

data_null__ — Wed, 14 Jul 2010 13:57:37 GMT

You will need AUDIT. There are a number of options that you will need to decide about but this should get you started. See PROC DATASETS documentation.

[pre]
proc datasets lib=work nolist;
modify want;
ic create not null(var);
run;
audit want;
initiate;
run;
quit;

proc append base=want data=have;
run;

proc print data=want(type=audit);
run;
[/pre]

Re: How to capture rejected records?

Patrick — Wed, 14 Jul 2010 13:59:12 GMT

Paige

The question was meant for people who might know...

But for you some more explanation.

/* create example source table with 3 records */
data have;
do id=1 to 3;
var='A';
if id=2 then var='';
output;
end;
run;

/* create target table: mapping step */
data want;
stop;
set have;
run;

/* add NOT NULL integrity constraint to target table */
proc datasets lib=work nolist;
modify want;
ic create not null(var);
run;
quit;

/* load source into target */
proc append base=want data=have;
run;

/* print resulting target table */
proc print data=want;
run;

Only the first and third record got loaded into the target table.

The second record has been rejected due to the NOT NULL constraint on variable "var".

The following can be seen in the log:
WARNING: Add/Update failed for data set WORK.WANT because data value(s) do not comply with integrity
constraint _NM0001_. (Occurred 1 times.)

Thanks
Patrick

Re: How to capture rejected records?

Patrick — Wed, 14 Jul 2010 14:14:07 GMT

Data _null_

Once more thanks a lot. ...and you definitly wrote this code on a Unix machine (no CR).

An audit trail on a SCD2 table with millions of records - scary!

Follow up question:
Has anybody experience with what that will do to performance?

Thanks
Patrick

Re: How to capture rejected records?

data_null__ — Wed, 14 Jul 2010 14:46:03 GMT

> Once more thanks a lot. ...and you definitly wrote
> this code on a Unix machine (no CR).

No windows the pre and /pre directives cause the loss of CR. I paste to WORD then copy to my editor, when I copy code from this forum.

> An audit trail on a SCD2 table with millions of
> records - scary!

Don't know what that is? You can limit the amount of output written to the AUDIT file.

[pre]log data_image=NO before_image=NO;[/pre]

You might try a different approcah using the MODIFY statement. Just send all rejected records to a BAD file. I don't know if it is possible to know which IC the record was rejected for as with AUDIT. Still might be adequate for your needs.

[pre]
data want bad;
if 0 then modify want;
set have;
output want;
if _iorc_ ne 0 then output bad;
run;
[/pre]

Re: How to capture rejected records?

Paige — Wed, 14 Jul 2010 15:19:55 GMT

data have reject;
do id=1 to 3;
if id=2 then output have;
else output reject;
end;
run;

Re: How to capture rejected records?

Patrick — Wed, 14 Jul 2010 15:24:38 GMT

Hi data _null_

SCD2: That's mainly nothing else than a loading technique where you keep all the history records in the same table - which means that these tables tend to grow.
http://en.wikipedia.org/wiki/Slowly_changing_dimension

Less of an issue if someone uses a data base like Oracle where a table can be partitioned; more of an issue with SAS files where a table is just one big file.

You really pushed me into the right direction. I think I found everything I need in the docu:
http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#/documentation/cdl/en/lrcon/62955/HTML/default/a001224397.htm

As I have to use SAS DI Studio in the project I am the only option will be to use an audit trail.

That's the approach I have in mind:
proc datasets lib=work nolist;
audit want;
initiate ;
LOG
BEFORE_IMAGE=NO
DATA_IMAGE=NO
ERROR_IMAGE=YES;
run;
quit;

proc append base=want data=have;
run;

data error_yymmdd;
set want(type=audit keep=id _:);
run;

proc datasets lib=work nolist;
audit want;
TERMINATE ;
run;
quit;

For the ones using DI Studio 4.2:
I'm thinking about implementing the part before the PROC APPEND as pre-code in the SCD2 loader, and the part after the PROC APPEND as post-code.

Thanks
Patrick