```Date: Wed, 30 May 2001 20:13:16 -0400 Reply-To: Rouslan Moukhine Sender: "SAS(r) Discussion" From: Rouslan Moukhine 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 ```

Back to: Top of message | Previous page | Main SAS-L page