Date:         Wed, 30 May 2001 20:13:16 -0400
Reply-To:     Rouslan Moukhine <rmoukhine@COSSETTE.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Rouslan Moukhine <rmoukhine@COSSETTE.COM>
Organization: Custometrics\Blitz\Cossette
Subject:      Re: merge question
Content-Type: text/plain; charset=us-ascii

A slightly modified version:

data Ds1;
input Ptid MD_Last $;
cards;
001  Adams
002  Jones
003  Keppler
004  Adams
004  Adams
;

proc sort data=ds1;
by md_last ptid;
run;

data ds1;
set ds1;
retain max 0;
by md_last ptid;
if first.md_last then group=0;
group+1;
if last.md_last then max=max(max,group);
call symput('max',max);
run;

proc print;
run;

data Ds2;
input MD_Last $ Prov_Id;
cards;
Adams          1234
Adams          9001
Adams          3425
Jones          8754
Jones          0012
Keppler        3214
Keppler        0394
Keppler        0004
Keppler        9995
;

data ds2;
 set ds2;
 do group=1 to &max;
 output;
 end;

proc print data=ds2;
run;

proc sort;
by md_last group;

data all (drop=group max);
 merge ds1 (in=inone) ds2 (in=intwo);
 by MD_Last group;
 if inone;
 run;

proc print;
run;


> "Huang, Ya" wrote:
>
> So far all the response are based on sql, and it is obviously
> the simplest solution in terms of number of lines. But it doesn't
> mean it can not be done by merge. To do it by merging, we can
> to add a second by var in data set 2, which is "patid" and then merge
> them by both "md_last" and "patid". We can expand data set 2 by adding
>
> a bunch of new observations with different patid. How do we know
> how many obs need to add? We don't know. We just add all the
> possible patid!
>
> data Ds1;
> input Ptid MD_Last $;
> cards;
> 001  Adams
> 002  Jones
> 003  Keppler
> 004  Adams
> ;
>
> proc sort;
> by md_last ptid;
>
> data Ds2;
> input MD_Last $ Prov_Id;
> cards;
> Adams          1234
> Adams          9001
> Adams          3425
> Jones          8754
> Jones          0012
> Keppler        3214
> Keppler        0394
> Keppler        0004
> Keppler        9995
> ;
>
> data ds2;
>  set ds2;
>  do ptid=1 to 4;
>  output;
>  end;
>
> proc sort;
> by md_last ptid;
>
> data all;
>  merge ds1 (in=inone) ds2 (in=intwo);
>  by MD_Last ptid;
>  if inone;
>  run;
>
> proc print;
> run;
>
> ----------------------
> The SAS System    09:52 Wednesday, May 30, 2001   2
>
> Obs    Ptid    MD_Last    Prov_Id
>
>   1      1     Adams        1234
>   2      1     Adams        9001
>   3      1     Adams        3425
>   4      4     Adams        1234
>   5      4     Adams        9001
>   6      4     Adams        3425
>   7      2     Jones        8754
>   8      2     Jones          12
>   9      3     Keppler      3214
>  10      3     Keppler       394
>  11      3     Keppler         4
>  12      3     Keppler      9995
>
> Have fun.
>
> Ya Huang