Exporting correlation results for all companies in a panel into excel

[Kate Pryce]

Hi all,

I'm trying to export results into excel and I'd usually use putexcel, but I'm having trouble getting it to export the results for every ID in my sample.

I'm running:
by ID: correlate adjustedRevenue adjustedOI

Does anyone know how to get the results from every ID into excel?

* Example generated by -dataex-. For more info, type help dataex
clear
input float(ID adjustedRevenue adjustedOI)
1 174.04813   37.19928
1 294.72977   62.74993
1  649.0655  120.05038
1  954.5475   170.4647
1  989.0937  131.83636
1  892.4097   92.94652
1  877.5094     72.734
1  1287.365    178.094
1  1674.908   331.2855
1   2557.89  604.83813
1 4468.7515   988.8685
1  5870.084   1325.257
1  9883.474  1207.0405
1 10363.486  1431.0327
1  7613.144   767.7521
1 4794.1055 -1280.3452
1 2442.2595 -185.80534
1 1517.5537  173.97925
1 1111.5864 -105.58484
1 1008.9455  -185.1705
1 1191.0099 -132.69574
1 1839.9536    48.2184
1 1827.6703   84.01294
1 1790.1293  198.36377
2 146.32205  19.710695
3 185.58026  35.771362
3 243.57654   44.71267
3    331.88   81.93309
3 372.32245   92.42505
3  355.1038   58.34927
3  327.0078   96.83813
3  221.5003   29.03988
3 217.28017   46.49731
3 199.81754   28.94145
3  162.4887   4.913128
3 194.91515  14.054737
3  279.6674  34.338932
3   234.155   13.55877
3 199.99828  -7.718551
3   331.661  12.523034
3   259.654  10.417974
3  263.2259  14.961594
3  304.7939   18.56952
3  398.3814   26.98311
3 515.59796   40.05764
3  757.8911   91.88888
3 1209.1892    378.274
3  408.6206   51.41244
3 132.84422  -184.8608
3  95.16264  -75.11956
3  77.62626  -35.77795
3 114.35115  -181.1258
3 135.48138  -94.77375
3  180.4369  -3.275075
3  354.0006  10.612848
3  639.4979   31.93538
3   950.273   58.06715
3 1006.7812   48.67156
4  674.5535   74.20273
4  796.2352   91.17432
4  895.5861  104.75555
4 1005.2687  126.29189
4 1049.9589  127.52206
4 1044.6508   93.94741
4  908.1694   74.54107
4  798.4496   48.49979
4  825.8218   58.94821
4  891.3833  68.653465
4  969.7365   81.72256
4 1099.2338  102.91794
4 1426.1196  144.04643
4  1648.269  169.56754
4  1799.377  156.39476
4  1485.802  109.39213
4 1055.4764   44.95914
4  986.3677   50.01488
4 1036.9498   75.53521
4 1158.6101   95.70392
4 1344.5707  137.67673
4 1540.4584  172.61273
4  1954.758   237.8621
4 1935.5685  225.74763
4 1844.5012   183.2632
4 2383.2935  263.47217
4  2699.008  289.73285
4  2762.322   312.5474
4  2556.493   321.6031
4  1970.887  103.47063
4  2052.883  169.03627
4  2155.341   180.7092
4 2087.3398  151.03156
4 2391.2896    178.898
4  2391.658  171.82207
5  34.94181   4.321829
5  61.30001 -15.986547
5  80.60378 -11.363566
5 30.660744  -9.326367
5 26.608004 -17.597063
5   24.2364 -16.218058
5 18.549856   -8.82907
end
label values ID ID
label def ID 1 "3COM CORP", modify
label def ID 2 "A & M FOOD SERVICES INC", modify
label def ID 3 "A V HOMES INC", modify
label def ID 4 "AAR CORP", modify
label def ID 5 "ACA JOE INC", modify

[Jeff Pitblado (StataCorp)]

If you have Stata 17 or newer, you can use the collect: prefix
with the by: prefix to capture the correlations for each level of
ID, construct a table layout from their tags, and export the
table to Excel. Here is an example, using your data [thank you ], to
get you started.

sort ID
collect : by ID: correlate adjustedRevenue adjustedOI
collect label levels result C "Correlation", modify
collect layout (rowname[adjustedRevenue]#colname[adjustedOI]#ID) (result[C])
collect export correlations.xlsx, replace

Here is the resulting table.

. collect layout (rowname[adjustedRevenue]#colname[adjustedOI]#ID) (result[C])

Collection: default
      Rows: rowname[adjustedRevenue]#colname[adjustedOI]#ID
   Columns: result[C]
   Table 1: 6 x 1

-------------------------------
                  | Correlation
------------------+------------
adjustedRevenue   |
  adjustedOI      |
    3COM CORP     |    .6354418
    A V HOMES INC |    .6904269
    AAR CORP      |    .8832126
    ACA JOE INC   |   -.0871732
-------------------------------

Here is a screenshot of the spreadsheet from LibreOffice.

If this is part of a bigger project using putexcel, search for collect in the documentation for putexcel to see its support for collections instead of using collect export.

[Kate Pryce]

That looks promising, but I get this error when I run it:

Note: collect is ignoring label "Correlation or covariance matrix" for level C of dimension result.

. collect label levels result C "Correlation", modify

. collect layout (rowname[adjustedRevenue]#colname[adjustedOI]#ID) (result[C])

Collection: default
Rows: rowname[adjustedRevenue]#colname[adjustedOI]#ID
Columns: result[C]

Your layout specification does not uniquely match any items. Dimension cmdset might help uniquely match items.

[Jeff Pitblado (StataCorp)]

You might need to start a new collection, it seems you already have a collection with results in it before running this example.
Try

collect create correlations

before rerunning your example.

Or, you can remove all collections in your current session with

collect clear

then rerun your example.

[Kate Pryce]

That worked perfectly! Thank you so much. One follow up, how would you include the # of observations per ID?

[Jeff Pitblado (StataCorp)]

If you do not care about idenditfying the variables participating in the
correlation you can change the layout to

collect layout (ID) (result[N rho])

If you want to keep the variables in the row header, then since
correlate posts the sample sizes to scalar r(N), you will
need to attach some extra tags to the results tagged with
result[N] so that they match with the row specification in the
layout.

Here is the code, with the changes highlighted.

sort ID
collect : by ID: correlate adjustedRevenue adjustedOI
collect addtags rowname[adjustedRevenue] colname[adjustedOI], fortags(result[N])
collect label levels result C "Correlation" N "N", modify
collect layout (rowname[adjustedRevenue]#colname[adjustedOI]#ID) (result[N C])

Here is the resulting table.

. collect layout (rowname[adjustedRevenue]#colname[adjustedOI]#ID) (result[N C])

Collection: default
      Rows: rowname[adjustedRevenue]#colname[adjustedOI]#ID
   Columns: result[N C]
   Table 1: 6 x 2

----------------------------------
                  |  N Correlation
------------------+---------------
adjustedRevenue   |
  adjustedOI      |
    3COM CORP     | 24    .6354418
    A V HOMES INC | 33    .6904269
    AAR CORP      | 35    .8832126
    ACA JOE INC   |  7   -.0871732
----------------------------------