Transpose Column into rows

[sameer112217]

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.

[novinosrin]

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;

[andreas_lds]

---

@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.

[s_lassen · Accepted solution]

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;