Date:         Sun, 5 May 2002 22:50:07 -0400
Reply-To:     Sigurd Hermansen <hermans1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <hermans1@WESTAT.COM>
Subject:      Re: Proc SQL By Groupings

In a SQL solution you have, in fact, an improvement on BY groups. You can
choose from inner, left, right, or outer joins. Rarely does a SQL program
begin with a Cartesian product. In the most explicit form, the program
spells out the condition of a join:

....
select t1.*,t2.other as newOther from datasetx as x inner join datasety as
y ON x.ID=y.ID ...

In this statement the SQL programmer declares an intersection of the ID
(one or more variables that datasetx and datasety have in common). The
intersection does much the same (but not exactly the same in all
circumstances) as a SAS MERGE with a BY group, but the programmer does not
have to sort datasets prior to joining them, and the keys compared in the
ON clause do no have to have the same name. Even better, the programmer can
refer to variables that have the same names in two datasets by qualifying
the names with the dataset name, and rename them at will.
Sig

On Sun, 5 May 2002 17:51:50 -0700, Paul Choate <pchoate@JPS.NET> wrote:

>I'd like to use Proc SQL to join two datasets of about 500k obs each.
>Because I want to get the cartesian product, I'm asking SAS to look at
>250 billion combinations, which is outside my resources.  (I guess I
>could run it a few days on a PC, but...)  If I could get Proc SQL to
>use by groups my problem would be solved because my By Groups are
>granular enough to drop the number of matches to the tens of millions,
>which would be okay.
>
>I just tried a "By" statement in a Proc SQL and regardless of what the
>online doc says, Proc SQL is a base procedure that does not accept By
>Group processing.
>
>My question is, is there a way to do something like a by group in an
>sql proc?  Or a cartesian join some other way that uses by groups on
>the joined elements?
>
>My email isn't pchoate@jps.net anymore, it's pchoate@dds.ca.gov or
>pchoate@gsos.net.  Thanks in advance.