Facing problem to collapse data

[Muhammad Salahuddin]

Hi,
Hope all of you are fine. I am here asking rather a simple and silly question. Having a monthly panel data set that is organized accordingly, I am trying to convert from monthly to quarterly.

While using the collapse option by "collapse dpi, by (qdate id)", it shows "type mismatch". Using different techniques, I couldn't solve the problem.


Also attached the data example for your consideration.


Thanks in advance

[Clyde Schechter]

The problem arises because your variable dpi is a string variable, so it is not possible to calculate mean values for it. You need to convert it to numeric. From its appearance, it seems the appropriate way would be:

destring dpi, replace

Then you will be able to do your -collapse-.

I appreciate the good intentions behind using -dataex-, but posting a screen shot of the -dataex- output defeats the purpose. The whole idea of -dataex- is to create a Stata program that can be copied from the Forum posts, pasted into the do-file editor, and then run to make a faithful replica of your example data set. But you cannot copy a screenshot into the data editor. In the future, when using -dataex- please use copy and paste directly from the -dataex- output in the Results window itself. Please do not post screenshots of -dataex-, they are not very helpful. Actually, posting screenshots here, in general, is rarely helpful, unless the only purpose is to show what some graphical content looks like.

Also, in the future, please don't state that you've tried several things but nothing worked. It is much more helpful to show the actual things you tried, and the results those produced.

[Muhammad Salahuddin]

destring dpi, replace

Thanks for your advice. As per your recommendation, I run "destring dpi, replace". Then running the following options-

collapse dpi, by(qdate id)
collapse (sum) dpi, by(qdate id)

However, still not working.

Herewith, I again put forward the dataex for your consideration. Pls

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id mdate) str103 industry str40 dpi float qdate
1 672 "10. (Food Products)"    "380.34" 224
1 673 "10. (Food Products)"    "317.81" 224
1 674 "10. (Food Products)"    "328.09" 224
1 675 "10. (Food Products)"    "365.01" 225
1 676 "10. (Food Products)"    "500.75" 225
1 677 "10. (Food Products)"    "414.3"  225
1 678 "10. (Food Products)"    "437.67" 226
1 679 "10. (Food Products)"    "321.67" 226
1 680 "10. (Food Products)"    "420.33" 226
1 681 "10. (Food Products)"    "298.01" 227
1 682 "10. (Food Products)"    "298.32" 227
1 683 "10. (Food Products)"    "462.06" 227
1 684 "10. (Food Products)"    "502.88" 228
1 685 "10. (Food Products)"    "464.32" 228
1 686 "10. (Food Products)"    "325.62" 228
1 687 "10. (Food Products)"    "494.16" 229
1 688 "10. (Food Products)"    "338.47" 229
1 689 "10. (Food Products)"    "410.61" 229
1 690 "10. (Food Products)"    "300.54" 230
1 691 "10. (Food Products)"    "320.12" 230
1 692 "10. (Food Products)"    "306.69" 230
1 693 "10. (Food Products)"    "298.14" 231
1 694 "10. (Food Products)"    "566.64" 231
1 695 "10. (Food Products)"    "407.91" 231
1 696 "10. (Food Products)"    "577.03" 232
1 697 "10. (Food Products)"    "456.32" 232
1 698 "10. (Food Products)"    "416.68" 232
1 699 "10. (Food Products)"    "410.83" 233
1 700 "10. (Food Products)"    "320.42" 233
1 701 "10. (Food Products)"    "532.56" 233
1 702 "10. (Food Products)"    "331.19" 234
1 703 "10. (Food Products)"    "447.13" 234
1 704 "10. (Food Products)"    "405.5"  234
1 705 "10. (Food Products)"    "460.62" 235
1 706 "10. (Food Products)"    "505.27" 235
1 707 "10. (Food Products)"    "369.63" 235
1 708 "10. (Food Products)"    "515.62" 236
1 709 "10. (Food Products)"    "313.56" 236
1 710 "10. (Food Products)"    "502.53" 236
1 711 "10. (Food Products)"    "478.29" 237
1 712 "10. (Food Products)"    "327.26" 237
1 713 "10. (Food Products)"    "347.51" 237
1 714 "10. (Food Products)"    "472.75" 238
1 715 "10. (Food Products)"    "327.55" 238
1 716 "10. (Food Products)"    "290.32" 238
1 717 "10. (Food Products)"    "291.17" 239
1 718 "10. (Food Products)"    "453.74" 239
1 719 "10. (Food Products)"    "289.03" 239
1 720 "10. (Food Products)"    "325.55" 240
1 721 "10. (Food Products)"    "299.3"  240
1 722 "10. (Food Products)"    "292.89" 240
1 723 "10. (Food Products)"    "323.06" 241
1 724 "10. (Food Products)"    "320.27" 241
1 725 "10. (Food Products)"    "487.79" 241
1 726 "10. (Food Products)"    "355.91" 242
1 727 "10. (Food Products)"    "447.98" 242
1 728 "10. (Food Products)"    "316.7"  242
1 729 "10. (Food Products)"    "454.17" 243
1 730 "10. (Food Products)"    "297.22" 243
1 731 "10. (Food Products)"    "298.02" 243
2 672 "12. (Tobacco Products)" "219.84" 224
2 673 "12. (Tobacco Products)" "325.87" 224
2 674 "12. (Tobacco Products)" "179.82" 224
2 675 "12. (Tobacco Products)" "217.7"  225
2 676 "12. (Tobacco Products)" "207.98" 225
2 677 "12. (Tobacco Products)" "229.37" 225
2 678 "12. (Tobacco Products)" "272.49" 226
2 679 "12. (Tobacco Products)" "214.05" 226
2 680 "12. (Tobacco Products)" "314.18" 226
2 681 "12. (Tobacco Products)" "184.7"  227
2 682 "12. (Tobacco Products)" "200.17" 227
2 683 "12. (Tobacco Products)" "219.84" 227
2 684 "12. (Tobacco Products)" "214.05" 228
2 685 "12. (Tobacco Products)" "219.48" 228
2 686 "12. (Tobacco Products)" "191.11" 228
2 687 "12. (Tobacco Products)" "244.22" 229
2 688 "12. (Tobacco Products)" "281.15" 229
2 689 "12. (Tobacco Products)" "281.29" 229
2 690 "12. (Tobacco Products)" "173.4"  230
2 691 "12. (Tobacco Products)" "196.7"  230
2 692 "12. (Tobacco Products)" "178.34" 230
2 693 "12. (Tobacco Products)" "286.04" 231
2 694 "12. (Tobacco Products)" "279.39" 231
2 695 "12. (Tobacco Products)" "290.25" 231
2 696 "12. (Tobacco Products)" "219.48" 232
2 697 "12. (Tobacco Products)" "280.66" 232
2 698 "12. (Tobacco Products)" "309.01" 232
2 699 "12. (Tobacco Products)" "275.13" 233
2 700 "12. (Tobacco Products)" "219.84" 233
2 701 "12. (Tobacco Products)" "347.87" 233
2 702 "12. (Tobacco Products)" "223.49" 234
2 703 "12. (Tobacco Products)" "281.13" 234
2 704 "12. (Tobacco Products)" "173.97" 234
2 705 "12. (Tobacco Products)" "219.48" 235
2 706 "12. (Tobacco Products)" "215.86" 235
2 707 "12. (Tobacco Products)" "229.33" 235
2 708 "12. (Tobacco Products)" "199.24" 236
2 709 "12. (Tobacco Products)" "200.7"  236
2 710 "12. (Tobacco Products)" "191.11" 236
2 711 "12. (Tobacco Products)" "219.84" 237
end
format %tm mdate
format %tq qdate

