Date:         Mon, 7 May 2001 11:52:39 -0400
Reply-To:     Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject:      Re: Can this be done in SQL?

The DATA step code will preserve observations from PARTONLY which do not
have corresponding observations in both of the large data sets. The SQL code
uses an inner join, which will exclude such records. This may be an
important difference.

An outer join can preserve mismatched rows, but can only operate on two
tables. So in this case it would probably be necessary to perform two joins,
in sequence.

On Mon, 7 May 2001 05:59:26 -0400, Ronald H=?ISO-8859-1?Q?=F6llwarth?=
<ronald.hoellwarth@KREDITWERK.DE> wrote:

>On Mon, 7 May 2001 20:40:32 +1200, Steve <stejam@XXX.ES.CO.NZ> wrote:
>
>>Hi all,
>>
>>Can the following merge statement be done in SQL?
>>
>>data partdet ;
>>    merge partonly         /*only a few records in here*/
>>                (in = __1__
>>                 rename = (pnidnum = idnum))
>>    client.clinte             /*approx million records in here*/
>>                (keep = givenme sname idnum
>>                 rename = (givenme = pgivenme
>>                                    sname = psnamef)
>>                )
>>    client.servic          /*approx million records in here*/
>>                (keep = idnum serv srvst fdate
>>                 rename = (serv = pserv
>>                                    srvst = psrvst
>>                   ));
>>    by swn ;
>>    if __1__  ;
>>    run ;
>>
>>From earlier posts it sounds like SQL could do this operation much quicker
>>but my SQL knowledge is very limited. Which way would be quicker??
>>
>>Any help much appreciated
>>regards
>>Steve
>
>Hello Steve,
>
>As I can't find the variable swm I'm using the variable idnum which you
seam
>to have in all three datasets:
>
>proc sql;
>create table partdet as
>select a.*, b.givenme as pgivenme, b.sname as psnamef, c.serv as pserv,
>c.srvst as psrvst, c.fdate
>from partonly as a, client.clinte as b, client.servic as c
>where
> a.pnidnum = b.idnum and
> a.pnidnum = c.idnum
>;
>quit;
>
>wether this is quicker or not - I don't know. But that's one way to do it
in
>SQL.
>
>HTH
>greetings from schwaebisch hall, germany
>ronald hoellwarth