topic Re: SAS DI Check if dataset is empty and abort if not in SAS Data Management

SAS DI Check if dataset is empty and abort if not

titan31 — Wed, 13 Sep 2017 06:49:38 GMT

Hi,

I'm quite new to SAS and really can't get my head around it's code, so asking here for help. I'm using DI studio.

I've a job that is reading an external csv file using file reader, and have a macro created by a colleague that validates the data in this external file and prints out error message to a work table.

What I'd like to do is either on precode of the file reader, or by using another user written code transformation is to read the work table and check if observations exist, and if they do, abort the job. From googling, and between here and stackoverflow, I can find how to read a dataset and count observations but I'm having real difficulty in figuring out how to implement it so any guidance would be really appreciated, or if there are better ways using SAS Di transformations like Conditional Start etc, I'm all ears for those too.

Can anyone please help me on this?

Thanks

Re: SAS DI Check if dataset is empty and abort if not

LinusH — Wed, 13 Sep 2017 10:41:12 GMT

Yes there are.

There is a Data Validation transformation that covers some use cases.

If you have validation like if the value exists in a look up table, the Look up transformation can be used.

I think (not sitting on a DI Studio client right now) that these transformations can trigger both return code and abortion.

Abortion can also be handled by using Status Handling events.

Re: SAS DI Check if dataset is empty and abort if not

titan31 — Thu, 14 Sep 2017 07:44:55 GMT

Hi,

We've the validation already, and have a lookup wrote written the macro. The File Reader transformation doesn't have a Status Handling tab which is what I was hoping so that doesn't work, unless there's another way I can put that in.

What I'd like to do is have my User Written Code transformation with the Validation macro run first, have the error messages (if found) output to a temporary work table and either;

by adding another transformation to abort if the dataset has records, or continue if not

or adding some code into pre-code of the file reader to check that dataset, and abort if it has records, or continue if not

or any other method I can do this

From what I can see, the Return Code transformation doesn't give me a chance to continue if failed, and not sure how I can status handling before or on the file reader step.

Re: SAS DI Check if dataset is empty and abort if not

Patrick — Thu, 14 Sep 2017 12:15:48 GMT

@titan31

What you describe sounds to me already like a bit too much user written code (like this macro you mention). But not knowing the details below some SAS code which you could run after the error table gets created, either as post code or as a separate user written node.

If there is a macro for capturing errors but you then only keep those error in a work table and you are not really interested to look at these error records then why don't you just abort the job directly in this macro as soon as it finds the first error.

But o.k., here a code sample that aborts the job if table test got any rows.

data _null_;
  if nobs>0 then
    do;
      put 'Table Test has ' nobs 'rows';
      put 'Aborting job';
      abort abend;
    end;
  stop;
  set test nobs=nobs;
run;

Re: SAS DI Check if dataset is empty and abort if not

titan31 — Thu, 14 Sep 2017 15:53:33 GMT

Hi, Thanks for that. Will try that now. We need to get all the errors as we'll send it back to business with all the errors and the line numbers of the file if there are any issues. But, we don't want to continue the job as if there are issues, we don't want to upload bad data into VA

Re: SAS DI Check if dataset is empty and abort if not

Patrick — Tue, 19 Sep 2017 12:11:56 GMT

@titan31

With DIS you normally would use a Validation node which allows you to implement validation rules which populates Error and Exception tables. You wouldn't use a custom writte SAS macro for such a task if you can do it differently.