Date: Tue, 26 Jan 2010 01:45:00 -0800
Reply-To: Frank Poppe <Frank.Poppe@PWCONSULTING.NL>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Frank Poppe <Frank.Poppe@PWCONSULTING.NL>
Subject: Re: Error or Abort on invalid data
Content-Type: text/plain; charset=windows-1252
For those that are interested.
Iíve found a hack to smuggle data step statements into the code after
the input statement.
What Iíve done is add a fake last dummy column in the External File
before the File Reader.
Because I need more characters there than the field for the column
name provides I use a reference to a macro, but the use of a macro is
not essential to the idea. Iíve named the macro variable _ds_stmnt, so
in the column name field Iíve typed <&_ds_stmnt> (without the
brackets, no semi-colon). It doesnít matter what attributes are
specified for this variable, and leave the format fields empty (i.e.
The column name is used in two places in the generated code. An ATTRIB
statement for the column is defined, and the column is named as the
last field on the INPUT statement. Generated code has the following
data <target> ;
infile <sourcefile and options> ;
attrib <var1> length = x ;
attrib <varn> length = x ;
attrib &_ds_stmnt length = $8 ;
input var1 Ö &_ds_stmnt ;
Now the macro variable has to get its value, and after evaluation it
should result in valid code in both places. What I really want is that
after the INPUT statement the statement <if _error_ then abort ;> is
inserted, so that has to be part of it.
I have added following code in the pre-process field for the job:
%let _ds_stmnt = %str ( ; if _error_ then abort ;*) ;
The ATTRIB statement for the last (dummy) variable will now look like
attrib ; if _error_ then abort ; * length = 8 ;
The ATTRIB statement thus is empty, which SAS does not mind. The abort
statement is inserted although I did not want it there, but it doesnít
hurt. The asterisk will cause the length specification to seen as a
The INPUT statement will look like this.
input var1 Ö varn ; if _error_ then abort ; * ;
The last semi-colon is inserted by DI Studio because it has to end the
input statement after the list of variables, not knowing that we have
inserted other code, and now ends the comment started by the asterisk.
Itís a hack, but it works. And I can still use the standard External
File object to apply any changes in the specified columns.
What I really would like in a next version of DI Studio is to have is
a tab on the File Reader where I can add code to be inserted between
the INPUT statement and the RUN;
On 21 jan, 17:15, Frank Poppe <Frank.Po...@PWconsulting.nl> wrote:
> Thanks for the reaction.
> There is something to say for your approach, but this way you lose the
> interface within the standard file reader (in combination with
> external file object) to set up the INPUT statement, listing the
> fields to be read. Or do see that wrong?
> Frank Poppe
> On 19 jan, 19:59, gerhard.hellrie...@T-ONLINE.DE (Gerhard Hellriegel)
> > I don't know how to add that option to the standard file-reader. However I
> > don't find file-reader and file-writer very useful. What I'd do is not to
> > use user-written code, but to create 2 user-transformations. One for input
> > with that special behaviour to set a returncode or abort, one for output
> > with the usage of proc export which let you have variable names as column
> > headers in CSV files for example.
> > You can also add some options to trigger special behaviours.
> > Gerhard
> > On Tue, 19 Jan 2010 08:02:32 -0800, Frank Poppe
> > <Frank.Po...@PWCONSULTING.NL> wrote:
> > >Hi,
> > >I have a job in DI Studio that reads data from an external (CSV)
> > >file.
> > >This process is scheduled together with some other jobs.
> > >Occasionally (or even more often) there is invalid data in one of the
> > >fields. This causes NOTE's in the LOG, but the process continues.
> > >I would like to have an error condition set, so that the process
> > >aborts, or at least so that the scheduler will not start subsequent
> > >processes.
> > >I know that the _ERROR_ flag is set within the data step so that code
> > >could be added there to abort. But there is no way that I know of to
> > >accomplish that from within DI Studio, without changing to 'user
> > >written code'. And that makes the code difficult to maintain in the
> > >future.
> > >Any ideas to force an abort, or at least a return code for the job
> > >that prevents the scheduler from continuing?
> > >Thanks
> > >Frank Poppe
> > >PW Consulting
> > >the Netherlands