Date:         Mon, 19 May 2008 11:38:35 -0400
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Nested Data Accesses?
Comments: To: Laptop765@gmail.com
In-Reply-To:  <58c75d2f-60de-4325-b237-aed24a7b2348@z72g2000hsb.googlegroups.com>
Content-Type: text/plain; charset="us-ascii"

The extra semi-colon in
UPDATE unmatched AS needid
    SET idfield2=(SELECT idfield2
              FROM oldids AS oldid
              WHERE oldid.matchfield EQ needid.matchfield
              GROUP BY idfield2
              HAVING MAX(idfield3)+1 < 99
              ORDER BY idfield2); <--- delete
              ....

   obviously doesn't conform to SAS SQL syntax.

You'll also need to use an existential operator to limit updates to
those tuples that match on matchfield. For explanations of that more
subtle issue in SQL updates, see the SAS-L archives with existential as
a keyword, or take a look at the SGF 2008 presentation by Hermansen and
Legum.
S

-----Original Message-----
From: owner-sas-l@listserv.uga.edu [mailto:owner-sas-l@listserv.uga.edu]
On Behalf Of Laptop765@gmail.com
Sent: Monday, May 19, 2008 11:11 AM
To: sas-l@uga.edu
Subject: Re: Nested Data Accesses?


Ok, I still seem to be stuck if anyone could help out...

I did the matching to IDs that can be carried over already in a previous
data step and sorted into 3 categories: oldids (the existing id file)
newinfo (new records matched to old records) unmatched (new records
without matches)

The union of newinfo and unmatched yield all of the new records that we
need to process.  The next step is to update unmatched for which I
essentially need to do the following (but this doesn't seem to work):

PROC SQL NOPRINT;
  UPDATE unmatched AS needid
    SET idfield2=(SELECT idfield2
              FROM oldids AS oldid
              WHERE oldid.matchfield EQ needid.matchfield
              GROUP BY idfield2
              HAVING MAX(idfield3)+1 < 99
              ORDER BY idfield2);
    SET idfield3=(SELECT MAX(idfield3)+1 AS HIGHEST
              FROM oldids AS oldid
              WHERE oldid.matchfield EQ needid.matchfield
              GROUP BY idfield2
              HAVING HIGHEST < 99
              ORDER BY idfield2);
QUIT;

Of course, this doesn't work for some reason (I seem to get syntax
errors that I cannot trace) and I'm not quite sure how to limit the
subqueries (can I set outobs somewhere only for the subqueries?) to just
the first record returned.  Also, I still need to handle cases where no
rows are returned (need to find the next available idfield2 value and
use that).  Also, the updated records in unmatched need to be accounted
for, so I essentially need to do a "FROM oldids, unmatched" and I don't
know if this is possible or not.

Thanks in advance for any help.