Spearman's rank correlation with significance star in Word or Excel output

[Francois Durant]

Hi,

I would like to create a spearman's rank correlation coefficients table with statistical significance with *, **, and ***. I would like to have it exported in nice format to copy/paste in an academic research paper. I have tried with "asdoc" but it does not output the stars. Would you be able to help me with the set of variables below?

Data:
clear
input double(qdate aaci_combined_au aaci_temp_high_au aaci_temp_low_au aaci_rainfall_au aaci_sea_level_au aaci_cdd_au aaci_wind_au yoy_cpi yoy_ngdp yoy_int yoy_unemp)
84 -.36 .05 -.02 .04 -1.17 -.04 . . .1271122025375666 .28643852946768056 -.04587305457845059
85 .03 -.29 -.06 .22 .16 .18 . . .14292247884747322 .18588177767809433 -.11417189168872977
86 -.12 -.17 -.17 .15 -.35 -.49 . . .1664775714641502 .259419896920732 -.04786934240414553
87 .1 .04 -.2 -.12 .39 .03 . . .14697246250297602 .2836290780527677 -.0017045554886018222
88 .1 .41 .18 -.17 .05 0 . . .14841520983555534 .2862068971148293 .07610175352398363
89 -.42 -.41 -.15 -.18 -.69 -.02 . . .1497348939575831 .2030895986000183 .18833087161355144
90 -.63 -.24 -.64 -.42 -1.25 .66 . . .10397836452921494 .07447266442650191 .22136159380395437
91 -.47 .02 -.49 -.19 -1.24 .71 . . .09045411563919825 -.07558528428093636 .4726505233415663
92 -.27 .32 -.1 -.3 -.83 .92 . . .07681362363357125 -.24013787816159748 .5441401975820732
93 .32 .12 -.44 1.12 -.27 -.54 . . .04890039372647981 -.30987844530046227 .5593660611895279
94 -.01 -.2 -.25 .07 .08 -.27 . . .09601168987450581 -.27844551262019235 .45398618620312803
95 .22 -.38 -.18 .12 .92 -.32 . . .10841564264715209 -.26724553762662806 .10360811133060843
96 -.02 -.51 -.2 .01 .43 -.35 . . .14497556473771422 -.10357862900615467 -.029640802187959836
97 -.22 -.53 -.34 -.05 -.08 -.17 . . .15657728281382854 .003969238153319621 -.1055894916588288
98 -.1 -.51 -.55 .29 -.1 -.01 . . .10673672653125243 -.04525263796633738 -.14843044977368225
99 -.22 -.44 -.55 .15 -.37 -.27 . . .09510179937794572 .1445029620985836 -.11021638326690542
100 -.23 -.2 .13 -.34 -.15 .31 . . .09075088387770425 .19342142221157688 -.08922545135089699
101 .09 .05 -.2 -.05 .26 -.15 . . .1092721762986606 .16308376575240913 -.07400158087246089
102 -.08 -.06 -.04 .05 -.22 .1 . . .12177579365079372 .40069787798798995 -.07472216214081873
103 -.27 -.37 .02 .07 -.51 -.33 . . .12512414838563535 .5559390276100087 -.08831315040066778
104 -.2 -.4 .14 -.1 -.11 .05 . . .1047982130561107 .26501766784452285 -.07173066663208816
105 -.01 .3 .39 -.21 -.13 .47 . . .08240790133035891 -.04036541300191199 -.07641221091942596
106 -.22 -.27 -.68 .33 -.71 .04 . . .07709800701178104 .044218393181603144 .008596162485175585
107 -.36 -.39 -.33 -.06 -.63 -.46 . . .09177352412810502 -.1081330868961431 .06278495056335132
108 -.27 -.29 -.4 -.21 -.31 .15 . . .09852089445601453 -.08808193687150823 .04306352193279417
109 -.2 0 -.62 -.11 -.49 -.25 . . .11978614417347422 -.05025459396717846 .049327676200307646
110 -.23 -.27 .35 .07 -.5 .02 . . .13494809688581322 -.2598409544215028 -.029157823796887472
111 .04 .47 -.06 -.18 -.17 .08 . .0720720720982897 .13108173281511526 -.2559585488613385 -.047881090140257876
112 -.15 -.24 -.08 -.17 -.05 -.09 . .06843267107433171 .1433770618842023 -.3271390648003143 -.0918952068593929
113 .15 .15 .2 .37 -.08 .14 . .06941431672511289 .12908431356858974 -.14289044265734274 -.06370692053375704
114 .16 .27 .47 .33 -.11 -.27 . .07478632489841264 .13282864820173623 .07547676631748579 -.13122023226947577
115 .46 .53 .14 -.07 .93 -.01 . .0756302522396064 .13522868358018036 .22116991581583 -.14799625780750614
116 -.11 -.36 -.04 -.11 .14 -.21 . .06818181823237945 .12793948562783664 .4977238244302651 -.12259440896933127
117 .36 -.16 .13 .84 .4 -.5 . .07707910747361679 .14271033888094875 .4509110681395403 -.19521270449218286
118 -.48 -.46 -.53 -.14 -.83 -.51 . .07952286284221244 .12422169004447481 .3521478520599033 -.13338324625769826
119 -.09 -.42 .29 -.16 .31 .3 . .07812500001642997 .1082720222708291 .24315693481823852 -.13236133252737992
120 -.12 -.18 -.15 .02 -.19 .15 . .08510638289896066 .09629168633846952 .00040526869300916424 -.05363662922147605
121 .41 .19 -.13 .53 .51 -.42 . .07532956687981285 .07615021241846964 -.15257731942878683 .0489915903713507
122 .16 -.2 -.31 .27 .43 -.23 . .060773480701328264 .049744885020744034 -.21924639837102955 .20108426900895426
123 -.28 -.22 .33 -.37 -.25 .08 . .0688405796748075 .044157371689443004 -.3088073393928794 .34252107343870386
124 .37 .34 .22 .24 .53 .49 . .0499108735542142 .011412351962415634 -.29025724138337916 .39837973413823113
125 -.27 -.26 .2 -.36 -.18 .55 . .03502626980729606 -.006675631543559657 -.2817960630391506 .48770199797392655
126 .28 .42 .35 .09 .35 .22 . .032986111000442575 .0069960478127688575 -.2890939198012775 .37217399887231495
127 -.43 -.25 .18 -.31 -.74 .8 . .015254237314427987 .004257634626215978 -.33235996834819226 .2946931558004373
128 -.16 -.32 -.08 .25 -.39 .02 . .016977928742669457 .03286366803437324 -.3584474886130137 .19388268644277362
129 -.36 -.25 .36 .06 -.89 -.45 . .011844331620939785 .03588454286128706 -.37911918675057577 .10645282604183759
130 .02 -.33 .1 -.1 .49 -.21 . .00672268916809049 .037894272197752654 -.42396006674743425 .09766768878543841
131 -.15 -.39 -.61 .23 -.3 -.61 . .0033388980294626336 .04872669892299353 -.298608349872708 .08661992457273793
132 -.16 -.34 -.24 .27 -.43 -.39 . .011686143552514405 .051268597983735065 -.24021352314235822 .04751615825571509
133 -.4 -.32 .29 -.27 -.61 .17 . .018394648721034912 .05786643765285748 -.2242063492559524 .01998859494592997
134 -.25 .05 .26 .16 -.97 -.24 . .021702838000418723 .04669933431952655 -.1513575967071057 .006354395776418276
135 -.2 -.33 .06 .06 -.35 -.58 . .01830282869230171 .04784953546340365 -.18310657602040814 -.024735435836105824
136 -.05 .1 -.35 .09 -.33 -.07 . .014851485174023571 .05080032797404721 -.15163934421262526 -.039297709395872515
137 .15 .05 -.48 -.29 .69 .6 . .01642036129759261 .05414515773218609 -.04795396419743947 -.08749274896054615
138 -.22 .09 .02 -.18 -.55 .7 . .017973856279871026 .08632553373788765 .15316541864171773 -.12962653947392955
139 -.11 -.16 -.37 -.41 .23 .82 . .02777777780710089 .057878620116950996 .49618320628009593 -.16774726031091491
140 -.16 .02 .18 .09 -.58 -.06 . .03902439025158899 .0560710408467906 .6963423049689441 -.158582016167651
141 -.23 -.1 -.55 -.13 -.46 -.13 . .04523424888812966 .05600991118440324 .5540631296544032 -.15214847296579725
142 .37 .74 -.33 0 .36 .33 . .05136436598109473 .04760781890327204 .33589138120803463 -.1188577352882787
143 -.08 -.17 -.35 .16 -.25 -.13 . .0492845786865459 .07442659143873431 .03710575129698013 -.07054574201040642
144 .05 -.33 -.81 .08 .41 -.36 . .037558685284623916 .06893948520258597 -.08624898283507931 -.043214852444324436
145 -.22 .03 .02 .15 -.85 -.02 . .030911901004493147 .07156446191895482 -.01944684529038221 .00550299587377534
146 .07 -.09 .13 .07 .25 -.08 . .021374045764382288 .05489797343971925 -.05479452050617739 .028761765842313602
147 .09 .05 -.18 .21 .01 -.46 . .016666666568780464 .04916398395701571 -.13774597487199214 .030522207800008472
148 .06 .16 .15 .15 -.14 -.03 . .01206636501170566 .04502663430979936 -.20837043632197816 .027014775993800644
149 -.25 -.31 .02 -.1 -.35 .3 . .002998500790769576 .055030329930463084 -.23931247246537302 .007213237073596668
150 -.48 -.43 -.31 -.27 -.74 -.05 -.25 -.002989536662722503 .05656601330272526 -.30528284249649373 -.027010274667525813
151 -.29 .04 .3 .14 -1.04 -.13 -.29 -.002980625812110005 .06935447601500111 -.2297717842723147 -.07295929595900219
152 -.33 .07 .37 -.2 -.85 .02 .39 -.0014903128258202392 .06725764242371945 -.1608548932417967 -.09090802946780652
153 .33 1.1 .33 -.09 -.01 .11 .26 .007473841495857059 .04923607653959139 -.11935110070216404 -.09038495131619428
154 .05 .12 -.07 .52 -.48 -.45 .26 .013493253397032001 .060480501392757624 .035666218104688285 -.06777526153967384
155 .25 .12 .02 .35 .27 -.62 -.18 .01494768299171434 .052300261840480866 -.022895622828282836 -.07372613986982579
156 -.08 -.17 .32 -.03 -.04 -.49 .1 .011940298509861336 .05439651862280814 -.0368632707129265 -.09558167411202945
157 .21 -.04 -.15 .05 .63 -.26 0 .01038575665949204 .05206460796172174 -.042763157957713016 -.11153833792696566
158 -.01 -.2 .51 -.3 .46 -.29 -.24 .016272189253448532 .05322914272581025 -.03703703703703698 -.11552597269191445
159 .04 -.09 .2 .02 .2 -.36 -.48 .017673048686128512 .05209246557681402 .13025499654512362 -.09847773434864682
160 .03 -.22 -.26 .18 .12 -.32 -.28 .028023598726364174 .07444320926131787 .2073764787056367 -.061858930860465566
161 -.1 .03 -.31 .13 -.46 -.65 .16 .03083700451324889 .08116520148381534 .2769759449484537 -.08578190654272899
162 -.05 -.33 -.1 -.24 .4 -.05 -.17 .06113537123858381 .0875485532230611 .2995951417004048 -.12594167532936895
163 -.05 -.18 .28 .49 -.46 .38 -.11 .05788712013043207 .06303573945064311 .15487804864909283 -.07083551173744962
164 .39 .2 .15 .04 .95 .04 .04 .060258249697394906 .06122825169170021 -.040922190088813926 -.04229269252364909
165 -.09 -.28 .11 -.12 .14 .16 -.09 .06125356116944092 .05878620527583567 -.20775026901010507 .08380293135936712
166 .24 .05 .38 .01 .65 .18 .2 .024691358068455127 .05487493765514562 -.24506749735202493 .14072660166780038
167 -.09 -.24 .15 -.11 .09 -.41 .07 .03146374827913245 .07561514031942917 -.3194297781583648 .13057264482414754
168 .13 -.08 -.02 -.11 .59 -.17 -.43 .02976995937522342 .0686105865939004 -.21694711549176993 .04919509523164378
169 -.02 .36 .49 -.35 -.06 .6 -.13 .02818791941652732 .07641385892484576 -.014945652308767321 -.06373207096616629
170 .08 .45 .09 -.31 .1 .78 .26 .033467202167758 .06959050025814517 .02200825295584541 -.08336957284887525
171 .1 .31 .42 -.41 .38 .62 .86 .030503978769613527 .07212672010974397 .13033359192161864 -.11816306258065568
172 -.03 .16 .2 .05 -.31 .68 .21 .0341655716607312 .06051930619350232 .0982348427550448 -.08816252535825309
173 .17 -.06 .22 -.13 .7 -.35 .14 .02610966065295428 .052781887446065934 -.017931034346064023 -.05396993514718784
174 .14 .06 .34 -.01 .36 -.36 -.01 .02461139888170738 .062136070321630044 -.027590847781129968 -.06126071548501888
175 .13 .31 -.03 -.21 .28 .23 .13 .02445302451134479 .07109798276654877 .0809883321152376 -.06929312403723131
176 .17 .55 .19 .07 -.1 -.07 .15 .01905972044456883 .07984356613829857 .16212438846960175 -.0881718977553021
177 -.11 -.03 .19 -.23 -.06 .08 -.43 .025445292558469834 .0853564188692919 .16151685378079228 -.10113801849784365
178 -.12 -.22 .18 -.3 .16 -.04 .28 .02275600509750375 .07599060462401752 .12941176462772241 -.07074354541362715
179 .07 .15 .16 -.05 .11 -.02 -.2 .026381909502365408 .06762450945165921 .031746031809523956 -.10034490473695068
180 .02 -.14 -.09 0 .21 -.23 .17 .023690772988986586 .06658669364992886 .013830427000230516 -.07878125223092214
181 .12 .67 .23 -.26 -.06 .39 -.18 .02481389585589211 .07279231969971756 .033252720800075686 -.06862958340202652
182 .42 .75 .19 .2 .31 .05 .29 .0309023486028408 .08278796393448462 .03492647058823528 -.09341285510239528
183 .21 .29 .34 .11 .22 -.35 .09 .0269277845514293 .08709619612488151 .03938461532065318 -.018174105475708302
end
format %tq qdate
[/CODE]


