Date:         Tue, 7 Jun 2005 17:12:29 -0400
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>
Subject:      Re: sql

1. A feature like (pseudocode)

   select t2.[* except b]

would indeed be handy at times.

2. In this case, you can actually get away with

   create table new as
   select *
   from t1, t2
   where t1.b=t2.b;

although you get an ugly warning message. What's happening is that two
columns named "B" are generated, which is fine with SQL but not with SAS
data set structure. So one gets axed, but since they are the same, it
doesn't matter.

3. In your example situation, you can get what you want, without the
warning message, from

   create table new as
   select *
   from t1 natural join t2;

4. Notwithstanding all of the above, pay attention to the advice offered by
Sig and Ian. Not all situations are as "textbook" as your example.

On Tue, 7 Jun 2005 14:15:40 -0400, Jianping Zhu <zhujp98@GMAIL.COM> wrote:

>data t1;
>input a$ b;
>cards;
>a 3
>5 6
>c 7
>;
>run;
>data t2;
>input c$ b;
>cards;
>a 3
>5 9
>c 7
>;
>proc sql;
>create table new as
>select t1.*, t2.c
>from t1, t2
>where t1.b=t2.b;
>run;
>
>above code with no problem.
>suppose I have  tens of varible in t1 and t2, it is silly to do
>select t1.*, t2.v1, t2.v2......t2,Xn
>in the sql.
>Is there an efficient way to do that?
>Thanks