topic Re: Data Validation - DI - Count Records in SAS Data Management

Data Validation - DI - Count Records

DavidCaliman — Tue, 10 Dec 2013 18:50:56 GMT

I can use the COUNT function in Data Validation within DI Studio?

What´s the syntax?

I need to abort the job if the table has more than one record.

Re: Data Validation - DI - Count Records

Patrick — Tue, 10 Dec 2013 20:24:18 GMT

From the top of my head:

The Data Validation Node creates SAS Data Step code and the rules are applied on row level. If so then you could use the automatic variable _N_ in an expression where you abort the job if: _N_>1

Re: Data Validation - DI - Count Records

DaveR_SAS — Tue, 10 Dec 2013 20:41:29 GMT

To follow up on Patrick's suggestion, perhaps this example for a Data Validation transformation will give you an idea of how this transformation can be used:

SAS(R) Data Integration Studio 4.7: User's Guide

Re: Data Validation - DI - Count Records

Quentin — Tue, 10 Dec 2013 20:52:06 GMT

Or just add the check as a user-written code module?

Is this seen as "bad form" in DI Studio land? I haven't done much with DI studio besides user-written code, so I'm curious if people often mix DI transformation nodes with user-written nodes.

Re: Data Validation - DI - Count Records

Patrick — Tue, 10 Dec 2013 22:43:01 GMT

Best practice is to use user written code only if you can't do it with an existing transformation.

Re: Data Validation - DI - Count Records

Quentin — Wed, 11 Dec 2013 03:48:39 GMT

THanks Patrick. Rather than hijack this thread, I'll start another one relating to pros and cons of mixing the canned transformations with user written code.

Re: Data Validation - DI - Count Records

DavidCaliman — Wed, 11 Dec 2013 12:24:52 GMT

Patrick,

I need abort the job too when the table is empty.

When the table has more than one record is working!

So, the table should have only one record for the job does not fail. (_N_ <> 1 did not work!)

Tks.

Re: Data Validation - DI - Count Records

LinusH — Wed, 11 Dec 2013 13:49:14 GMT

For an empty table, you could use Status Handling instead - where you can specify an action based on if the target table was updated (or not).

Re: Data Validation - DI - Count Records

DavidCaliman — Wed, 11 Dec 2013 13:59:15 GMT

Thanks for the reply, but I want in the data validation transformation.

Re: Data Validation - DI - Count Records

LinusH — Wed, 11 Dec 2013 15:32:28 GMT

Why?

Re: Data Validation - DI - Count Records

Patrick — Wed, 11 Dec 2013 16:31:03 GMT

So you need to abort the job when the table exists but has 0 records (and not "more than 1 record"). That's something else.

Can't test it but doesn't the Data Validation node include a Status Handling tab with one of the default conditions being "Table truncated". You could use this one and if truncated then select action "abort all processes".

SAS(R) Data Integration Studio 4.21: User's Guide

P.S: You should always post the DIS version so we can know what's available to you "out-of-the-box".