topic Re: Transformation Error Code in SAS Data Management

Transformation Error Code

kandasamy — Tue, 31 Jul 2012 18:16:13 GMT

Hi,

As per our requirement we need to capture the each transformation error and we need to log it to the Error Table.

Please guide me how to get the Transformation error Code from the SAS DI tool.

I am using SAS DI 4.21 version tool.

Thanks!

Regards

Kandasamy

Re: Transformation Error Code

art297 — Tue, 31 Jul 2012 18:25:52 GMT

The following paper might contain what you need:

http://analytics.ncsu.edu/sesug/2007/AD10.pdf

Re: Transformation Error Code

shivas — Wed, 01 Aug 2012 09:13:59 GMT

Hi Kandasamy,

You can try using following ...

1)Status handling

2)As status handling tab is not present in some of the transformation,you can use Return code check transformation for capturing error code.

3)You can also write user written code.

Hope this helps...

Thanks,

Shiva

Re: Transformation Error Code

Patrick — Wed, 01 Aug 2012 12:11:02 GMT

Make sure that you understand the requirement correctly! SAS DI studio let's you create a job flow as SAS Metadata. Based on this metadata SAS code gets generated. What will be executed is SAS code.

If SAS code throws an error (eg. there is a syntax error in a expression defined in a transformation) then it's kind-of too late to try and capture this error and write it to an error table (as this capturing code would come after the error occured). A syntax error is a syntax error - how code behaves after that is unpredictable.

What you can do is define conditions (eg: lookup table does not exist) and actions (eg. logging the condition in an error table).

Thanks for the question! I didn't realize what the Return Code Check transformation is used for. Was good to have a read in the docu.

http://support.sas.com/documentation/cdl/en/etlug/60948/HTML/default/viewer.htm#p0ygtz7di8z8kpn1ufncd1ajt1pj.htm

http://support.sas.com/documentation/cdl/en/etlug/60948/HTML/default/viewer.htm#n13t3zm3lp6u7dn15a8k13mak3gh.htm

The paper Art posted: That could be a way to capture rejected rows when loading into a SAS dataset with integrity constraints turned on (using audit trails: http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a001224397.htm) I personally feel that when this level of control is needed then it's possibly time to think about loading data into a data base like Teradata or Oracle.