topic Re: Transpose Column into rows in SAS Programming

Transpose Column into rows

sameer112217 — Sun, 05 Jul 2020 20:46:55 GMT

Hello!

Below is my dataset. This is company hierarchy level data

Owner Owner1 Owner2

Tom Sam Radha

Tom Sam Jane

Tom Jack Chang

Jane Vijay Ajay

Jane Uma Sandeep

Jane Peter Brian

Jane Peter Lance

Tom and Jane are level 7 Managers, Sam and Jack Reports to Tom and then there are people under Sam and Jack. I need the below data as my output

Owner1 Owner2 Owner3 Owner4 Owner5 Owner6 Owner7 Owner8

Tom Sam Jack Radha Jane Chang

Jane Peter Vijay uma Ajay Sandeep Brian Lance

Based on the Name variable grouping (first column, I want everyone under Tom and those employees under his hierarchy. Similarly for Jane.

I have worked on a logic but it looks too long.

Split the table into two tables based on group. In my real example table there are 7 groups.

Then bring all the three columns values into one column

Use distinct and transpose.

Append all table

Let me know if there is better approach.

Re: Transpose Column into rows

novinosrin — Sun, 05 Jul 2020 23:36:41 GMT

data have;
input (Owner           Owner1              Owner2) (:$10.);
cards;

Tom               Sam                  Radha  

Tom               Sam                  Jane

Tom               Jack                  Chang

Jane              Vijay                  Ajay

Jane              Uma                  Sandeep

Jane              Peter                 Brian

Jane              Peter                 Lance
;


data want;
  set have;
  by Owner notsorted;
  if first.Owner then do; _n+1;	k=0;end;
  array t Owner Owner1   Owner2;
  array j(999)$ _temporary_;
  do over t;
   if t in j then continue;
   k+1;
   n=_i_;
   v=t;
   output;
   j(k)=t;
  end;
  if last.Owner then call missing(of j(*));
  keep _n Owner n v;
run;

proc sort data=want out=_want ;
by _n  n ;
run;

proc transpose data=_want out=final__want(drop=Owner _name_) prefix=Owner;
by Owner notsorted;
var v;
run;

Re: Transpose Column into rows

andreas_lds — Mon, 06 Jul 2020 05:48:21 GMT

@sameer112217 wrote:

I have worked on a logic but it looks too long.

Split the table into two tables based on group. In my real example table there are 7 groups.

Then bring all the three columns values into one column

Use distinct and transpose.

Append all table

Let me know if there is better approach.

The length does not matter, but readability and maintainability do. So please post the code you have actually used, so that we can suggest improvements.

Re: Transpose Column into rows

s_lassen — Mon, 06 Jul 2020 08:44:35 GMT

Like this?

data long;
  set have;
  array owners Owner Owner1-Owner2;
  do level=1 to dim(Owners);
    sub=Owners(level);
    if not missing(sub) then 
      output;
    end;
  keep Owner level sub;
run;
  
proc sort nodupkey data=long;
  by owner level sub;
run;

proc transpose data=long out=want(drop=_name_ Owner) prefix=Owner;
  by owner;
  var sub;
run;