[Lisa Wilson]

Sorry Nick. As I use dataex to produce a sample I change some labels to make it simply. Anyways, Tbtm should be replaced by profit.
If you do it now, you will see that it does not produce the required dataset as in #12.
I think one problem is with the id variable. In fact, I start with data about firms (gvkey is the firm id) as in my post #8 where each firm belong to an industry (I have 5 industries). I do not know why dataex shows industries as numbers (in my dataset industry is mining, manufacturing, and so on). As explained before I want to create different time series where each series represent the value weighted average of profit of firms in a specific industry at a certain quarter, i.e. as per #12. In this case, I will have only a time identifier and no firm identifier.
reshape requires a firm identifier which makes me confused.

Lisa

[Nick Cox]

OK. Now the code runs. The result with the example data is a single observation, but that's inevitable: the example data contain only one industry and one quarter. I can't test with the full dataset that I don't have, but I guess you need instead

reshape wide profit, j(panelid) i(fqdate)

I don't see any need for a firm identifier: reshape as such knows nothing about what your data mean. You want dates to define observations and industries to define variables, as I understand it.

You are likely to lose the industry text that way. reshape doesn't always preserve all the information in a dataset. You may need to put that back as variable labels.

[Lisa Wilson]

Hi again
When I tried the code:

collapse (mean) Tbtm [w=lag_me], by (industry fqdate)
egen panelid=group(industry), label

reshape wide Tbtm, j(panelid) i(fqdate)

It gave me the following error message:

. reshape wide profit, j(panelid) i(fqdate)
(note: j = 1 2 3 4 5)
industry not constant within fqdate
Type "reshape error" for a listing of the problem observations.
r(9);

end of do-file

r(9);

I attach a sample of the dataset here to make things easier.

As explained earlier I want to convert this panel data which contains firms and and their profits and associated industries into a time series data that includes industries aggregates (i.e. the value weighted profit of all firms in each industry as a separate time series variables). Having 5 industries in my dataset, I would expect to have 5 aggregate industry profit time series.

Please I really need help with this and thanks to all of you in advance.

Lisa

[Nick Cox]

Please don't try emotional blackmail, however mild or gentle. Everyone who asks a question presumably wants an answer. There isn't a hierarchy or queue that people answering follow on based on perceived need.

The way to get good answers is to ask good questions.

I am still struggling to understand what's causing difficulty here because details are not presented clearly or consistently or completely.

You are asked not to post .dta files, but I looked any way. In your sample dataset, the variable name profit is used and there is no variable called Tbtm or lag_me so the code in #18 needs to be simplified or adapted.

With your data I did this. I added a tricky extra, trying to keep as much as possible of the value label text for use as variable labels (following my aside in #17). It works:

use sample_profit_stata.dta , clear 

forval j = 1/5 { 
    local label`j' : label (industry) `j' 
} 

collapse (mean) profit, by (industry fqdate)

reshape wide profit, j(industry) i(fqdate)

forval j = 1/5 { 
    label var profit`j' "`label`j''"
}

list, sep(4)

     +---------------------------------------------------------------+
     | fqdate    profit1    profit2    profit3    profit4    profit5 |
     |---------------------------------------------------------------|
  1. | 1990q1    .745227   .8010588   .7397179   .5380291   .9268841 |
  2. | 1990q2   .8811146   .8615603   .7171642   .4746851   .9803613 |
  3. | 1990q3   1.145761   1.042951   1.027596   .6307718   1.365093 |
  4. | 1990q4    1.09449   .9714624   .9793909   .6249442   1.293292 |
     |---------------------------------------------------------------|
  5. | 1991q1   .8664826    .870782   .7654774   .4859166   1.029009 |
  6. | 1991q2   .9684059    .955861   .7627502   .4282674   1.046828 |
  7. | 1991q3   .9427209   .9056909   .7129015   .4445784   1.017303 |
  8. | 1991q4     .79417   .8086051   .7188345   .3957742   .8770282 |
     |---------------------------------------------------------------|
  9. | 1992q1   .6818098   .7872059   .6450382   .4306367   .7930876 |
 10. | 1992q2   .8145198   .8587036   .7329251   .4681344   .8646128 |
 11. | 1992q3   .8100548   .8584831   .7151828   .4739688   .8743342 |
 12. | 1992q4   .6509538   .7203751   .6213737   .4072715   .7490591 |
     |---------------------------------------------------------------|
 13. | 1993q1   .6468593   .6564524   .6154274   .4750081   .6871668 |
 14. | 1993q2   .6831167   .6926857   .5971057   .4737465   .7348801 |
 15. | 1993q3    .644363   .6665295   .5616587   .4684633   .6768462 |
 16. | 1993q4   .5796283   .6302921   .5323487   .4202363   .6673762 |
     +---------------------------------------------------------------+

. describe

Contains data
  obs:            16                          
 vars:             6                          
 size:           352                          
------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
fqdate          int     %tq                   
profit1         float   %9.0g                 Consumer Durables, NonDurables, Wholesale, Retail,
                                                and Some Services (Laundries,
profit2         float   %9.0g                 Manufacturing, Energy, and Utilities
profit3         float   %9.0g                 Business Equipment, Telephone and Television
                                                Transmission
profit4         float   %9.0g                 Healthcare, Medical Equipment, and Drugs
profit5         float   %9.0g                 Other -- Mines, Constr, BldMt, Trans, Hotels, Bus
                                                Serv, Entertainment, Finance
------------------------------------------------------------------------------------------------
Sorted by: fqdate

The key point is that -- allowing for your use of different files, different variable names, etc. -- the error message makes no sense in principle.

A consequence of collapse by industry and quarterly date should be that each combination of industry and quarterly date occurs just once. So, the reshape message should not ever be seen.

Sorry, but I can only guess that you're doing something different from what you tell us. I can't see that a larger dataset would show the problem you're reporting either. Positively, a dataset such as you want can be produced.