Reshape to wide

[Navid Asgari]

I am trying to reshape the following table to wide format; but, I keep getting errors because, I think, my data structure is not what -reshape- is supposed to work with

Firm	Year	estimator
3D	2000	BEAR
3D	2000	GSAX
3D	2001	GSAX
Canon	2000	BEAR
Canon	2000	JPMORGAN
Canon	2000	GSAX

Now, I want this to be in this format:

Firm	Year	estimator1	estimator2	estimator3
3D	2000	Bear	GSAX
3D	2001	GSAX
Canon	2000	BEAR	JPMORGAN	GSAX

Could you please help me with the code?

Thanks,
Navid

[Pablo Bonilla]

Navid,

Please install dataex

ssc install dataex

, and send us an example of your data.

Best,
Pablo.

[David Benson]

See if this works. I think the challenge was that firm doesn't uniquely identify the obs, but i(firm year) does.

* Example generated by -dataex-. To install: ssc install dataex
dataex firm year estimator  // command I actually ran
clear
input str5 firm int year str8 estimator
"3D"    2000 "BEAR"    
"3D"    2000 "GSAX"    
"3D"    2001 "GSAX"    
"Canon" 2000 "BEAR"    
"Canon" 2000 "JPMORGAN"
"Canon" 2000 "GSAX"    
end

bysort firm year: gen estimator_number = _n  // need this counter variable for the j() in reshape
reshape wide estimator, i( firm year) j( estimator_number)
egen est_count = rownonmiss(estimator1 - estimator3), strok  // strok means "String OK"
label var est_count "Count of estimators firm had that year"
brow

. list, sepby(firm)

     +----------------------------------------------------------+
     |  firm   year   estima~1   estima~2   estima~3   est_co~t |
     |----------------------------------------------------------|
  1. |    3D   2000       BEAR       GSAX                     2 |
  2. |    3D   2001       GSAX                                1 |
     |----------------------------------------------------------|
  3. | Canon   2000       BEAR   JPMORGAN       GSAX          3 |
     +----------------------------------------------------------+

[Navid Asgari]

Thanks, David and Pablo!