[Clyde Schechter]

I cannot reproduce your problem using your example data and code in my setup. It runs just fine:

. destring dpi, replace
dpi: all characters numeric; replaced as double

. collapse dpi, by(qdate id)

. collapse (sum) dpi, by(qdate id)

. list, noobs clean

    id    qdate         dpi  
     1   2016q1      342.08  
     2   2016q1   241.84333  
     1   2016q2   426.68667  
     2   2016q2      218.35  
     1   2016q3   393.22333  
     2   2016q3   266.90667  
     1   2016q4   352.79667  
     2   2016q4      201.57  
     1   2017q1      430.94  
     2   2017q1   208.21333  
     1   2017q2   414.41333  
     2   2017q2   268.88667  
     1   2017q3   309.11667  
     2   2017q3   182.81333  
     1   2017q4      424.23  
     2   2017q4   285.22667  
     1   2018q1   483.34333  
     2   2018q1   269.71667  
     1   2018q2      421.27  
     2   2018q2   280.94667  
     1   2018q3   394.60667  
     2   2018q3   226.19667  
     1   2018q4   445.17333  
     2   2018q4   221.55667  
     1   2019q1   443.90333  
     2   2019q1   197.01667  
     1   2019q2   384.35333  
     2   2019q2      219.84  
     1   2019q3      363.54  
     1   2019q4   344.64667  
     1   2020q1   305.91333  
     1   2020q2      377.04  
     1   2020q3      373.53  
     1   2020q4   349.80333

First, it is not helpful to say something is "not working." In what way did it not work? Did you get an error message? Did it run but produce results that differ from your expectations? Did Stata hang or crash the computer?

Next, it may be that in your full data set there are values of variable dpi that are not convertible to numeric values with -destring-. Your example data doesn't contain any, but perhaps the full data set does. If so, the -destring- command would have given you a warning that some values of dpi contain non-numeric characters and the variable was "not replaced." If so, you have to identify those values of dpi and do something about that. Just what you would have to do depends on what those values are. If this is the problem you are having, then to see the offending values run -browse dpi if missing(real(dpi)) & !missing(dpi)-.

Finally, I should point out that it doesn't make sense to run two -collapse whatever, by(qdate id)- commands in succession as you did. The first -collapse- will reduce the data to one observation per qdate/id combination. So the second one has only one observation to work with per -qdate/id-, and that means that the data will be left unchanged because the sum of one number is just that number. (The same is true for all the other -collapse- operators: applied to just a single number they give back that same number.)