Date:         Fri, 14 Mar 2003 11:42:38 -0500
Reply-To:     Howard_Schreier@ITA.DOC.GOV
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard_Schreier@ITA.DOC.GOV
Subject:      Re: what is wrong with this??

If you just run the code

   select * from a ,c where a.a=c.a;

you will see in the result two columns with the same name. Like wise for
the second SELECT. That is the ambiguity.

Try

   create table test as
      select a.* from a ,c
       where a.a=c.a
     outer union corr
      select b.* from b ,c
       where b.a=c.a;

On Fri, 14 Mar 2003 06:44:25 -0800, peter <fjo2222222@HOTMAIL.COM> wrote:

>I have written many, many programs in sas version 6.12 containing
>sql-code like :
>proc sql;
>   create table test as
>     select * from a ,c
>      where a.a=c.a
>    outer union corr
>     select * from b ,c
>      where b.a=c.a;
>and there was never a problem concatenating two queries this way, but
>... in verion 8.2 I get the errors below.
>
>Please somebody tell me : What is wrong?
>
>
>5    data a;
>6     a=0; b=2; output;
>7     a=1; b=3; output;
>8     a=2; b=2; output;
>9     a=3; b=2; output;
>
>NOTE: The data set WORK.A has 4 observations and 2 variables.
>NOTE: DATA statement used:
>      real time           0.00 seconds
>      cpu time            0.00 seconds
>
>
>10   data b;
>11    a=0; c=2; output;
>12    a=1; c=5; output;
>13    a=2; c=67; output;
>14    a=5; c=28; output;
>
>NOTE: The data set WORK.B has 4 observations and 2 variables.
>NOTE: DATA statement used:
>      real time           0.02 seconds
>      cpu time            0.01 seconds
>
>
>15   data c;
>16    a=0;  output;
>17    a=2;  output;
>18    a=3;  output;
>19    a=5;  output;
>20    a=6;  output;
>21   run;
>
>NOTE: The data set WORK.C has 5 observations and 1 variables.
>NOTE: DATA statement used:
>      real time           0.01 seconds
>      cpu time            0.01 seconds
>
>
>22   proc sql;
>23    create table test as
>24      select * from a ,c
>25       where a.a=c.a
>26     outer union corr
>27      select * from b ,c
>28       where b.a=c.a;
>ERROR: Duplicate column names have been detected in the above query
>which requested that
>       CORRESPONDING column names be matched. This situation is
>ambiguous.
>29   quit;
>NOTE: The SAS System stopped processing this step because of errors.
>NOTE: PROCEDURE SQL used:
>      real time           0.03 seconds
>      cpu time            0.00 seconds