topic sas transpose in SAS Enterprise Guide

sas transpose

amar8581980 — Wed, 04 Oct 2023 16:34:09 GMT

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

Re: sas transpose

Reeza — Wed, 04 Oct 2023 16:39:51 GMT

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

Re: sas transpose

Kurt_Bremser — Wed, 04 Oct 2023 16:45:16 GMT

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.

Re: sas transpose

ballardw — Wed, 04 Oct 2023 16:54:32 GMT

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.