Date:         Tue, 26 Aug 2003 11:16:20 -0400
Reply-To:     Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject:      Re: sql all corresponding
Comments: To: Arto Raiskio <arto.raiskio@SUOMENPOSTI.COM>
Content-Type: text/plain

Arto,

I think the answer is no, but let's rephrase the question.  I want

>*-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;

from

>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;

How can it be obtained with SQL code?

The first thing to notice is that PAINT=a1234567891 has two values of type,
but you want only one.  It is not clear whether that is mistake in your
expectations or whether that is what you want.  Assuming that is what you
want, the question becomes on what basis "slow" is chosen over "fast".  The
precise answer of how to write the SQL code depends on how you choose the
record with "slow" instead of the one with "fast".  I am going to assume
that it has nothing to do with the words, that "slow" is chosen because it
comes first in B.  (If that is not the case the number of possible ways to
choose becomes too large to write the code for all possibile ways to
choose.)

In this case, as I pointed out in another recent thread ("Re: Sql FIRST
function"), it is best (since you want to do this in SQL) to add a variable
to B so that you can identify the order of records, i.e. so that you can say
I want the first TYPE within PAINT.  To achieve that end I used

data b;
   input paint $1-11 @13 type $4.;
   seq = _n_ ;
cards;
a1234567890 fast
a1234567893 none
a1234567891 slow
a1234567891 fast
;

Now one way to achieve the wanted data set is

proc sql ;
   create table expected as
   select brick , paint , t , type
   from
   ( select a.brick
          , coalesce ( a.paint , b.paint ) as paint
          , a.t
          , b.type
          , b.seq
       from b full join a
         on a.paint = b.paint
      where a.paint is null or b.paint is null

     union corr all

     select a.brick
          , a.paint as paint
          , a.t
          , b.type
          , b.seq
       from a, b
      where a.paint = b.paint
   )
   group by paint
   having seq = min(seq)
   order by paint
   ;
quit ;

Here I delayed the elimination of the unwanted record to the end.  That
makes the code less efficient, but shorter.  I also chose to do the
elimination last because it would then be easier to remove if in fact it was
expectation that was wrong.  Instead one could use a subquery to extract the
correct records from B first and then do the join and union.

In using SQL it is important to understand the principle that data values
must support the information you want to extract from the data.  Typically
in procedural programming one uses the physical order in files in addition
to data values to extract information.  In SQL the order cannot be assumed
in order to allow the SQL compiler flexibility in how the extraction is to
be made.  Thus a certain amount of re-education is necessary if a
procedurally oriented programmer is going to work easily with SQL.

IanWhitlock@westat.com


-----Original Message-----
From: Arto Raiskio [mailto:arto.raiskio@SUOMENPOSTI.COM]
Sent: Monday, August 25, 2003 10: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;