proc transpose

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

proc transpose


Hi Friends,

I need your help with a code. I have a data with Firm, Year,  DUM, TITLE, Name, Tenure. The subset of data looks as follows:

Firm     Year     DUM     TITLE     NAME          Tenure

A          1999     1     CEO        John Smith     1

A          2000     1     CEO        John Smith     2

A          2001     1     CEO        John Smith     3

A          2002     1     CEO        John Smith     4

A          2003     1     CEO        John Smith     5

A          2004     1     CEO        Danny            1

A          2005     1     CEO        Danny            2

A          2002     0     CFO        Steve Mills     1

A          2003     0     CFO        Steve Mills     2

A          2004     0     CFO        Frank            1

A          2005     0     CFO        Frank             2

DUM is 1 if the title is CEO and 0 otherwise. I want to transpose the data in such a way that the output looks as follows:

Firm     Year     dum TITLE_CEO     NAME_CEO          Tenure_CEO    TITLE_CFO     NAME_CFO          Tenure_CFO

A          1999     1     CEO              John Smith                1

A          2000     1     CEO              John Smith                2

A          2001     1     CEO              John Smith                3

A          2002     1     CEO              John Smith                4                     CFO              Steve Mills               1

A          2003     1     CEO              John Smith                5                     CFO              Steve Mills               2

A          2004     1     CEO              Danny                       1                     CFO              Frank                       1

A          2005     1     CEO              Danny                       2                     CFO              Frank                       2

I used the following code for transpose but am getting some error.

proc sort data=have; by firm year; run;

proc transpose data=have out=want;

by firm year;

var title name tenure;

id dum;

run;

Can anyone please provide me with the code?    


Accepted Solutions
Solution
‎03-23-2013 12:22 AM
Trusted Advisor
Posts: 1,137

Re: proc transpose

Hi

Please try the below code

data have;

  input firm $ year dum title $ name & $10.  tenure;

cards;

A          1999     1     CEO        John Smith     1

A          2000     1     CEO        John Smith     2

A          2001     1     CEO        John Smith     3

A          2002     1     CEO        John Smith     4

A          2003     1     CEO        John Smith     5

A          2004     1     CEO        Danny            1

A          2005     1     CEO        Danny            2

A          2002     0     CFO        Steve Mills     1

A          2003     0     CFO        Steve Mills     2

A          2004     0     CFO        Frank            1

A          2005     0     CFO        Frank             2

;

run;

proc sort data=have out=ceo;

  by firm year;

  where title='CEO';

run;

proc sort data=have out=cfo (keep=firm year title name tenure rename=(title=title_cfo name=name_cfo tenure=tenure_cfo));

  by firm year;

  where title='CFO';

run;

data want;

  merge ceo(in=a) cfo(in=b);

  by firm year;

  if a;

run;

Thanks,

Jagadish

Thanks,
Jag

View solution in original post


All Replies
Solution
‎03-23-2013 12:22 AM
Trusted Advisor
Posts: 1,137

Re: proc transpose

Hi

Please try the below code

data have;

  input firm $ year dum title $ name & $10.  tenure;

cards;

A          1999     1     CEO        John Smith     1

A          2000     1     CEO        John Smith     2

A          2001     1     CEO        John Smith     3

A          2002     1     CEO        John Smith     4

A          2003     1     CEO        John Smith     5

A          2004     1     CEO        Danny            1

A          2005     1     CEO        Danny            2

A          2002     0     CFO        Steve Mills     1

A          2003     0     CFO        Steve Mills     2

A          2004     0     CFO        Frank            1

A          2005     0     CFO        Frank             2

;

run;

proc sort data=have out=ceo;

  by firm year;

  where title='CEO';

run;

proc sort data=have out=cfo (keep=firm year title name tenure rename=(title=title_cfo name=name_cfo tenure=tenure_cfo));

  by firm year;

  where title='CFO';

run;

data want;

  merge ceo(in=a) cfo(in=b);

  by firm year;

  if a;

run;

Thanks,

Jagadish

Thanks,
Jag
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 190 views
  • 0 likes
  • 2 in conversation