SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Data Validation - DI - Count Records

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Data Validation - DI - Count Records

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.


Accepted Solutions
Solution
‎12-10-2013 03:24 PM
Respected Advisor
Posts: 4,173

Re: Data Validation - DI - Count Records

Posted in reply to DavidCaliman

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

View solution in original post


All Replies
Solution
‎12-10-2013 03:24 PM
Respected Advisor
Posts: 4,173

Re: Data Validation - DI - Count Records

Posted in reply to DavidCaliman

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

SAS Employee
Posts: 75

Re: Data Validation - DI - Count Records

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

Contributor
Posts: 58

Re: Data Validation - DI - Count Records

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.

Super User
Posts: 5,441

Re: Data Validation - DI - Count Records

Posted in reply to DavidCaliman

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).

Data never sleeps
Contributor
Posts: 58

Re: Data Validation - DI - Count Records

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

Super User
Posts: 5,441

Re: Data Validation - DI - Count Records

Posted in reply to DavidCaliman

Why?

Data never sleeps
Respected Advisor
Posts: 4,173

Re: Data Validation - DI - Count Records

Posted in reply to DavidCaliman

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".

PROC Star
Posts: 1,325

Re: Data Validation - DI - Count Records

Posted in reply to DavidCaliman

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.

Respected Advisor
Posts: 4,173

Re: Data Validation - DI - Count Records

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

PROC Star
Posts: 1,325

Re: Data Validation - DI - Count Records

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1775 views
  • 3 likes
  • 5 in conversation