Date:         Sun, 4 Apr 2010 14:04:36 -0500
Reply-To:     Craig Johnson <cjohns38@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Craig Johnson <cjohns38@GMAIL.COM>
Subject:      Re: Two Proc SQL Questions (Easy for an expert)
Comments: To: Jonathan Goldberg <jgoldberg@biomedsys.com>
In-Reply-To:  <v2mbfed04db1004041102l128e2548s387bd892215ea1cc@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

Below is the code I ended up creating with Jonathan's help and some
searching on the net.  It looks like it is going to work but I still need to
do some more testing.  The code reads from an error log that is generated in
SAS and exports the results to Access including the date.  The information
is only inserted if the ID and error code do not already exist.

NOTES:

1) The error trapping is anemic at best
2) This code does generate an error (WARNING: This DELETE/INSERT statement
recursively references the target table. A consequence of
    this is a possible data integrity problem.) but I don't think this will
be an issue given it's recursive due to correlated subquery.....please
correct me if I'm
    wrong.

Thanks for the help again.

****************
Code
****************

PROC SQL;
  INSERT INTO ACCESSDB.FORMERRORSANDFIXES
  select ERRORID, ID, FILENAME, FIELDID, FIELD, ERROR,
    CASE
        WHEN ERRORID NE '' THEN ''
        ELSE 'ERROR!'
    END AS Solution,
    CASE
        WHEN ERRORID NE '' THEN ''
        ELSE 'ERROR!'
    END AS Notes,
    CASE
        WHEN ID NE . then Datetime()
        Else '01Jan1900:00:00:00'DT
    END AS DateFound FORMAT=DateTime12.
   from &sysday._errors
   where (SELECT count(*)
          FROM ACCESSDB.FORMERRORSANDFIXES AS F FULL JOIN &sysday._errors AS
E
          ON E.ID = F.ID AND E.ErrorID = F.ErrorID
          WHERE E.ID = F.ID AND E.ErrorID = F.ErrorID)= 0;
 QUIT;