Date:         Thu, 17 May 2012 10:52:59 -0600
Reply-To:     J <jdiebal@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         J <jdiebal@GMAIL.COM>
Subject:      Re: datastep matching obs problem
Comments: To: Bolotin Yevgeniy <YBolotin@schools.nyc.gov>
In-Reply-To:  <01384B2401936142AF5F65E3D12402780C80F5FA@EX3VS1.nyced.org>
Content-Type: text/plain; charset=us-ascii

Thank you.  I ended up with this solution:
> 
> data have;
>   infile datalines missover;
>   input partno $2. @4PartTag $2. @7 PartOpp $2.;
>   datalines;
> a1    a2
> b1 b1 b2
> b2    b1
> c1 c1 c2
> c1 c1 c2
> c2    c1
> ;
>  
> data want;
>   infile datalines missover;
>   input partno $2. @4PartTag $2. @7 PartOpp $2.;
>   datalines;
> a1    a2
> b1 b1 b2
> b2 b1 b1
> c1 c1 c2
> c1 c1 c2
> c2 c1 c1
> ;
>  
>  
> proc sql;
> create table solution  as
> SELECT A.partno,
>       CASE
>       WHEN B.PartTag IS NOTNULL THEN B.PartTag
>       ELSE A.PartTag
>       END as PartTag,
>       A.PartOpp
> FROM have A LEFT JOIN have B
> ON (A.partno = B.PartOpp);
> quit;
>  
>  
>  


Sent from Phone

On May 17, 2012, at 8:15 AM, "Bolotin Yevgeniy" <YBolotin@schools.nyc.gov> wrote:

> If i understand the requirement correctly...
> 
> Proc sql;
> Create table WANT as
> Select have1.parta, coalesce(have1.partb, have2.partb) as partb,
> have1.partc 
> From HAVE have1 left outer join HAVE have2 
> On have1.partc = have2.parta
> And not missing(have2.partb)
> ;
> Quit;
> 
> 
> WARNING: this will explode the number of records where you have
> duplicate matches
> 
> e.g. 
> a1 a1 a2
> a1 a1 a2
> a1 a1 a2
> a2 . a1
> a2 . a1
> 
> will result in 2 copies of each A1 and 3 copies of each A2, so will
> produce 12 records total, instead of 5
> 
> 
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of JD
> Sent: Wednesday, May 16, 2012 8:22 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: datastep matching obs problem
> 
> I have a dataset and need to fill in data (var partb) such that if a
> value
> is in partc and that same value is also in parta partb will then be
> filled
> in with value from parta. I hope this example explains it better. Thank
> you.
> 
> data have;
>   infile datalines missover;
>   input parta $2. @4 partb $2. @7 partc $2.;
>   datalines;
> a1    a2
> b1 b1 b2
> b2    b1
> c1 c1 c2
> c1 c1 c2
> c2    c1
> ;
> 
> data want;
>   infile datalines missover;
>   input parta $2. @4 partb $2. @7 partc $2.;
>   datalines;
> a1    a2
> b1 b1 b2
> b2 b1 b1
> c1 c1 c2
> c1 c1 c2
> c2 c1 c1
> ;