PROC TRANSPOSE is an appropriate tool for this problem.

But if Mike wants to perform a MERGE, he could try

data horizontal; merge test(where=(type='Bass') rename=(number=Bass )) test(where=(type='Heineken') rename=(number=Heineken)); by name; drop type; run;

after sorting TEST, of course.

On Fri, 4 Oct 2002 16:41:44 -0400, Parent, David <david.parent@CAPITALONE.COM> wrote:

>Hi Mike, > >I have decided to completely dodge the merge question and offer an >alternative solution with proc transpose. > >data test; >input name $7. type $11. number 3.; >cards; >Dan Bass 12 >Dan Heineken 8 >Jill Bass 6 >Lisa Heineken 1 >Arkay Bass 146 >Arkay Heineken 84 >run; > >proc sort data=test; > by name; >run; > >proc transpose data=test out=test2; >by name; >id type; >var number; >run; > >Regards, >David Parent > > > -----Original Message----- >From: Mike Fay [mailto:rmf4@CDC.GOV] >Sent: Friday, October 04, 2002 4:31 PM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Simple merge question > >Hi folks, > >Newbie question #11... > >I am having trouble getting a simple merge to work. I must be missing >something obvious, but Help is not helping. > >I am trying to turn a "vertical" list into a "horizontal" one. Here's an >example (not tested) where I'm trying to figure which friend to visit, >based on how much beer they have on hand. The vertical list reads: > > Name Type Number > ---- ---- ------ > Dan Bass 12 > Dan Heineken 8 > Jill Bass 6 > Lisa Heineken 1 > Arkay Bass 146 > Arkay Heineken 84 > >I want to turn this into: > > Name Bass Hein > ---- ---- ---- > Dan 12 8 > Jill 6 . > Lisa . 1 > Arkay 146 84 > >... so that the choice of friend to visit will be obvious. (It would be >Jill, of course! lol) > >Let's call the vertical (first) file work.beerV and the horizontal (merged) >one beerH... > >first I made a list of all names... > > sort work.beerV; by name; run; > > data work.beerH (keep=name); > set work.BeerV; > by name; > if first.name then output; > run; > >...then I merged the Bass counts into beerH... > > data work.beerH (drop=Type Number); > merge work.beerH work.beerV; > by name; > if Type='Bass' then Bass=Number; > run; > >then I merge Heineys into BeerH with a highly similar DATA statement. Or, >if I just put two IFs in the one merge DATA statement, either way, I get >the same problem... > >the problem is that my results look something like (remember, not tested): > > Name Bass Hein > Dan 12 . > Jill 6 . > Lisa . 1 > Arkay 146 . > >More precisely, there is only one record per name (which is good), but >there is also only only beer count per name (which is bad... most of >my 'friends' data should have counts for both columns, but not a single >record has both count columns filled in). I suspect it might effectively >only be reading the first (or last?) record from BeerV when it's merging, >because the majority of the records have one of the columns (but not the >other) filled in; I can only guess this corresponds to how one of the two >values appears first (or last) most of the time. > >I don't see any way to address this. > >Help? TIA! > > Mike > >************************************************************************** >The information transmitted herewith is sensitive information intended only >for use by the individual or entity to which it is addressed. If the reader >of this message is not the intended recipient, you are hereby notified that >any review, retransmission, dissemination, distribution, copying or other >use of, or taking of any action in reliance upon this information is >strictly prohibited. If you have received this communication in error, >please contact the sender and delete the material from your computer.