CODEWITHSUNDEEP
sastranspose
IceCreamToucan
IceCreamToucan

Reputation: 28705

Proc Transpose With multiple ID values per Group

In this first data-set each employee has one team lead and one supervisor. I can transpose that no problem.

data a;
input employee_id ReportsTo $ ReportsToType $12.;
cards;
100 Jane  Supervisor
100 Mark  Team_lead
101 Max   Supervisor
101 Marie Team_lead
102 Sarah Supervisor
102 Sam   Team_lead
;
run;

proc transpose data = a
                out = aTP(drop =  _:);
by employee_id;
id ReportsToType;
var ReportsTo;
run; 
/* Output */
/*employee_id   Supervisor  Team_lead */
/*100                 Jane       Mark */
/*101                  Max      Marie */
/*102                Sarah        Sam */

Now, what if an employee can have anywhere from 1 to 3 team leads?

data b;
input employee_id ReportsTo $ ReportsToType $12.;
cards;
100 Jane  Supervisor
100 Mark  Team_lead
100 Jamie Team_lead  
101 Max   Supervisor
101 Marie Team_lead
101 Satyendra Team_lead
101 Usha      Team_lead
102 Sarah Supervisor
102 Sam   Team_lead
;
run;

/* Desired Output */
/*employee_id   Supervisor  Team_lead1     Team_lead2  Team_lead3 */
/*100                 Jane        Mark          Jamie             */
/*101                  Max       Marie      Satyendra        Usha */
/*102                Sarah         Sam                            */

Using proc transpose gives an error telling me I can't have more than one identical ID variable in each group. Is there a procedure for transposing which does allow this? 

ERROR: The ID value "Team_lead" occurs twice in the same BY group

Upvotes: 1

Views: 3410

Answers (1)

Robert Penridge
Robert Penridge

Reputation: 8513

You need to change your input data so that rather than the word Team_lead repeating, it shows it incrementing... i.e. Team_lead1, Team_lead2, etc...

You can use by-group processing and the retain statement to achieve this:

proc sort data=b;
  by employee_id reportstotype;
run;

data want;
  set b;
  by employee_id reportstotype;
  retain cnt .;

  if first.reportstotype then do;
    cnt = 1;
  end;

  if upcase(reportsToType) eq 'TEAM_LEAD' then do;
    reportsToType = cats(reportsToType,cnt);
  end;

  cnt = cnt + 1;

run;

Then simply call proc transpose like you did beforehand:

proc transpose data=want out=trans;
  by employee_id;
  id reportsToType;
  var reportsTo;
run;

Upvotes: 1

Related Questions