Panel data: Merging two rows and its respective variables into one row

[Taria Smith]

Hello everyone,

I am very new to Stata, using version 16.

I would like to keep my panel data structure and "merge" two rows (CEO & CFO data) into one row by gvkey fyear.

My dataset looks like this:

gvkey fyear execid ceoann cfoann total_comp_new
001004 2006 09249 CEO 6787.1
001004 2006 09252 CFO 1885.9
001004 2007 09249 CEO 2453.37
001004 2007 33979 CFO 777.177
001004 2008 09249 CEO 3313.996
001004 2008 33979 CFO 952.892

Currently I have for each gvkey and fyear two rows; one for CEO and its compensation variables and one row with CFO's compensation variables.

How could I operationalize in Stata that in the end I have for each firm year one row listing CEO and CFO variables horizontally?

Thank you so much for your help!

[Ali Atia]

reshape wide execid total_comp_new, i(gvkey fyear) j(ceoanncfoann) string

[William Lisowski]

Your example data was confused; this is my best guess at figuring out what your data are meant to represent.

* Example generated by -dataex-. For more info, type help dataex
clear
input str6 gvkey int fyear str5 execid str3 pos float comp
"001004" 2006 "09249" "CEO"   6787.1
"001004" 2006 "09252" "CFO"   1885.9
"001004" 2007 "09249" "CEO"  2453.37
"001004" 2007 "33979" "CFO"  777.177
"001004" 2008 "09249" "CEO" 3313.996
"001004" 2008 "33979" "CFO"  952.892
end
reshape wide execid comp, i(gvkey fyear) j(pos) string
list, clean abbreviate(16)

. reshape wide execid comp, i(gvkey fyear) j(pos) string
(j = CEO CFO)

Data                               Long   ->   Wide
-----------------------------------------------------------------------------
Number of observations                6   ->   3           
Number of variables                   5   ->   6           
j variable (2 values)               pos   ->   (dropped)
xij variables:
                                 execid   ->   execidCEO execidCFO
                                   comp   ->   compCEO compCFO
-----------------------------------------------------------------------------

. list, clean abbreviate(16)

        gvkey   fyear   execidCEO    compCEO   execidCFO   compCFO  
  1.   001004    2006       09249     6787.1       09252    1885.9  
  2.   001004    2007       09249    2453.37       33979   777.177  
  3.   001004    2008       09249   3313.996       33979   952.892  

.

Please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It is particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.