Here is the code I have so far, but I do not get the stars and I need them:
asdoc spearman aaci_combined_au aaci_temp_high_au aaci_temp_low_au aaci_rainfall_au aaci_sea_level_au aaci_cdd_au aaci_wind_au yoy_cpi yoy_ngdp yoy_int yoy_unemp, star(all) replace dec(2) label

[Nick Cox]

The usual machinery for P-values is likely to be undermined by serial dependence as your data are time series.

[Francois Durant]

This is required and explicitly demanded for the revision of an academic article. Would you be able to provide the code? Thank you in advance, Nick!

[Nick Cox]

I don’t use asdoc and so stop there for that reason alone. Sorry to disappoint.

[Frode Andre]

Not using asdoc, but the recipe from the first example in the attached file illustrates how to get significance stars into a table of correlations.

https://www.stata.com/manuals/tablesexample1.pdf

[Francois Durant]

I was asking a solution adapted to my code that would not use asdoc. Would you be able to provide me with the actual code? I have been struggling to implement. Thank you in advance Nick!

[Nick Cox]

I don’t think I can be of any further help here. Sorry.

[Francois Durant]

Thanks for responding at least, Nick. i appreciate it.

[Francois Durant]

I am stuck here, so if anyone has the answer, it would be greatly appreciated! Thanks!

