Date:         Mon, 19 Nov 2007 15:19:38 -0800
Reply-To:     "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Howard Schreier <hs AT dc-sug DOT org>" <nospam@HOWLES.COM>
Organization: http://groups.google.com
Subject:      Re: ERROR: Subquery evaluated to more than one row
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset=ISO-8859-1

On Nov 15, 5:03 pm, RYan <donnelly.rya...@gmail.com> wrote:
> Hello SAS-l'ers,
>
> I've run into some problems with proc sql.  I'm trying to update a
> variable from one table based on the values found in another table.
> Below are two variations of code I've tried to make this work.
>
> PROC SQL;
>   SET HOSPID = (SELECT DISTINCT PROV1680 FROM CREF.&POS_CRSREF WHERE
> HOSPID=PROV0300)
>   WHERE HOSPID IN (SELECT PROV0300 FROM CREF.&POS_CRSREF);
> QUIT;
>
> and
>
> PROC SQL;
>   UPDATE MEDPAR.MEDPAR&YR.3M AS M
>   SET HOSPID=(SELECT PROV1680 FROM CREF.&POS_CRSREF AS U
>   WHERE M.HOSPID=U.PROV0300)
>   WHERE M.HOSPID IN (SELECT PROV0300 FROM CREF.&POS_CRSREF);
> QUIT;
>
> Here's the catch...I have tried this on a small sample of the dataset
> and it works fine, no errors and everything comes out okay.  However,
> when I try to run the full dataset I get an error message in the log
> "ERROR: Subquery evaluated to more than one row".  What's going on,
> what am I doing wrong?  Shouldn't those statements work regardless of
> the size of the dataset (the dataset is about 13 million rows)?
>
> Thanks in advance for the help.
>
> RYan

Indeed, size of the table does not matter. However, the subquery must
return a single, unambiguous result for each value to be updated.
Consider this example:

   data m;
   input key value;
   cards;
   11 12.13
   ;

   data u;
   input key value;
   cards;
   11 14.15
   11 16.17
   ;

   proc sql;
   update m
    set value = (select value
                  from u
                  where m.key=u.key);
   quit;

Should SQL use 14.15 or 16.17 to replace 12.13?