Date:         Mon, 12 Feb 2001 19:18:04 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Merging "unrelated" records
Comments: To: Andreas De Troy <Andreas.DeTroy@ADVALVAS.BE>
Content-Type: text/plain; charset="iso-8859-1"

This continues an earlier discussion of the differences between the SAS data
step MERGE and the SQL join.  You want a Cartesian product of two datasets,
it seems, and will find it easier to use an unrestricted SQL join:

proc sql;
  create table deCart as
  select * from one as t1,two as t2
  ;
quit;

Sig
-----Original Message-----
From: Andreas De Troy [mailto:Andreas.DeTroy@ADVALVAS.BE]
Sent: Monday, February 12, 2001 6:19 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Merging "unrelated" records


Hi,

I have the following problem:

I have 2 datasets,

-one with (unique) ID's, like:
    "A5001"
    "A5003"
    "A5114"
    ...
-and one with a variable number of types, like:
    "type 1"
    "type 2"
    "type 3"
(could be two different types, could be one, could be three or four... this
is variable)

Now I want to combine them, so that every ID is merged with all of the types
available. The resulting dataset should look like:
    "A5001"        "type 1"
    "A5001"        "type 2"
    "A5001"        "type 3"
    "A5003"        "type 1"
    "A5003"        "type 2"
    "A5003"        "type 3"
    "A5114"        "type 1"
    etc.

Is there any general way to do this, considering that the number of types is
not fixed? I think this is a simple problem, yet I cannot find a solution -
I don't see how a merge could be performed here. Anyone able to help?

Thanks in advance,

Andreas.