topic Re: Proc Transpose in SAS Enterprise Guide

Proc Transpose

Daily1 — Mon, 18 Sep 2023 08:50:25 GMT

/* Sample data */
data have;
input ID $ TYPE $ TYPE_ID;
datalines;
1 A 1
1 B 1
1 c 1
1 d 1
1 e 1
1 f 1
1 g 2
1 h 2
1 j 2
1 k 2
1 l 2
1 q 3
1 w 3
1 e 3
1 r 3
1 t 3
1 y 4
1 u 4
1 i 4
;
run;

Desired Output
ID    1       2       3        4
1     A        g        q        y
1     B        h        w        u
1     c        j        e        i
1     d        k        r
1     e        l        t
1     f

i need to transpose data

proc transpose data=haveout=youroutput prefix=ID_;
  by ID;
  var TYPE;
  id TYPE_ID;
run;

Re: Proc Transpose

Kurt_Bremser — Mon, 18 Sep 2023 09:16:01 GMT

data pretrans;
set have;
by id type_id;
if first.type_id
then group = 1;
else group + 1;
run;

proc sort data=pretrans;
by id group;
run;

proc transpose
  data=pretrans
  out=want
;
by id group;
var type;
if type_id;
run;

as you can't have multiple identical ID (the statement!) values within a BY group of PROC TRANSPOSE, so you must create a new group for this.

Re: Proc Transpose

Ksharp — Tue, 19 Sep 2023 11:36:44 GMT

/*Just for having some fun*/
data have;
input ID $ TYPE $ TYPE_ID;
datalines;
1 A 1
1 B 1
1 c 1
1 d 1
1 e 1
1 f 1
1 g 2
1 h 2
1 j 2
1 k 2
1 l 2
1 q 3
1 w 3
1 e 3
1 r 3
1 t 3
1 y 4
1 u 4
1 i 4
;
run;
proc sql noprint;
select distinct catt('have(where=(TYPE_ID=',TYPE_ID,') rename=(type=_',TYPE_ID,'))')
      into :merge separated by ' '
 from have;
quit;
data want;
 merge &merge.;
 by id;
 output;
 call missing(of _all_);
 drop TYPE_ID;
run;