Date:         Thu, 17 Nov 2005 10:58:11 -0500
Reply-To:     Charles Harbour <charles.harbour@PEARSON.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Charles Harbour <charles.harbour@PEARSON.COM>
Subject:      Re: error checking

Here's a little scrap of code that has served me well over the years.  It
was created to run against DB2, but in theory, it could be adapted to run
against any rdbms (possibly even access and excel) that supports sysrc
functionality.

The insert and update statements can be swapped, depending on whether the
majority of your transactional data is changing existing rows, or is new
(insert).  And in this code, MSTRLIB has already been defined in a libname
statement with the DB2 engine.

Note that this code will run many times slower (in the neighborhood of 10
to 15 <times> longer than a simple proc append) because you are doing
individual row checking.  If you are running millions of rows, it is worth
your time to do the analysis beforehand to identify which transactional
rows will be inserted into your permanent data and which ones will be
updating existing rows--split the transactional data, and handle
accordingly.

 DATA MSTRLIB.Tablename;
   MODIFY MSTRLIB.Tablename Transactionaldataset;
   BY By_vars_go_here;
   SELECT (_IORC_);
      WHEN (%SYSRC(_DSENMR)) DO;         /* NO MATCH - INSERT */
      /* PROGRAMMING NOTE--_DSENMR IS USED WITH A BY STATEMENT,
         _DSENOM IS USED WITH KEY= OR POINT= STATEMENT */
        OUTPUT MSTRLIB.Tablename;
        _ERROR_=0;
      END;
      WHEN (%SYSRC(_SOK)) DO;           /* FOR RERUNNING (UPDATE) */
        REPLACE MSTRLIB.Tablename;
      END;                              /* TRAP UNEXPECTED OUTCOMES */
      OTHERWISE DO;
        PUT 'UNEXPECTED ERROR CONDITION: _IORC_ = ' _IORC_ ;
        PUT _ALL_;
        _ERROR_=0;
      END;
   END;
 RUN;


CH


On Wed, 16 Nov 2005 17:45:58 -0500, Howard Schreier <hs AT dc-sug DOT org>
<nospam@HOWLES.COM> wrote:

>First, note that it's not a complete statement. Rather it's a boolean
>expression, which evaluates as either true (1) or false(0).
>
>_IORC_ (think "input/output return code") is an automatic variable which
>reflects the result of a most-recent read/write operation. It is numeric,
>and can take on many values, corresponding to many possible conditions.
>
>Hard-coding of possibilities makes things hard for humans to decipher. The
%
>SYSRC macro was designed just to make code more mnemonic. In this case ("%
>sysrc(_sok)"), it returns a zero, which indicates success (think "It's
OK").
>
>">=" is a non-equality comparison operator.
>
>So the code in question is equivalent to
>
>   _IORC_  ne  0
>
>and would typiclly be used in a contect like
>
>   if _IORC_ ^= %sysrc(_sok) then [statement];
>
>On Tue, 15 Nov 2005 08:46:34 Z, Dirk Nachbar <Dirk.Nachbar@DWP.GSI.GOV.UK>
>wrote:
>
>>dear all
>>
>>can someone please explain what the following line means
>>
>>_IORC_ ^= %sysrc(_sok)
>>
>>
>>
>>Dirk Nachbar
>>Assistant Economist
>>Pensim2
>>Department for Work and Pensions
>>Level 4, The Adelphi
>>1-11 John Adam St
>>WC2N 6HT London
>>020 796 28531
>>**********************************************************************
>>This document is strictly confidential and is intended only for use by the
>addressee.
>>If you are not the intended recipient, any disclosure, copying,
>distribution or other
>>action taken in reliance of the information contained in this e-mail is
>strictly prohibited.
>>Any views expressed by the sender of this message are not necessarily
>those of the Department
>>for Work and Pensions.
>>If you have received this transmission in error, please use the reply
>function to tell us
>>and then permanently delete what you have received.
>>Please note: Incoming and outgoing e-mail messages are routinely monitored
>for compliance
>>with our policy on the use of electronic communications.
>>**********************************************************************
>>
>>
>>
>>The original of this email was scanned for viruses by the Government
>Secure Intranet (GSi) virus scanning service supplied exclusively by Cable
>& Wireless in partnership with MessageLabs.
>>
>>On leaving the GSi this email was certified virus-free