Date:         Tue, 26 Aug 2003 08:24:40 -0700
Reply-To:     "Huang, Ya" <yhuang@AMYLIN.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Huang, Ya" <yhuang@AMYLIN.COM>
Subject:      Re: sql all corresponding
Comments: To: Arto Raiskio <arto.raiskio@SUOMENPOSTI.COM>
Content-Type: text/plain; charset="iso-8859-1"

What you need is a 'JOIN' not a 'UNION':

data a;
input brick $8. paint $10-20 @22 t time.;
cards;
red      a1234567890 08:11:12
black    a1234567891 08:11:13
;
data b;
input paint $1-11 @13 type $4.;
cards;
a1234567890 fast
a1234567893 none
a1234567891 slow
a1234567891 fast
;
proc sql;
select a.brick,coalesce(a.paint,b.paint) as paint,a.t,b.type
from a full join b
on a.paint=b.paint
;
--------------------------
brick     paint               t  type
-------------------------------------
red       a1234567890     29472  fast
black     a1234567891     29473  slow
black     a1234567891     29473  fast
          a1234567893         .  none

BTW, in your 'expected' result, you have only
one record for a1234567891, and type is 'slow',
if that's indeed what you want, you need to tell us
why the 'slow' is chosen, is that because it is
the first obs?



-----Original Message-----
From: Arto Raiskio [mailto:arto.raiskio@SUOMENPOSTI.COM]
Sent: Monday, August 25, 2003 7:27 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: sql all corresponding


"Sigurd Hermansen" wrote
> Ya's given you a clear and concise example of the effect of the UNION
> qualifier CORRESPONDING. It takes a bit more complex example to illustrate
> the effects ALL qualifier and the related OUTER qualifier:

to recap, here's what I understood, kindly correct me if wrong

I can't really use any flavor of Union, Union Corr, Union All when I expect
to match and merge data

to illustrate

data a;input brick $8. paint $10-20 @22 t time.;cards;
red      a1234567890 08:11:12
black    a1234567891 08:11:13
;run;data b;input paint $1-11 @13 type $4.;cards;
a1234567890 fast
a1234567893 none
a1234567891 slow
a1234567891 fast
;run;
proc sql;create table c as select * from a
outer union corr select * from b;quit;
data _null_;set c;format t time.;put(_all_)(=);run;
proc sql;create table d as
    select * from a, b
    where index(b.paint,a.paint) ;quit;
data _null_;set d;format t time.;put(_all_)(=);run;
*-expected-------------------------
brick=red   paint=a1234567890 t=8:11:12 type=fast
brick=black paint=a1234567891 t=8:11:13 type=slow
brick=      paint=a1234567893 t=.       type=none;
*-got --------------------------
brick=red   paint=a1234567890 t=8:11:12 type=
brick=black paint=a1234567891 t=8:11:13 type=
brick=      paint=a1234567890 t=.       type=fast
brick=      paint=a1234567893 t=.       type=none
brick=      paint=a1234567891 t=.       type=slow
brick=      paint=a1234567891 t=.       type=fast;
*-settled for -----------
brick=red   paint=a1234567890 t=8:11:12 type=fast
brick=black paint=a1234567891 t=8:11:13 type=slow
brick=black paint=a1234567891 t=8:11:13 type=fast;