topic Re: proc transport with duplicate values in SAS Programming

proc transpose with duplicate values

podarum — Sat, 16 Jan 2021 19:40:54 GMT

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

Re: proc transport with duplicate values

novinosrin — Sat, 16 Jan 2021 18:45:01 GMT

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

Re: proc transport with duplicate values

PeterClemmensen — Sat, 16 Jan 2021 19:07:56 GMT

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