LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (May 2012, week 3)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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 > ;


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