[Frode Andre]

English is not my mother tongue, so I am a little bit unsure whether it is a requirement that asdoc must be used. If not, I can produce a table of Spearman's correlation coefficient using the recipe from the link from #5 (changing to spearman).

label variable aaci_combined_au "AACI"
label variable aaci_temp_high_au "H.Temp."
label variable aaci_temp_low_au "L.Temp."

spearman aaci_combined_au aaci_temp_high_au aaci_temp_low_au aaci_rainfall_au aaci_sea_level_au aaci_cdd_au aaci_wind_au yoy_cpi yoy_ngdp yoy_int yoy_unemp

collect get corr=vech(r(Rho)) sig=vech(r(P))
collect layout (rowname#result) (roweq)
collect stars sig 0.01 "***" 0.05 "**" 0.1 "*", attach(corr) shownote
collect style cell result[corr], maximum(0.99, label("-")) nformat(%6.2f) halign(center)
collect layout (rowname#result[corr]) (roweq)
collect style header result, level(hide)
collect style cell border_block[corner row-header], border(right, pattern(nil))
collect title "Spearman's rank correlations coefficients"

collect export spear.docx, replace

[Francois Durant]

Thanks a lot Frode for helping, I really appreciate it!

I have Stata 16. collect does not seem compatible. Is there any equivalent for Stata 16? Thank you in advance!

[Frode Andre]

Does this command work in Stata 16?

label variable aaci_combined_au "AACI"
label variable aaci_temp_high_au "H.Temp."
label variable aaci_temp_low_au "L.Temp."

corr2docx aaci_combined_au aaci_temp_high_au aaci_temp_low_au aaci_rainfall_au aaci_sea_level_au aaci_cdd_au aaci_wind_au yoy_cpi yoy_ngdp yoy_int yoy_unemp using temp2.docx, replace pearson(ignore) spearman(pw) star(* 0.1 ** 0.05 *** 0.01) varlabel landscape fmt(%9.2f)

[Frode Andre]

The command in #12 can be found here:

https://ideas.repec.org/c/boc/bocode/s458383.html