proc transpose with duplicate values

[podarum]

Hi,  I'm trying to get the proc transpose with a duplicate value ... wanting it to show in the columns.. Here's what I mean:

HAVE;

Acct    ID    Type

234    123   BOR

245    122   BOR

245    124   COB

245    125   COB

245    126   POA

246    128   BOR

246    127   POA

WANT

Acct   BOR    COB1    COB2       POA

234    123 

245    122        124         125          126

246    128                                       127

Thanks

[novinosrin]

Hi @podarum  You need an intermediate step to get the sequence and then it's straight forward-

data have;
input Acct    ID    Type $;
cards;
234    123   BOR

245    122   BOR

245    124   COB

245    125   COB

245    126   POA

246    128   BOR

246    127   POA
;

data temp/view=temp;
 set have;
 by acct type;
 if first.type then n=1;
 else n+1;
run;


proc transpose data=temp out=want(drop=_:);
 by acct;
 var id;
 id type n;
run;

Acct	BOR1	COB1	COB2	POA1
234	123	.	.	.
245	122	124	125	126
246	128	.	.	127

[PeterClemmensen · Accepted solution]

Try this

data have;
input Acct ID Type $;
datalines;
234 123 BOR
245 122 BOR
245 124 COB
245 125 COB
245 126 POA
246 128 BOR
246 127 POA
;

data temp;
   do _N_ = 1 by 1 until (last.Type);
      set have;
      by Acct Type;
      if not (first.Type and last.Type) then t = cats(Type, _N_);
      else t = Type;
      output;
   end;
run;

proc transpose data = temp out = want(drop=_:);
   by Acct;
   id t;
   var ID;
run;

Result:

Acct  BOR  COB1  COB2  POA 
234   123  .     .     . 
245   122  124   125   126 
246   128  .     .     127