BookmarkSubscribeRSS Feed
Patrick
Opal | Level 21
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
7 REPLIES 7
Paige
Quartz | Level 8
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.
Patrick
Opal | Level 21
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
Paige
Quartz | Level 8
data have reject;
do id=1 to 3;
if id=2 then output have;
else output reject;
end;
run;
data_null__
Jade | Level 19
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]
Patrick
Opal | Level 21
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
data_null__
Jade | Level 19
> 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]
Patrick
Opal | Level 21
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/e...



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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1883 views
  • 0 likes
  • 3 in conversation