sas transpose

[amar8581980]

i have a dataset in sas

col 1 col 2    wk 1   w2

A        A1         10      20

A         B1          3     5

I want the output to look like

col 1    A1_WK1  A1_WK2    B1_WK1   B2_WK2

A              10             20             3                5

i am new to sas just wondering if this is possible

i just want one column to be transposed

[Reeza · Accepted solution]

data have;
input _col1 $ _col2  $  wk1   wk2;
cards;
A        A1         10      20
A         B1          3     5
;

proc transpose data=have out=temp;
by _col1 _col2;
var wk1 wk2;
run;

proc transpose data=temp delim = _ out=want;
by _col1;
id _col2 _name_;
var col1;
run;

proc print;run;

Note I renamed col1 to not interfere with the default output name from PROC TRANSPOSE.

---

@amar8581980 wrote:

i have a dataset in sas

col 1 col 2    wk 1   w2

A        A1         10      20

A         B1          3     5

I want the output to look like

col 1    A1_WK1  A1_WK2    B1_WK1   B2_WK2

A              10             20             3                5

i am new to sas just wondering if this is possible

i just want one column to be transposed

---

[Kurt_Bremser]

Try a double transpose:

proc transpose data=have (rename=(col1=id)) out=long;
by id col2;
var wk:;
run;

proc transpose data=long out=want (drop=_name_ rename=(id=col1));
by id;
var col1;
id col1 _name_;
run;

But before you do this, what is the purpose of this wide dataset? Wide datasets are very unwieldy for future analysis, and the wide layout is usually the sign of a report.

[ballardw]

Another approach, which in effect is a manual transpose and adding the _name_ which transpose will use to rename the output variables:

data have;
input _col1 $ _col2  $  wk1   wk2;
cards;
A        A1         10      20
A         B1          3     5
;

data helper;
   set have;
   array w(*) wk1 wk2;
   length _name_ $ 32;
   do i=1 to dim(w);
      _name_= catx('_',_col2,vname(w[i]));
      value=w[i];
      output;
   end;
   drop i wk1 wk2;
run;

proc transpose data=helper out=want(drop=_name_);
  by _col1;
  var value;
run;
      

May be of interest if have a more complex naming scheme involved.

Note: Assumes the Have data is actually sorted by _col1 and that the combination of _col2 and the wk variable names will not create duplicates.