Average cross-sectional correlation coefficients

[John Hanser]

Dear Community,

I have a panel dataset and am trying to create a table with average cross-sectional correlation coefficients.

So basically something like:

	c5_mktrf	c5_smb	c5_hml	c5_rmw	c5_cma	c3_mktrf	...
c5_mktrf	1
c5_smb	0.26	1
c5_hml	-0.56	0.85	1
c5_rmw	...	...	...	1
c5_cma	...	...	...	...	1
c3_mktrf	...	...	...	...	...	1
...	...	...	...	...	...	...	1

Here is an extract of my dataset:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ID date_ts) double(c5_mktrf c5_smb c5_hml c5_rmw c5_cma c3_mktrf c3_smb3 c3_hml c1_mktrf) float(OP_w log_ME log_BM log_INV)
5 359  1.9347252042414982   -.3905245497744346 -1.4906551998755013   -8.162143831022872    4.070628917994181  1.7319010888263762    .9203252330044648   2.097480836020017  1.3724168726620731  -.6076443  7.197248   .8956395 -.6176966
5 360  1.1971255400260845    .8685433735081052 -.38929896142703235  -5.9325738085560555   3.9178517402899153   .8008292806572217    2.106452365484552  2.6729202038433844    .575156660506446 -.12941177  7.197248   .6670209 -.2496547
5 361  1.3920525517839448    .6044295960256703   .6873508151369343  -3.8163306702576785    5.520081922388272  1.0606007297515792     1.39948062059428   4.296404563418569   .4667772595850316 -.12941177  7.197248   .6670209 -.2496547
5 362  1.4378165176868607    .8371652812655392   .5519792517070288  -2.7037996915147104   6.6988355543082605  1.0388489334324262   1.4137429376405024   4.186236155069077  .44072853144495616 -.12941177  7.197248   .6670209 -.2496547
5 363   1.379008194912084    .9587896185019986   .6710269010198253  -2.3162221911199747    6.376217535521352  1.0646664283550424    1.528534350251105   4.250144052949576   .5072460150310617 -.12941177  7.197248   .6670209 -.2496547
5 364  1.2867571768776913    .8368391033872099  1.1794915654766172  -3.7528442789181895    3.917027039100539  1.0932009586751477    1.524369216518606   4.256408695029719  .41838390840545453 -.12941177  7.197248   .6670209 -.2496547
5 365  1.3206026807075508    .8362458017528067  1.3199866504937807  -3.4419113253687605      4.0454653808413  1.1077243840334758   1.5039338672145661    4.24957207741499   .4277954715415384 -.12941177  7.197248   .6670209 -.2496547
5 366   .6305770013132945   1.4714167731144812  1.8605933530148597  -3.0656492750296014    .6478616946645523   .5710243840375813   1.8250297399010307   3.210055587781746  .14368844740621628 -.12941177  7.197248   .6670209 -.2496547
5 367   .6395426308274041   1.4177638026531807   2.139012152439845  -2.6716255714740473   .16543947574967888   .6113279690673934   1.7195666522711721  3.1477220736995477    .232534786876472 -.12941177  7.197248   .6670209 -.2496547
5 368   .6394007378532345   1.1301865899444192  .17773633034873448  -1.9035351247439998    5.258505928695147   .2647469029487343     1.03969904219307    2.79195941093347 -.12386629221863998 -.12941177  7.197248   .6670209 -.2496547
5 369   .9464247620057464   1.6257534800452271  -.8794117526853205   -4.284125656950484    6.056170311001235  -.1766716936948125   1.5378352499793682  2.7479655406024692  -.7222391547080472 -.12941177  7.197248   .6670209 -.2496547
5 370  1.2435730832504135   1.0824146088176727  -1.387715567007298  -5.0241309583871745    5.068163033588776   .0312151741294735   1.3216287251816792    2.40945471974017  -.3732036401719805 -.12941177  7.197248   .6670209 -.2496547
5 371   .9208517980420268    1.150991205036207  -.1220012783306017   -5.012762477007817    4.172212440265646  -.1326970335905171   1.3453737000085035  3.3316770615695206  -.6354069905349176 -.12941177  7.197248   .9580646 -.2496547
5 372 -.33083456253069743    2.326385588977386 -1.1749047216522772    -5.11037384971025   -.3018523008296352  -.6333040052800087   2.7678783895969437   .2723905185905391 -.36188463554826655  -.3096234  7.197248   .6055826 -.3842368
5 373 -.42944094482711415    2.001101758983033 -1.0041007069653407   -5.931317422396286   -.9496027178657922  -.5942201553915964    2.737433104557887   .4137199192514629  -.3075484082354494  -.3096234  7.197248   .6055826 -.3842368
5 374 -.42609098054422045   1.9904739963255516  -1.037786646537532  -6.0541685957003555  -1.0185506300260254  -.6392396569754988    3.013085063382402  .39810665554016467 -.17070392494827535  -.3096234  7.197248   .6055826 -.3842368
5 375  -.3455609877027368   2.2599626065338265  -.5864800794277911   -5.554814801153251   -.6691387018739281  -.5861963119872313    3.137123196075319   .7832201138751547  -.1839028877549853  -.3096234  7.197248   .6055826 -.3842368
5 376 -.34726705061649754    2.138441674392749  .17373702798026325   -5.112989221924888  -1.4371675882395796 -.49916885783680165   3.1184870594749707   .8310495257068862 -.12157239300981937  -.3096234  7.197248   .6055826 -.3842368
5 377 -1.2369492510434223   3.3402109089647114   1.479729947662595   -.8848723367077445   -.8833604382917546 -1.1516407273840725    3.647067442467524  1.2601960546542355  -.8708067150149874  -.3096234  7.197248   .6055826 -.3842368
6 138  1.1879083258691043   .28158635458210846  -1.289557581707129  -1.0054989302193889    .5501796704483368  1.1629565634030923    .5654333919258163  -.3013792628426275   1.433831413766143  .22785635  12.54754 -.06253362  .0786238
6 139  1.3170193281356175    .5020591256361016  -.9070582992946328   -.4541306325667165    .7459696979454478  1.2537076788753234     .629424131447196 -.05178836053219138   1.500364555719282  .22785635 12.689142 -.06253362  .0786238
6 140  1.3733469126956557    .5215689560517571 -1.4733099022200165   -.6353325718588732   1.2222749684701597   1.260508782532244    .6864702735112271  -.1397848880722643    1.53674066803387  .22785635 12.682628 -.06253362  .0786238
6 141  1.2802328614977871    .4900939465290519 -1.2126746857904038   -.5993697736708166     .750930951798118  1.2208602943745133    .6563175669237716  -.2745061552710957  1.5060545872045554  .22785635 12.564844 -.06253362  .0786238
6 142  1.3023622890032907    .3905914302394362 -1.8684193072544772    -.624290522570786    1.421327628610122  1.2034230382180937    .5390417987620585 -.38188036522682123  1.4588035282646112  .22785635 12.522285 -.06253362  .0786238
6 143   1.417674166010463   .26610927310015153 -1.2513433638806433   -.2668547700239077    .8481966269268284  1.3599403148605904    .2939118079953027  -.4325071752124665  1.5374816000225113  .22785635 12.571568 -.18062685  .0786238
6 144  1.3304403625508043    .6219963909003132  -.5245240462441956  -.05359584853367405    .3758056538443526  1.3011856945963285     .616655744270255  -.1957139089558413   1.580359821085393  .20153306  12.57641 -.12851967 .05554624
6 145  1.4368931574975452    .7635560177644454 -1.1220325100050275   .35172430648726616    1.222080671453405  1.3250086246510604    .5953589509219697  -.3559647527342813  1.6153746527997774  .20153306 12.556897 -.12851967 .05554624
6 146  1.4675144631956707    .7459392565891161  -1.396679539865417   .32927192662804083   1.4727228359933306  1.3426335584875497    .5565796199315078  -.4374381356781153  1.6311346487673024  .20153306 12.485438 -.12851967 .05554624
6 147  1.4829743712915917   .40429254978640605 -1.0941828839260026  -.33604128760523166    .6543093640948356  1.4374258021939752    .4446517077008989  -.4179849221533243   1.678879984792687  .20153306 12.414176 -.12851967 .05554624
6 148  1.5426498978729137   .20582848675408805  -.9595255153998821  -.11875359576067601    .3183796662046845  1.5123854791948828   .23291713612015538  -.6490297827465097  1.7015521987405395  .20153306 12.425475 -.12851967 .05554624
6 149   1.380085405059961   .29176377158141564 -.39289364072667254    .8539868560114385  -.16269133447111983  1.3671409983961065   .16693123250799757  -.9086863139712852  1.5719139886770717  .20153306  12.39987 -.12851967 .05554624
6 150   1.206422391356051   .23448883031214174   1.137400465352929   1.8094000124898662  -1.5838396026103538  1.3415350077868786  .014399736830140076  -.8720738470709817  1.4795062530169798  .20153306 12.431077 -.12851967 .05554624
6 151  1.4005527277474519   -.5533449645478989  1.1904471052749912   .12890086904006903   -2.538443492463145  1.6490826806410186   -.3247401998319218  -.6301909319246177  1.6027379728926288  .20153306 12.524354 -.12851967 .05554624
6 152  1.2615281514190704 -.025833528262153127   1.953590456844415   2.0470832724182078   -2.548480796367535  1.5110812126697677   -.3229918396593724  -.8752372557745296   1.502675742398813  .20153306 12.566702 -.12851967 .05554624
6 153  1.3412878868907407   -.1777249084571304  1.0675947796504772    .9580829380223015   -2.262311722853241  1.5831608486350806  -.24870658580359772 -1.0385690459804442  1.6082551704609862  .20153306 12.588415 -.12851967 .05554624
6 154  1.2767702335573632   -.2121877311082998   .8711710204357885    .8943561223178683  -2.2252473180994414   1.504245030783597    -.275537095427709  -1.197648779680125   1.519000885999061  .20153306  12.51155 -.12851967 .05554624
6 155  1.1727820369288393   -.9697181254007474  -.1927437105593306   -.8169902019967998  -2.6500128066576814  1.4418166755593975   -.6029483264281277 -1.6864162804513998  1.4243932608044914  .20153306  12.49495 -.05190247 .05554624
6 156  1.1089972393897836   -.8134848010797802  -.6242612273752925  -1.1175320411547194  -2.1705302185943367  1.2594411313001488  -.32966920219073215 -1.5365969532628427  1.2656109916168132   .2052562 12.432684 .012503685 .01870639
6 157  1.5048778360760684  -1.3061357505805882  -1.338223683766909   -2.405874430554455  -1.6963200559710465  1.5358631663709594    -.518061868102208 -1.2712316532045742   1.463279566334285   .2052562 12.339048 .012503685 .01870639
6 158  1.5015723534906322  -1.3036629488652007 -1.2643583271633414  -2.4704852581941426  -1.5892222842529513  1.5186194017123718     -.50557021467348 -1.0176264294434496  1.4146916929896998   .2052562 12.443795 .012503685 .01870639
6 159  1.6234740144611686  -1.4225146360708765 -1.5199801718666288  -2.7185583718036623  -1.6468413248936837   1.564769132437048    -.528307406781815  -1.082055999296637  1.4076464844534307   .2052562  12.44103 .012503685 .01870639
6 160    1.36872982101197  -1.3244067060281115 -2.5832873604629985   -4.048541540348051    -.378196582104702  1.2804229087660324  -.23967680689698667  -.7147361350096055  1.1916125288037693   .2052562 12.415782 .012503685 .01870639
6 161  1.1172323001433748  -1.1204782856063282  -3.244674673546328   -4.225483890262443    .6680969622215924   1.075321366836248  -.10581193371275088  -.6672381084700925  1.0473726803300591   .2052562 12.449306 .012503685 .01870639
6 162  1.6059318617824832  -1.2436217852308844 -1.7890247985359542  -1.8784327967039625   1.1389428784552464  1.5981788685671885   -.8215592656405221  -.3115766326346014  1.1140749430190968   .2052562 12.505395 .012503685 .01870639
6 163   2.095641309826343   -.2832462447432318   .9382688464739726   2.5600696332593396   .12157646525202193   2.191637139832948   -1.036515547006256 -.41832900756525715  1.6003823704242448   .2052562 12.392793 .012503685 .01870639
6 164  1.9473157905276923   -.2654810518501755  1.3694942373241679     3.15708269376468   -.8755953421358038   2.021968406645757   -1.114668158184788   -.900694221105506  1.4364416598935654   .2052562 12.476407 .012503685 .01870639
6 165  1.9918265449178938  -.08065572848507749  1.9334301984259405    4.545421881619591   -.8430715102046773  2.1611913618575054   -1.385529826309859 -1.1458140008506117  1.4641286008079686   .2052562  12.48174 .012503685 .01870639
6 166  2.2790232961753985  -.34604550392249034  2.0751921340075565    5.226602698622758   -.3152836736725533  2.6093092928093107  -1.7353724314525458 -1.2461976662888659  1.7496979165928868   .2052562 12.307894 .012503685 .01870639
6 167   2.797753826271075   -1.120252241660685   1.654281332672019   3.8130751526422677   -.2856057354457029  2.9055666178788777    -2.00980222772058 -1.0100061745619322  1.8773663151899183   .2052562 12.689878   -.182424 .01870639
6 168  2.9203020153210653  -1.6338181872416087  1.1216477725795126   3.5678382917155935 .0010501614548665827  2.8550891490279997  -2.0795310995967653 -1.0213842934829938  1.9067178612660611   .2187769  12.67902 -.09550706 .13626105
6 169   2.936810745628131  -1.6371599943866946     1.5397535034773   3.9089085228542326  -.49953935434375296   2.883583054594026   -2.185267367270919 -1.1201666668668713   1.903255461169253   .2187769 12.625196 -.09550706 .13626105
6 170   3.010353384782666  -1.9872852769116527  1.2101331071113766    2.744859971701415   -.6147895171040432   2.886122515418269  -2.2714453078058416   -.936129536101193  1.8623717667923827   .2187769 12.582836 -.09550706 .13626105
6 171    3.04141083065442   -2.106135805464727  1.2732407436823223   1.1425350195787014   -2.223960127962183   3.053041524845413   -2.402489149362731  -.9558075004262276  1.9970699385764803   .2187769 12.460234 -.09550706 .13626105
6 172   3.283488564875093  -2.0785509336177626  2.1289785136983905  -.30715493825154594   -4.040431187011759  3.4882901402508644   -2.416372183748458  -.3343679899676699  2.3238944278530207   .2187769 12.369263 -.09550706 .13626105
6 173  3.2555130007296587  -2.0832009764181483  1.8062875861747436  -.27837817427436695  -3.4166642282951507  3.4840212803894324  -2.4040837524039205  -.2883359550380282   2.333175844723216   .2187769 12.415782 -.09550706 .13626105
6 174  3.4394935443810524   -2.099429146573466  2.3365629881741867 -.005198736728298847  -4.0153250833135345   3.705572931440578  -2.5563096329806116  -.3675165076003388  2.6589068598103704   .2187769  12.23573 -.09550706 .13626105
6 175   3.224987411931663  -1.9900872353044594  2.2832516250575594  -.12258148176269712   -4.166162049830753  3.6173734605334964  -2.5122503903378055 -.38138753730896546   2.697687832990277   .2187769  12.04932 -.09550706 .13626105
6 176  2.9741501645304496  -1.9739114228740853  1.8124399109188758   -.8624046029392043   -3.574916757173362  3.4488913283626017   -2.394849163236061 -.07254457964453387  2.7013202814281794   .2187769  12.08158 -.09550706 .13626105
6 177   2.590408375105124  -1.2128156458608834    4.18696198379614    .6994911467296685   -5.271024700803046  2.8092345752343664    -1.78348756146528   .9502192174521156  2.1609339489221635   .2187769 12.222033 -.09550706 .13626105
6 178   2.309639600458244  -1.2545796665666447  3.1146921857472982   -.5268825673696345   -4.695918395947082  2.7300407423949857  -1.7352835320353366   .8872687924380269   2.120175612282971   .2187769 12.286168 -.09550706 .13626105
6 179   2.409922744355609  -1.0014048178378512  3.7605652547580117   -.6661855276945055   -5.901389712929642  2.8372417718517564  -1.4770295990616122  1.0790294174320465  2.2177351494386235   .2187769 12.132017   .4623542 .13626105
6 180  1.9396674259682296   -1.689427318158712   2.217885973039924   -.8208929945019783   -4.278354935413887   2.597147838545899   -1.930647620223656   .6998272230885014   1.948227497268614    .222379 12.339893   .5624761 .15061657
6 181   1.876658409609787   -1.810660729896809  2.4497490721050195  -1.2500040328642896  -4.6845074213747395   2.607647206730104  -2.0473183322840596    .877320595776439  1.8966593386597173    .222379 12.385014   .5624761 .15061657
6 182  1.8578174082581613   -1.871722388263046  2.5632459035075046  -1.5184833652267287   -4.839421669927091  2.6328568773769536  -2.0518252498579614  1.0226414507399495  1.8772483205477646    .222379 12.352125   .5624761 .15061657
6 183   1.860788297975989  -1.8910386063207436  2.5525740568715394  -1.4411719985993443  -4.5766005547294455  2.5988124028877575  -2.0710246783725115  1.0862272888006101   1.830537803549648    .222379 12.305352   .5624761 .15061657
6 184  1.8302527973585976   -1.599572685932853  2.0413193763335773  -2.1621718199746134   -4.507159638629965  2.5673066575082815  -1.7713287885740776   .9066206328231567  1.8689787879489008    .222379 12.457658   .5624761 .15061657
6 185   1.958876030675376  -1.7746449754647189  2.4168574145674127  -2.3276789411247414   -4.578734445458494   2.721907287198306  -1.9650310275709026  1.2790165409749905  1.8840418356707558    .222379  12.40651   .5624761 .15061657
6 186   1.733050934680425  -1.4129687478348671  2.1599211711431447  -2.3563716708517894   -4.496436665052563   2.462790688248348  -1.5703622227321552  1.1192906778654583  1.8123156165220937    .222379 12.412324   .5624761 .15061657
6 187  1.4145207250629568  -1.0742357270343614  1.8262564983668437    -2.55064432623994    -5.03295317722209  2.2739876373726053   -1.336149213812086   .8152799207050823   1.717891504146923    .222379  12.34022   .5624761 .15061657
6 188  1.4603750685080499   -1.021893367688063  1.6462502772973284  -2.5567278108845457  -4.5335364713027175  2.2327401728648226  -1.2219236181910755   .7985022335544159  1.7196719573030297    .222379  12.27589   .5624761 .15061657
6 189  1.3704799581076479   -.9078664992493186   1.562206056367387  -2.6562895119009298  -4.6326995599833865  2.0398497324048743   -.9484099347180388   .6508093897305507  1.6566737080758873    .222379 12.292365   .5624761 .15061657
6 190  1.3980681564772288   -.9293911651573538  1.5674557051726872   -2.583308314078085   -4.569856793360415  2.1116131652390093  -1.0142325226570077   .7520089683080347  1.6735875942812166    .222379  12.29888   .5624761 .15061657
6 191  1.4239567368067156   -.9262883925618548  1.5906379968494182  -2.6711474348303597   -4.393124088180279   2.118409912974413   -1.003587176952124   .8825406472045234  1.6532198243111773    .222379  12.29888   .3956134 .15061657
6 192  1.5288740033766874   -.9184026252820374  1.6832032799929113   -2.628329035047546   -4.247949150955115  2.1182415097872584   -.9663543964766959   .8609839078813266    1.73583340439226    .240826 12.490358   .4877871 .02322312
6 193  1.5763532448842232   -.6043289433682013  1.5598142641178985   -2.602532956557482  -3.5957077056367055   2.089170389754442   -.6267420862401152   .9706463268259533  1.7880355347973218    .240826  12.51158   .4877871 .02322312
6 194  1.6446213241657013   -.5468834026132015  1.3410777971641394   -2.435526242366297   -3.130612869536378   2.085015701332353   -.5398349193628142   .8531665051482085  1.8252577022378784    .240826 12.547666   .4877871 .02322312
6 195   1.583132294134085   -.4760635176672078   1.298570940472187    -2.50361800985241   -3.439089362210254   2.049344212985549  -.43251486210180357   .6869470250105509  1.8557187959632686    .240826   12.5747   .4877871 .02322312
6 196  1.5288050939091429   -.4956771673951401  1.3822737815967834   -2.248372837033287   -3.745185337468186   2.013734311525419  -.36971529994021557   .5650872838702119  1.8544952134159614    .240826 12.544242   .4877871 .02322312
6 197  1.6078046874786214   -.5495809412291979   1.380947534480681   -2.181363845536719  -3.5803024570783535  2.0653204035104915  -.39974534370207193   .5786221794299848  1.9016897829105133    .240826  12.64824   .4877871 .02322312
6 198  1.3765862092204069   .06716515291272177   .6757391172497353   -2.970627778981253   -4.428887342293791   2.002134248774217  -.09832312557838829   .2706775168531781  1.9539362227582702    .240826 12.594562   .4877871 .02322312
6 199  1.2555499643599388 -.023708122251042185    .756992663212797   -3.397100786993775   -4.975338712360582  1.9793380198881438   -.2391478123691062   .3123684095665187    1.89810167619832    .240826  12.58347   .4877871 .02322312
6 200  1.3037014273372725   .07746629525466142   .6478392876079759  -3.6121419220691138   -4.938601313531606  2.0395872774676036   -.1825082794725219  .31289653119434035  1.9689221027784283    .240826 12.623549   .4877871 .02322312
6 201  1.3250151272869264   .05890423466809246   .6072741077246544    -3.66593111462171   -4.773294021973875  2.0190638698242482  -.17167143622193937   .2949357427532693  1.9535191307220552    .240826 12.533937   .4877871 .02322312
6 202  1.1136874828979089   -.3293379245328829  1.1437672636184022   -4.300542861201283   -5.841337746546259  2.0237278071992493 -.009492237438518458   .1919453852102908  2.0045904376597536    .240826 12.530023   .4877871 .02322312
6 203  1.2188889798755627    .9273962081116984   -.308517402653667  -3.1492658139092207   -4.169528707717895  1.7794153879414876    1.240085451156964  -.8489229221060786   2.036212911562704    .240826 12.627113  .15955307 .02322312
6 204  1.1416778169539406   1.9556265055160935 -1.1125461779147194   -3.016208256682532  -3.1780950959076577  1.5312611869786745   2.3253072304631255 -1.1209598502462903  1.9503700802300137   .2386904  12.64125   .2467319 .04977695
6 205  1.1001601444182647   1.9037206354516716 -1.0505044518845494   -3.236910356088213  -3.3621125082881917  1.4999042965512748   2.3534742229276233  -1.126991738633924  1.9174870043045025   .2386904  12.60553   .2467319 .04977695
6 206  1.0534803441103273   2.0234482751979517 -1.1366507279438771    -3.13655358430508   -3.401220693494764   1.387636842276143    2.666130354401508 -1.3754248193310161   1.886699654473797   .2386904  12.60916   .2467319 .04977695
6 207   1.058083030334788   1.8255396558993038  -.8392901757063846  -2.4992906770383136  -3.1157911751833183  1.3189021744198914   2.4054018884540547 -1.1293648711960742  1.7572282157246182   .2386904  12.65864   .2467319 .04977695
6 208  1.0485805586251402   1.9121109045817466  -.9668743063829517  -.41177061324619757   -2.082068343838827     1.1738135639033   2.3874188994858754 -1.3723632889011697  1.6219075320952345   .2386904 12.662083   .2467319 .04977695
6 209  1.0358302751348019   1.8288071303315157  -.8837091927403549    -.576130681000727  -2.1511609916710768  1.1537414826164867   2.3216121295512098 -1.3187985346349826  1.6006215388697531   .2386904 12.653102   .2467319 .04977695
6 210   .9131390007175071   1.7750735163242632  -.8023456864819707  -1.2794937292352024  -1.9605686909223863  1.0084720745616929    2.169795412069238  -.9721963630639365    1.38218722170072   .2386904 12.617763   .2467319 .04977695
6 211   .8266216139336997    1.689479217236288  -.6729718625264969   -.8676189178663449  -1.6146666419172817   .8994848415873896    2.002443399660101  -.7749212481124399  1.2128513123694955   .2386904   12.5708   .2467319 .04977695
6 212   .5705409661031609    1.724510797423504  -.7747655939433091    -1.86196974295952  -1.2392904259732873   .6381503918999978   1.9093222373221064  -.5077864736992068   .9186942095765436   .2386904 12.626061   .2467319 .04977695
6 213   .6936890220971519   1.7513245825829427  -.9643378121072015  -1.6801133003974016    -.884252545222175   .7239121584296924    1.879535044261761  -.5757531257544164   1.122640263609006   .2386904 12.589561   .2467319 .04977695
6 214   .6369537987311716    1.546165637764617  -.6561838657533681  -1.8053114578310971  -1.2978802549458184   .7067655968956823   1.7221124614588201 -.41847716220419695  1.1014041337607374   .2386904 12.593272   .2467319 .04977695
6 215   .6214766180965268   1.0387879833757925  -.7956675710406618  -1.0649733222442026   1.0460167899304231   .6244869327253072    .8882225600036717  .04203295412698438   .8489808667956547   .2386904 12.615595  .25825113 .04977695
6 216  1.0480423964532515   1.3346544044763213  -.3823011454041012  -.41812556343912544  .062268991857203915  1.0518977855583813    1.277491035162178  .02937466116334253  1.1157734336096428  .22985804 12.484132   .3447957 .08257484
6 217  1.0531983886587792   1.2191483177238396 -.21821211705614108   -.5752713059802144  -.18347415283426827  1.0581944968054284    1.154781924646533   .1328379557083892  1.1285690993984183  .22985804 12.450515   .3447957 .08257484
6 218  1.0699470177899209   1.1821895209811848    .343979502743176    -.499739579787704  -1.3505488772268883   1.030964109503747   1.0774551125797998  .21727263790408635  1.1029178435447702  .22985804 12.488256   .3447957 .08257484
end
format %tm date_ts

I am a beginner and don't really know how to tackle this.
I tried the pwcorr function with grouping on the time variable. But I did not find a good way to do it for all months and then average it.
Is there an easy way to accomplish this?

Best regards
John

[Clyde Schechter]

It is not clear what you want to do here. Do you want to calculate the correlations of these variables among the different panels separately for each month and then average across the months? Or do you want to calculate the correlations of these variables among the different months separately for each panel and then average across the panels?

[John Hanser]

The first! Calculate cross-sectional correlation coefficients for each month (each ID is another firm in my case), and then calculate the time-series average.

[Clyde Schechter]

Unfortunately, your example data is not suitable for this. You have provided two ID's but there is no overlap of their dates, so that when you try to do a correlation within a date, there is only one observation for any date, so no correlations are calculable. Please provide an example with at least 2 forms and where at least some of the dates overlap.

[John Hanser]

True, sorry.
This extract should be better:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ID date_ts) double(c5_mktrf c5_smb c5_hml c5_rmw c5_cma c3_mktrf c3_smb3 c3_hml c1_mktrf) float(OP_w log_ME log_BM log_INV)
 2 518  -.6594277566392851   .05445094242234677   -1.077858951724136    -.6806547372405378    .3934844991135229  -.35060817080290607   .23986541400841066  -1.0643549264277183     .268177445555968   .177662  9.895276  .08281849   -.0746393
 2 519   -.816827319182983 -.059856149045377005   -1.075107352802319    -.6671493608196993    .3429487788723353  -.49725482208050775   .14368907151868116   -1.106208205326498  .044749473209862556   .177662  9.503444  .08281849   -.0746393
 2 520 -1.0701099836391945  -.20708634475456672    -.717444162894207   -2.1019536912819157   1.0766906744340865  .016025658950552968    .3712397820982991   -.8199320061102071    .4661251510490124   .177662  9.993938  .08281849   -.0746393
 2 521 -1.0638513434727304    .5469000324589408   -1.664144504556238   -1.8554267151428205   1.6397368008252222   -.1201503519279512   1.1086553124371468  -1.1321482160623288    .5098260601876486   .177662  9.654766  .08281849   -.0746393
 2 522 -1.0429674246993836    .5242384437654679   -1.491087483369891   -1.8954153001486331   1.5452163741417095 -.048295100944089064    1.144510261369622   -.9730097158354991    .5265940128850879   .177662  9.672904  .08281849   -.0746393
 2 523  -.8707605327888202    .2889160812563596  -1.2504663526234037   -2.1459010898107973    1.548474611127883    .2542822077544691   1.0448681000084168   -.8476407493974674    .7943256643849086   .177662  9.755959  .08281849   -.0746393
 2 524 -1.1354596333360267   .06907083149875537  -1.0438674723199837   -2.5433146153273363   1.6466571245607708   .21316825453884136    .9854142802655839   -.8178396299638147    .7128754956314696   .177662  9.792863  .08281849   -.0746393
 2 525   -.860988123085949   .03365089114233044  -1.1444267158052073   -2.1137677686192284   1.7414389682427434   .24931272052025677    .7094032618453386    -.696546456780359    .6297433666330817   .177662  9.653101  .08281849   -.0746393
 2 526  -.7773414386266049  .051228722426810036   -1.056836826005369   -1.9441049959858874   1.7946026514152944    .2206129172077662    .6809342053785858  -.44780665122665786   .45869987703948356   .177662  9.648088  .08281849   -.0746393
 2 527 -.46495504204296045  -.02406774640297804  -.49951676137511325   -1.8857790247871544   1.8100768022908111   .48830025609715033    .5809198698245798     .091704519008456    .5369865284390679   .177662  9.645119   .2923358   -.0746393
 2 528  -.7590385509442364     .352905297154263   -.2754460207100755   -2.3252532312051337    .7466557160390765    .4638115683509141   1.1079549991239255  -.33281886001529637    .6855174028452262 .09179211  9.655152   .3125259    .0759443
 2 529  -.5720943623828041    .3376754156495048    .1916292375336488   -1.9688302516492133    .6566005805811441   .45752815785103323       .9791709480483   .13228292637305605    .5370519592638734 .09179211   9.73199   .3125259    .0759443
 2 530 -1.0350847242126342   .36886837325759775    .9999795263009603   -2.4509992541551515      .27429725634875   .30271110821357017    1.052347338922903     .502750044634501    .3433070133569227 .09179211  9.843498   .3125259    .0759443
 2 531  -.6753432288152006     .236788522744518   .47030446315245755   -2.0829142483512753  -.12062353159075023    .5101694438650821    .7952930758407566   -.1892052496299994     .625640783399696 .09179211  9.831007   .3125259    .0759443
 2 532  -.6618176482822845   .32027147396855543    .6030308199616317   -2.0783764143664287  -.12229867994737287      .52473503187279    .8490155072273599 -.059035475369381585    .6326774804949536 .09179211  9.748051   .3125259    .0759443
 2 533  -.6101730780392948    .4578612432182476    .6090321728203103   -1.9784804020958215  -.23393721657726996    .5006043133036017    .9649933814897531  -.07718124288872841    .6388783837156035 .09179211  9.759002   .3125259    .0759443
 2 534  -.6255067238920355   .28231687664763583   1.0147478711564295   -1.9710635135416363  -.48162435325107533   .48451352420674554     .685902862035111   .31195229465536123    .5527418549009201 .09179211  9.808792   .3125259    .0759443
 2 535   -.729376232347035   .35809743272086336    .9235112666906642    -1.896730780832791 -.028705923257644872   .34789015821404284    .7959279678837049   .44079501847035785   .44179057486245843 .09179211  9.813068   .3125259    .0759443
 2 536 -1.0977351176856183  -.21673411120230113    .8996651548027845   -2.1097510353229487    .5923477742591541   .21143700215188027    .5054126654321143    .4709531796448711   .22049507896608028 .09179211  9.671171   .3125259    .0759443
 2 537 -1.0020780018173283    .1347782329131826     .365511674795139   -1.9344210362109897    .3434693838487243   .16008176724350043    .9659643328704941   -.4232042596315526   .25461248834044553 .09179211  9.613819   .3125259    .0759443
 2 538  -.9600754080105114  .039493939867665626    .5997693061395606     -2.05784399220207    .1625129836174537   .20934931453105815    .8806235767208399   -.3626450704761581   .33099250949201764 .09179211  9.644591   .3125259    .0759443
 2 539  -.8863136504081155   .05222099216077969    .5764890127689746   -2.0322968223746445   .11329656136968991    .2666509222649872     .926189955482837   -.3787626732415068    .3847438453949824 .09179211  9.794403  .16324078    .0759443
 2 540   -.848306305323352   .11745930966452874     .449634602311745   -1.9847684481155956   .19205932715508722   .27881589049337707    .9757087923835845   -.3795348003734731   .41201851963670644  .0559569  9.751437 .018283758 -.018460508
 2 541  -1.031914102967818   .14918688110505698    .4792034476839251    -2.370140186988881  -.16291001634210017   .24033937520696402   1.0005781885544638  -.40833270747009354   .36439883446698224  .0559569  9.706759 .018283758 -.018460508
 3 518  -.5861513242319291    .7312464537431488  -1.3289418905878083    .25144871207094033    .2913310560051201   -.7125826409849143    .6647664708900154   -.8845772621559558  -.07533138286607843 .20092946 11.742893  -.1803327    .1168695
 3 519  -.4207942513571583    .4591176879961159   -.8446157524311477   -.18567120371984436    .2529415917525082   -.3331036763265418    .5018617268539436   -.6404295913180964   .06876593514512486 .20092946 11.842048  -.1803327    .1168695
 3 520  -.5818590775361532   .17155431106867625   -.6709853953344913    -.5048336299032874   .15054402677587575    -.314922745782799    .2814012690972454   -.7100023311016144   .06641393988543132 .20092946  11.81984  -.1803327    .1168695
 3 521  -.4812033702042062    .8320654894916679  -1.3524956772096992   -.23033073508002677    .4182467917061139   -.3630742561765157    .9413487602333468   -.9534822479121825    .1686361266216725 .20092946 11.851997  -.1803327    .1168695
 3 522 -.43942730615946524    .7836581571435226  -1.0173284643693616   -.30131827384850096   .23024635650711645   -.2785512735707216    .9437719373623698   -.7535985943979817   .17576503483607842 .20092946 11.851375  -.1803327    .1168695
 3 523 -.21491447661912189    .5059895380328068   -.8114947277238649    -.4999912670352613   .24264658653306448   .04387194071818956    .7485902033088556    -.670106212214578    .4563687005997379 .20092946 11.853978  -.1803327    .1168695
 3 524  -.7620819166689371   .08237251437513204  -.31235091621477196   -1.3772533982611836    .5712520124499889  -.03245983306164868    .6053298820733206  -.37724903147592004   .22517094120151304 .20092946 11.790818  -.1803327    .1168695
 3 525 -.38316824843591046   .09691865828053547   -.3402373749661418    -.9516215998907982    .6486544925416116   .11126022061396246   .45833057481577055   -.1942304721764132    .2628955198886706 .20092946 11.812437  -.1803327    .1168695
 3 526 -.16930548609661528   .13648696428689894   -.1711997780213821   -.39205726093746984    .8352517232940349  .017762475079386775    .3696179485666989   .29258952959362783 -.013513295748693116 .20092946  11.83498  -.1803327    .1168695
 3 527  .16192870779757584   .11432995603115498   .41367843928387416     -.315549318350386    .8878597087205351    .3053616170856523   .29552510820579897     .909049323242425   .07432384414342783 .20092946 11.850076  -.5490569    .1168695
 3 528  -.0698186370706615    .3759422660937906    .6004114349585263    -.6927218906199007   .14148718920924397   .28814304269993146    .6619745124334219    .6134058915212599   .19618072890873794 .12633502 11.855678 -.18300496    .4808375
 3 529  .08438631242170697     .402604652181851   1.2905978763497639    -.3264338932548112    .2125888282606192   .24943135773806802     .538901879493648   1.4184293191869621   .05672808346793445 .12633502  11.97041 -.18300496    .4808375
 3 530  .19175963394429346   .42536125454271784   1.1010711400645496     -.190835869824589   .21862799204954375   .28884139962146455    .5286065304156102    1.258260563116483     .172607482571777 .12633502 12.164367 -.18300496    .4808375
 3 531   .9157342280958747    .2683091988194366     .226190233109959    .41282666208087104  -.38561481250425844     .667840907391303   .18309734782623552   .23025395001631033    .6703744049041107 .12633502 12.127088 -.18300496    .4808375
 3 532   .9043910204208063   .37279528482133856    .5716095164257884     .3080490379724934   -.4657479861710252    .7174070849501037   .28357262596677735    .5210654053352254    .7038867917207255 .12633502 12.142984 -.18300496    .4808375
 3 533   .9404140863685251    .7255547503283759     .461242471188547    .49911958754258345     -.66367543197422     .665027835810094   .48348160484989217    .4695649846132395    .6869160560969249 .12633502  12.16617 -.18300496    .4808375
 3 534  1.2523978184594142    .5239937599366099    .8160305963257133     1.008492316995191   -.4982810378563971    .6885632739240389    .1439459508146025    .9998680599432627    .6126660023437689 .12633502 12.170592 -.18300496    .4808375
 3 535  1.0975644105507345    .6589898455968186     .646574899624055     1.132405627220828    .2274104776624013   .47134516145259814   .34189560168860056   1.1919965417611418     .426812366849566 .12633502  12.19309 -.18300496    .4808375
 3 536   .7036788431628432    .2157587998126206    .6974606808292286     .8365883028195042    .5540493571082573     .255580700913168 -.004061726720843317   1.1894639363315322   .15974042638493147 .12633502 12.238416 -.18300496    .4808375
 3 537   .8216520424868339    .6074118688479045   .07137446317935037    1.0454407072901035    .2811708612537388   .23099120696016945   .23202500833214387    .7122505241965827    .2071668749573447 .12633502 12.250907 -.18300496    .4808375
 3 538   .8580950663770417    .5877408661539578   .17948172225468645    1.0045267271384366   .18090644196541927     .317546827958734   .19712587794734687    .8239447454144687    .2833127282339817 .12633502 12.365175 -.18300496    .4808375
 3 539  1.0040819584872864    .8322011026308325   .07424111207598261    1.1630205890639334  .015486607325947643   .35942291368487705    .2866690165458073    .8148203272284155   .32982904754233433 .12633502  12.51071  -.8436403    .4808375
 3 540  1.1741745748145738   1.0120599738909184   -.1921231628499519    1.4592627745843725    .4310440601506438   .40981409085017656     .473705546060708    .7309554618198151   .41101155095532677 .20186116  12.36861  -.7974355   .10089315
 3 541  1.2211247600456494   1.0623149082082444   -.4106571372441135    1.7396646725916425    .8525826647929718   .37657864220759546    .5954979914246795    .5251250624546279   .40496995721387996 .20186116 12.334788  -.7974355   .10089315
 3 542  1.5317360542930085   1.5475412212984376   -.6341202008569702      2.06903361654959     .635421103286572   .48359534101228335    .8910523120403971    .4679476783542297    .5306771085093952 .20186116  12.32434  -.7974355   .10089315
 3 543    1.58086062788477   1.5223622493226558   -.6532311674169562    1.7874855656615507  .024720630759698548    .6672794972551322    .7814772302757155   .23053275312328236     .784297608140435 .20186116  12.25135  -.7974355   .10089315
12 518   .6951112751778548   2.7939554142615286  -3.0866648486148343    -.4554563763861963  -.42871825881933034    .9525020510083984   2.8369532662023644  -2.7697408916698087    3.147457716296923 .22271118  9.951706  -.4659322 -.019581215
12 519   .8220104099629413   2.7676944458544943   -2.941782145066764    -.5940367939926805   -.4155949786615203   1.1627860979599145    2.843357948978398   -2.666342040543933   2.9945270366063914 .22271118 10.037884  -.4659322 -.019581215
12 520   .6904562938569727    2.598224850793624  -2.7818170217643967   -1.0547926698466004  -.30733884476916984   1.2902258006599183    2.818632697241534  -2.6235962511143898   3.0730375214606247 .22271118  10.29691  -.4659322 -.019581215
12 521   .6591129173021666   2.3762629286648456  -2.5489441026398367   -1.1455118343542097   -.4031799227013195   1.2911999050869856    2.707855212360802  -2.5861914978566904   2.7566527066860003 .22271118 10.134933  -.4659322 -.019581215
12 522   .6461856545719429   2.3825213420004863   -2.683895592851199   -1.0980075328109833  -.34194867778892046   1.2539840907201216    2.687790658805108   -2.668136708816345    2.759196238902415 .22271118 10.109614  -.4659322 -.019581215
12 523  1.2622489118150302   1.6927899241192192  -2.3864218975085265   -1.3262139898651348  -.28771914427216316   1.9866241093217933   2.0569940217434217  -2.5838392013170086    3.422509290203173 .22271118 10.093562  -.4659322 -.019581215
12 524  1.0304047924120217   1.4891918586407087  -2.2312764040831787   -1.6543339513826933  -.24671676029330314    1.933146281308972   1.9847800393651243  -2.5820570384538684     3.30815939381816 .22271118 10.208443  -.4659322 -.019581215
12 525  1.8692694188486618   1.4915529690180982   -2.345194937955897    -.6333175628854057 -.050218681925002784   2.2120318423997225   1.6500818359601175  -2.2113438388094417    3.314516661403602 .22271118 10.430035  -.4659322 -.019581215
12 526   2.028746140348968    1.525966589100563   -2.168998324347883   -.33092661515230537  .042648469455306426    2.196420939854592   1.6317035908778519  -1.8440078034321727    2.997954905051854 .22271118 10.585073  -.4659322 -.019581215
12 527   2.417740542509758   1.7879699381141765   -1.512427374127106   -.16786160309813414    .2851875286862544   2.4939973898052523    1.779753408863915   -.9581678951698507   3.0107063341003615 .22271118 10.454576  -.6467184 -.019581215
12 528   2.250652864349174   1.8985644928307126  -1.3554398405283283     -.507697252905456 -.051271842096600295   2.5125085855614477   1.9723537449426285  -1.0457943199129183   3.0324352972221056 .09732015 10.547127  -.3942127   .18792105
12 529   2.122799289509387    2.003835782009266    -.937537025742587    -.5766581071619543     .361894149141361   2.4163502872200446    2.116792617614248  -.42973646835393486   2.7140399522524326 .09732015 10.263887  -.3942127   .18792105
12 530  1.6074842951695196    1.879578935513981 -.026911306651628664   -1.2393564914453563    .3743485142653915   2.2822369000313874    2.139976061752068   .20900532902350788    2.475281917586331 .09732015  9.963782  -.3942127   .18792105
12 531   2.020428539781826   1.6790497882749984   -.7208645469256196    -.7551695253685966  -.16467272605375705   2.4524430636587713   1.7546593194709847    -.690553760773363   2.7311972244358946 .09732015 10.031676  -.3942127   .18792105
12 532  2.1020728871314116    1.993279540163171  -.41620049243275625    -.6180112667601106  -.08614446605787851   2.4589539814137487   1.9800388006846816  -.26027619334617347   2.7219311934741532 .09732015  9.836323  -.3942127   .18792105
12 533  1.9281104794762394    .7963983493047122   -.1092388519550711   -1.3012137762466964    .6328029004785427   2.6532367729133184   1.2101238718058944   -.0758940182812693   2.8246874785058904 .09732015  9.659064  -.3942127   .18792105
12 534  1.9149639522853399    .9927175577037807    -.552761091295126   -1.3555455370763243    .8684593830626934   2.6807182080379155    1.432082433676622  -.36599056197427826    2.921391425105231 .09732015 9.1829815  -.3942127   .18792105
12 535    1.74907846511217    .9946122597218365   -.5175942624879046   -1.3077096806577495   1.3184967826001022    2.547405085334251    1.443530305572925  -.18727652291879648    2.801227080085198 .09732015  9.298051  -.3942127   .18792105
12 536  1.4953344100261543    .7151132194060736  -.48212487605375387   -1.5006719589585074   1.5170432127361386     2.51772693523456    1.394787179305554  -.18711252323035077     2.66205190646847 .09732015 9.3406105  -.3942127   .18792105
12 537  1.5043268479556267    .9673279716952559   -.7034329165767186   -1.4455497166516058   1.3153278503406305    2.447990473240325   1.8806199434365245   -.9613568819801581    2.639887453877294 .09732015  9.106996  -.3942127   .18792105
12 538   1.897972744423626     1.10556287288615  -.06533673571051352   -1.5191213603574982    .5481642453091686   2.7984893456823623   1.8561050975981632    -.503678948358953   3.0371772758267555 .09732015  9.420653  -.3942127   .18792105
12 539  1.8999798439829079    -.242328196314039   .29781877625982994    -2.182068062350819     .965013424842883   3.1993145089973436    1.072584837507233   -.6684417418009168    3.352382463695758 .09732015 10.104138 -.04377531   .18792105
12 540  1.9276471227788978  .001988624037434439   -.3236278849339678     -2.29415258108906    .9116017027632222   3.2857213485837806   1.2965503127883542  -1.2437609206951308    3.514005413135414 -.4000597  9.717905  -.8877165   -.6768553
12 541  1.3762489962105962   .14776618639497174   -.4173465837738495   -3.2941436293470407   .13173939960291836    3.170458234955559   1.5645989988822175  -1.6791808021333026    3.426015112514994 -.4000597  9.381433  -.8877165   -.6768553
12 542  1.4286671815798124   .20993915929853302   -.6129647277515083   -3.1597769450912585   .13657618968657204   3.1607899144266667   1.5528146473913176  -1.7210696712005649    3.413923577002241 -.4000597  9.298051  -.8877165   -.6768553
24 518  1.0849281548227325   1.5902525865297834   -1.201721506777245    1.4033398033972415    .5094632185513124   .39605018626281385   1.4964874413620533   .09975907506353998    .7394310750111355  .3676919 11.132304 -.51352525   .06835862
24 519   .8576518015446987   1.0395837490590194   -.7187290640754238    1.0070250155327574     .346551508766062   .34200401188499074   1.0699358067794342   .21335716336606358    .4683904989559322  .3676919  10.96274 -.51352525   .06835862
24 520    .619198112598521    .6002144061576349   -.4654560366692566     .5762243494938275    .1549451893371927    .3103419243113317    .6943805139033878   .06959242218941286    .4246376841470375  .3676919 10.947103 -.51352525   .06835862
24 521   .6349825811447389  -.24841785245411807    .6355387792310205     .3127930104572647   -.5328779048868046      .46635137120336  -.09335297286001387   .40825166552038317   .28529370602011084  .3676919 11.004182 -.51352525   .06835862
24 522    .644444708842612   -.1927561716753352    .9500962984387025    .10161018366375885   -.5988069706194974    .5734510324010819   .06339637300813872     .612468592632692      .33546054118171  .3676919 11.068382 -.51352525   .06835862
24 523   .7697853303262876  -.38455670488602567   1.2012255456461363   -.17080368676932234   -.6021785305483753    .8307343455374001 .0006340533618395261    .7314338960410725    .5329451403722646  .3676919 11.134664 -.51352525   .06835862
24 524   .6058562590256829     -.53223229882641   1.3022310357319196    -.3960817824486842   -.5883406407846419    .7845161210283393  -.06142469684829566    .7304297178707571    .4855090401218206  .3676919 11.189498 -.51352525   .06835862
24 525  1.1667857186363182   -.6411411057589604    1.032858878700492     .5781165523889458   -.3638712194747269    .8194717234719069   -.4519147430426934    .8866370316855878    .4140960279083173  .3676919 11.023984 -.51352525   .06835862
24 526  1.2887033755477029   -.6176738798054693   1.1385247155477938     .8758010775441138  -.26608127580926944    .7917098246592551   -.4792457607100179   1.1105903579242784   .38579445222435343  .3676919 10.988702 -.51352525   .06835862
24 527  1.7821961140834555   -.9823234658593454   2.0447971493371235     .9054846824424774  -.39583164828107886    1.294769646736412   -.8948078951703072   1.8732957486757271    .6222181470966961  .3676919  11.28522 -.25608787   .06835862
24 528  1.3006245194811679   -.5984988164491485    2.478614331594993   -.01734438340016875  -1.5337526680467706   1.3105613402279361   -.3173873450427383    1.512973189747977    .8553600985164364  .5298633 11.412585 -.45886245  -.02737811
24 529  1.3321100943460071   -.5611113986604401    2.867843781998552    .11631045655730188  -1.4008383819570827   1.2693138656342244    -.362048790181376   2.1049423124557083    .8614072592744672  .5298633 11.416216 -.45886245  -.02737811
24 530   .9837674694872144   -.5758925204229086    3.478654185514392    -.2767867076134647  -1.5830985424050854   1.1321228616371994  -.30668399641992833   2.5081694031587185    .7596577983816883  .5298633 11.384934 -.45886245  -.02737811
24 531  1.1193249431955294   -.7278681624623244   3.0994904238551735  -.009240845131881281  -1.9109646833199276   1.0752695698649646   -.5872513634071597     2.02995685177516    .8239707094511157  .5298633  11.51314 -.45886245  -.02737811
24 532  1.1326067189138591   -.6526414850236243   3.2121532027151645 -.0008781490951925708  -1.9094548871492805    1.087805320407431   -.5319245820678821    2.158785134157605    .8256734414424393  .5298633 11.397865 -.45886245  -.02737811
24 533  1.2258787815554313    .4960804690597711   2.8213995344322007     .6051026875570304   -2.531967447822173    .8833223809624103    .1689113946047499   1.9391368284086203    .7254526236003356  .5298633  11.44019 -.45886245  -.02737811
24 534   1.921114404122497   .27826199144723185   3.0841447524278487     1.699723597083649  -1.8645514325096473    .9528901413610174   -.3857599882584174   2.8465163583126563    .6227488233190823  .5298633 11.402142 -.45886245  -.02737811
24 535  1.8340009951383043    .3975214532549271    2.923051327274613    1.7952063077378013   -1.356907198322937    .7507032432493446   -.1819628764611369    3.014550603544895   .46554270324620084  .5298633 11.385097 -.45886245  -.02737811
24 536  1.5349211795882363   .05410553671091947    2.958629237629099    1.5733158440303257  -1.0953697350650116    .5025861424038139   -.5725808729697792   3.0084440891518294   .20805238622502492  .5298633 11.408693 -.45886245  -.02737811
24 537  1.6989083195817807    .5254939469589083    2.145351791271237    1.8507542421873688  -1.4152679843366835    .5022225859696582   -.5144034749295372    2.828993412901932   .30303388536918374  .5298633 11.439918 -.45886245  -.02737811
24 538   1.543770680238651    .2645930910581465    2.205610173833568    1.6629370189337793  -1.2988328802697713   .47850853071321653   -.6085137065995876    2.793614610206566   .22176467364543564  .5298633 11.433568 -.45886245  -.02737811
24 539  1.7063641597323194    .2262314709971071    2.172214006273747    1.6865589309585547   -1.386720424626006    .6165362764263501   -.7498444470085663    2.742940630716939   .34803079657149044  .5298633 11.727825  -.9014685  -.02737811
24 540  1.5856343507954462   .06695299125626464   2.4462673059645246    1.4998515062713833  -1.6638889209408165    .5694199215988389   -.9141848723023522    2.868368984510162   .27035115104925733  .8235081 11.976895  -.8967311  -.00933653
24 541   1.847678527998922   .08093063238019954   2.1898728654331663    2.2344875402885753   -.8214587082856881    .5335618815043692   -.9821379941032271   3.0059248081560073    .2576712357668589  .8235081 12.055917  -.8967311  -.00933653
24 542  1.7830465625735823 -.048631221941606295      2.0073076033922     2.280236908897838   -.7160932777325884   .45151844497861665  -1.1163147238904163    2.758271557762301    .1669528296503122  .8235081 12.033325  -.8967311  -.00933653
end
format %tm date_ts

[Clyde Schechter]

Try this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(ID date_ts) double(c5_mktrf c5_smb c5_hml c5_rmw c5_cma c3_mktrf c3_smb3 c3_hml c1_mktrf) float(OP_w log_ME log_BM log_INV)
 2 518  -.6594277566392851   .05445094242234677   -1.077858951724136    -.6806547372405378    .3934844991135229  -.35060817080290607   .23986541400841066  -1.0643549264277183     .268177445555968   .177662  9.895276  .08281849   -.0746393
 2 519   -.816827319182983 -.059856149045377005   -1.075107352802319    -.6671493608196993    .3429487788723353  -.49725482208050775   .14368907151868116   -1.106208205326498  .044749473209862556   .177662  9.503444  .08281849   -.0746393
 2 520 -1.0701099836391945  -.20708634475456672    -.717444162894207   -2.1019536912819157   1.0766906744340865  .016025658950552968    .3712397820982991   -.8199320061102071    .4661251510490124   .177662  9.993938  .08281849   -.0746393
 2 521 -1.0638513434727304    .5469000324589408   -1.664144504556238   -1.8554267151428205   1.6397368008252222   -.1201503519279512   1.1086553124371468  -1.1321482160623288    .5098260601876486   .177662  9.654766  .08281849   -.0746393
 2 522 -1.0429674246993836    .5242384437654679   -1.491087483369891   -1.8954153001486331   1.5452163741417095 -.048295100944089064    1.144510261369622   -.9730097158354991    .5265940128850879   .177662  9.672904  .08281849   -.0746393
 2 523  -.8707605327888202    .2889160812563596  -1.2504663526234037   -2.1459010898107973    1.548474611127883    .2542822077544691   1.0448681000084168   -.8476407493974674    .7943256643849086   .177662  9.755959  .08281849   -.0746393
 2 524 -1.1354596333360267   .06907083149875537  -1.0438674723199837   -2.5433146153273363   1.6466571245607708   .21316825453884136    .9854142802655839   -.8178396299638147    .7128754956314696   .177662  9.792863  .08281849   -.0746393
 2 525   -.860988123085949   .03365089114233044  -1.1444267158052073   -2.1137677686192284   1.7414389682427434   .24931272052025677    .7094032618453386    -.696546456780359    .6297433666330817   .177662  9.653101  .08281849   -.0746393
 2 526  -.7773414386266049  .051228722426810036   -1.056836826005369   -1.9441049959858874   1.7946026514152944    .2206129172077662    .6809342053785858  -.44780665122665786   .45869987703948356   .177662  9.648088  .08281849   -.0746393
 2 527 -.46495504204296045  -.02406774640297804  -.49951676137511325   -1.8857790247871544   1.8100768022908111   .48830025609715033    .5809198698245798     .091704519008456    .5369865284390679   .177662  9.645119   .2923358   -.0746393
 2 528  -.7590385509442364     .352905297154263   -.2754460207100755   -2.3252532312051337    .7466557160390765    .4638115683509141   1.1079549991239255  -.33281886001529637    .6855174028452262 .09179211  9.655152   .3125259    .0759443
 2 529  -.5720943623828041    .3376754156495048    .1916292375336488   -1.9688302516492133    .6566005805811441   .45752815785103323       .9791709480483   .13228292637305605    .5370519592638734 .09179211   9.73199   .3125259    .0759443
 2 530 -1.0350847242126342   .36886837325759775    .9999795263009603   -2.4509992541551515      .27429725634875   .30271110821357017    1.052347338922903     .502750044634501    .3433070133569227 .09179211  9.843498   .3125259    .0759443
 2 531  -.6753432288152006     .236788522744518   .47030446315245755   -2.0829142483512753  -.12062353159075023    .5101694438650821    .7952930758407566   -.1892052496299994     .625640783399696 .09179211  9.831007   .3125259    .0759443
 2 532  -.6618176482822845   .32027147396855543    .6030308199616317   -2.0783764143664287  -.12229867994737287      .52473503187279    .8490155072273599 -.059035475369381585    .6326774804949536 .09179211  9.748051   .3125259    .0759443
 2 533  -.6101730780392948    .4578612432182476    .6090321728203103   -1.9784804020958215  -.23393721657726996    .5006043133036017    .9649933814897531  -.07718124288872841    .6388783837156035 .09179211  9.759002   .3125259    .0759443
 2 534  -.6255067238920355   .28231687664763583   1.0147478711564295   -1.9710635135416363  -.48162435325107533   .48451352420674554     .685902862035111   .31195229465536123    .5527418549009201 .09179211  9.808792   .3125259    .0759443
 2 535   -.729376232347035   .35809743272086336    .9235112666906642    -1.896730780832791 -.028705923257644872   .34789015821404284    .7959279678837049   .44079501847035785   .44179057486245843 .09179211  9.813068   .3125259    .0759443
 2 536 -1.0977351176856183  -.21673411120230113    .8996651548027845   -2.1097510353229487    .5923477742591541   .21143700215188027    .5054126654321143    .4709531796448711   .22049507896608028 .09179211  9.671171   .3125259    .0759443
 2 537 -1.0020780018173283    .1347782329131826     .365511674795139   -1.9344210362109897    .3434693838487243   .16008176724350043    .9659643328704941   -.4232042596315526   .25461248834044553 .09179211  9.613819   .3125259    .0759443
 2 538  -.9600754080105114  .039493939867665626    .5997693061395606     -2.05784399220207    .1625129836174537   .20934931453105815    .8806235767208399   -.3626450704761581   .33099250949201764 .09179211  9.644591   .3125259    .0759443
 2 539  -.8863136504081155   .05222099216077969    .5764890127689746   -2.0322968223746445   .11329656136968991    .2666509222649872     .926189955482837   -.3787626732415068    .3847438453949824 .09179211  9.794403  .16324078    .0759443
 2 540   -.848306305323352   .11745930966452874     .449634602311745   -1.9847684481155956   .19205932715508722   .27881589049337707    .9757087923835845   -.3795348003734731   .41201851963670644  .0559569  9.751437 .018283758 -.018460508
 2 541  -1.031914102967818   .14918688110505698    .4792034476839251    -2.370140186988881  -.16291001634210017   .24033937520696402   1.0005781885544638  -.40833270747009354   .36439883446698224  .0559569  9.706759 .018283758 -.018460508
 3 518  -.5861513242319291    .7312464537431488  -1.3289418905878083    .25144871207094033    .2913310560051201   -.7125826409849143    .6647664708900154   -.8845772621559558  -.07533138286607843 .20092946 11.742893  -.1803327    .1168695
 3 519  -.4207942513571583    .4591176879961159   -.8446157524311477   -.18567120371984436    .2529415917525082   -.3331036763265418    .5018617268539436   -.6404295913180964   .06876593514512486 .20092946 11.842048  -.1803327    .1168695
 3 520  -.5818590775361532   .17155431106867625   -.6709853953344913    -.5048336299032874   .15054402677587575    -.314922745782799    .2814012690972454   -.7100023311016144   .06641393988543132 .20092946  11.81984  -.1803327    .1168695
 3 521  -.4812033702042062    .8320654894916679  -1.3524956772096992   -.23033073508002677    .4182467917061139   -.3630742561765157    .9413487602333468   -.9534822479121825    .1686361266216725 .20092946 11.851997  -.1803327    .1168695
 3 522 -.43942730615946524    .7836581571435226  -1.0173284643693616   -.30131827384850096   .23024635650711645   -.2785512735707216    .9437719373623698   -.7535985943979817   .17576503483607842 .20092946 11.851375  -.1803327    .1168695
 3 523 -.21491447661912189    .5059895380328068   -.8114947277238649    -.4999912670352613   .24264658653306448   .04387194071818956    .7485902033088556    -.670106212214578    .4563687005997379 .20092946 11.853978  -.1803327    .1168695
 3 524  -.7620819166689371   .08237251437513204  -.31235091621477196   -1.3772533982611836    .5712520124499889  -.03245983306164868    .6053298820733206  -.37724903147592004   .22517094120151304 .20092946 11.790818  -.1803327    .1168695
 3 525 -.38316824843591046   .09691865828053547   -.3402373749661418    -.9516215998907982    .6486544925416116   .11126022061396246   .45833057481577055   -.1942304721764132    .2628955198886706 .20092946 11.812437  -.1803327    .1168695
 3 526 -.16930548609661528   .13648696428689894   -.1711997780213821   -.39205726093746984    .8352517232940349  .017762475079386775    .3696179485666989   .29258952959362783 -.013513295748693116 .20092946  11.83498  -.1803327    .1168695
 3 527  .16192870779757584   .11432995603115498   .41367843928387416     -.315549318350386    .8878597087205351    .3053616170856523   .29552510820579897     .909049323242425   .07432384414342783 .20092946 11.850076  -.5490569    .1168695
 3 528  -.0698186370706615    .3759422660937906    .6004114349585263    -.6927218906199007   .14148718920924397   .28814304269993146    .6619745124334219    .6134058915212599   .19618072890873794 .12633502 11.855678 -.18300496    .4808375
 3 529  .08438631242170697     .402604652181851   1.2905978763497639    -.3264338932548112    .2125888282606192   .24943135773806802     .538901879493648   1.4184293191869621   .05672808346793445 .12633502  11.97041 -.18300496    .4808375
 3 530  .19175963394429346   .42536125454271784   1.1010711400645496     -.190835869824589   .21862799204954375   .28884139962146455    .5286065304156102    1.258260563116483     .172607482571777 .12633502 12.164367 -.18300496    .4808375
 3 531   .9157342280958747    .2683091988194366     .226190233109959    .41282666208087104  -.38561481250425844     .667840907391303   .18309734782623552   .23025395001631033    .6703744049041107 .12633502 12.127088 -.18300496    .4808375
 3 532   .9043910204208063   .37279528482133856    .5716095164257884     .3080490379724934   -.4657479861710252    .7174070849501037   .28357262596677735    .5210654053352254    .7038867917207255 .12633502 12.142984 -.18300496    .4808375
 3 533   .9404140863685251    .7255547503283759     .461242471188547    .49911958754258345     -.66367543197422     .665027835810094   .48348160484989217    .4695649846132395    .6869160560969249 .12633502  12.16617 -.18300496    .4808375
 3 534  1.2523978184594142    .5239937599366099    .8160305963257133     1.008492316995191   -.4982810378563971    .6885632739240389    .1439459508146025    .9998680599432627    .6126660023437689 .12633502 12.170592 -.18300496    .4808375
 3 535  1.0975644105507345    .6589898455968186     .646574899624055     1.132405627220828    .2274104776624013   .47134516145259814   .34189560168860056   1.1919965417611418     .426812366849566 .12633502  12.19309 -.18300496    .4808375
 3 536   .7036788431628432    .2157587998126206    .6974606808292286     .8365883028195042    .5540493571082573     .255580700913168 -.004061726720843317   1.1894639363315322   .15974042638493147 .12633502 12.238416 -.18300496    .4808375
 3 537   .8216520424868339    .6074118688479045   .07137446317935037    1.0454407072901035    .2811708612537388   .23099120696016945   .23202500833214387    .7122505241965827    .2071668749573447 .12633502 12.250907 -.18300496    .4808375
 3 538   .8580950663770417    .5877408661539578   .17948172225468645    1.0045267271384366   .18090644196541927     .317546827958734   .19712587794734687    .8239447454144687    .2833127282339817 .12633502 12.365175 -.18300496    .4808375
 3 539  1.0040819584872864    .8322011026308325   .07424111207598261    1.1630205890639334  .015486607325947643   .35942291368487705    .2866690165458073    .8148203272284155   .32982904754233433 .12633502  12.51071  -.8436403    .4808375
 3 540  1.1741745748145738   1.0120599738909184   -.1921231628499519    1.4592627745843725    .4310440601506438   .40981409085017656     .473705546060708    .7309554618198151   .41101155095532677 .20186116  12.36861  -.7974355   .10089315
 3 541  1.2211247600456494   1.0623149082082444   -.4106571372441135    1.7396646725916425    .8525826647929718   .37657864220759546    .5954979914246795    .5251250624546279   .40496995721387996 .20186116 12.334788  -.7974355   .10089315
 3 542  1.5317360542930085   1.5475412212984376   -.6341202008569702      2.06903361654959     .635421103286572   .48359534101228335    .8910523120403971    .4679476783542297    .5306771085093952 .20186116  12.32434  -.7974355   .10089315
 3 543    1.58086062788477   1.5223622493226558   -.6532311674169562    1.7874855656615507  .024720630759698548    .6672794972551322    .7814772302757155   .23053275312328236     .784297608140435 .20186116  12.25135  -.7974355   .10089315
12 518   .6951112751778548   2.7939554142615286  -3.0866648486148343    -.4554563763861963  -.42871825881933034    .9525020510083984   2.8369532662023644  -2.7697408916698087    3.147457716296923 .22271118  9.951706  -.4659322 -.019581215
12 519   .8220104099629413   2.7676944458544943   -2.941782145066764    -.5940367939926805   -.4155949786615203   1.1627860979599145    2.843357948978398   -2.666342040543933   2.9945270366063914 .22271118 10.037884  -.4659322 -.019581215
12 520   .6904562938569727    2.598224850793624  -2.7818170217643967   -1.0547926698466004  -.30733884476916984   1.2902258006599183    2.818632697241534  -2.6235962511143898   3.0730375214606247 .22271118  10.29691  -.4659322 -.019581215
12 521   .6591129173021666   2.3762629286648456  -2.5489441026398367   -1.1455118343542097   -.4031799227013195   1.2911999050869856    2.707855212360802  -2.5861914978566904   2.7566527066860003 .22271118 10.134933  -.4659322 -.019581215
12 522   .6461856545719429   2.3825213420004863   -2.683895592851199   -1.0980075328109833  -.34194867778892046   1.2539840907201216    2.687790658805108   -2.668136708816345    2.759196238902415 .22271118 10.109614  -.4659322 -.019581215
12 523  1.2622489118150302   1.6927899241192192  -2.3864218975085265   -1.3262139898651348  -.28771914427216316   1.9866241093217933   2.0569940217434217  -2.5838392013170086    3.422509290203173 .22271118 10.093562  -.4659322 -.019581215
12 524  1.0304047924120217   1.4891918586407087  -2.2312764040831787   -1.6543339513826933  -.24671676029330314    1.933146281308972   1.9847800393651243  -2.5820570384538684     3.30815939381816 .22271118 10.208443  -.4659322 -.019581215
12 525  1.8692694188486618   1.4915529690180982   -2.345194937955897    -.6333175628854057 -.050218681925002784   2.2120318423997225   1.6500818359601175  -2.2113438388094417    3.314516661403602 .22271118 10.430035  -.4659322 -.019581215
12 526   2.028746140348968    1.525966589100563   -2.168998324347883   -.33092661515230537  .042648469455306426    2.196420939854592   1.6317035908778519  -1.8440078034321727    2.997954905051854 .22271118 10.585073  -.4659322 -.019581215
12 527   2.417740542509758   1.7879699381141765   -1.512427374127106   -.16786160309813414    .2851875286862544   2.4939973898052523    1.779753408863915   -.9581678951698507   3.0107063341003615 .22271118 10.454576  -.6467184 -.019581215
12 528   2.250652864349174   1.8985644928307126  -1.3554398405283283     -.507697252905456 -.051271842096600295   2.5125085855614477   1.9723537449426285  -1.0457943199129183   3.0324352972221056 .09732015 10.547127  -.3942127   .18792105
12 529   2.122799289509387    2.003835782009266    -.937537025742587    -.5766581071619543     .361894149141361   2.4163502872200446    2.116792617614248  -.42973646835393486   2.7140399522524326 .09732015 10.263887  -.3942127   .18792105
12 530  1.6074842951695196    1.879578935513981 -.026911306651628664   -1.2393564914453563    .3743485142653915   2.2822369000313874    2.139976061752068   .20900532902350788    2.475281917586331 .09732015  9.963782  -.3942127   .18792105
12 531   2.020428539781826   1.6790497882749984   -.7208645469256196    -.7551695253685966  -.16467272605375705   2.4524430636587713   1.7546593194709847    -.690553760773363   2.7311972244358946 .09732015 10.031676  -.3942127   .18792105
12 532  2.1020728871314116    1.993279540163171  -.41620049243275625    -.6180112667601106  -.08614446605787851   2.4589539814137487   1.9800388006846816  -.26027619334617347   2.7219311934741532 .09732015  9.836323  -.3942127   .18792105
12 533  1.9281104794762394    .7963983493047122   -.1092388519550711   -1.3012137762466964    .6328029004785427   2.6532367729133184   1.2101238718058944   -.0758940182812693   2.8246874785058904 .09732015  9.659064  -.3942127   .18792105
12 534  1.9149639522853399    .9927175577037807    -.552761091295126   -1.3555455370763243    .8684593830626934   2.6807182080379155    1.432082433676622  -.36599056197427826    2.921391425105231 .09732015 9.1829815  -.3942127   .18792105
12 535    1.74907846511217    .9946122597218365   -.5175942624879046   -1.3077096806577495   1.3184967826001022    2.547405085334251    1.443530305572925  -.18727652291879648    2.801227080085198 .09732015  9.298051  -.3942127   .18792105
12 536  1.4953344100261543    .7151132194060736  -.48212487605375387   -1.5006719589585074   1.5170432127361386     2.51772693523456    1.394787179305554  -.18711252323035077     2.66205190646847 .09732015 9.3406105  -.3942127   .18792105
12 537  1.5043268479556267    .9673279716952559   -.7034329165767186   -1.4455497166516058   1.3153278503406305    2.447990473240325   1.8806199434365245   -.9613568819801581    2.639887453877294 .09732015  9.106996  -.3942127   .18792105
12 538   1.897972744423626     1.10556287288615  -.06533673571051352   -1.5191213603574982    .5481642453091686   2.7984893456823623   1.8561050975981632    -.503678948358953   3.0371772758267555 .09732015  9.420653  -.3942127   .18792105
12 539  1.8999798439829079    -.242328196314039   .29781877625982994    -2.182068062350819     .965013424842883   3.1993145089973436    1.072584837507233   -.6684417418009168    3.352382463695758 .09732015 10.104138 -.04377531   .18792105
12 540  1.9276471227788978  .001988624037434439   -.3236278849339678     -2.29415258108906    .9116017027632222   3.2857213485837806   1.2965503127883542  -1.2437609206951308    3.514005413135414 -.4000597  9.717905  -.8877165   -.6768553
12 541  1.3762489962105962   .14776618639497174   -.4173465837738495   -3.2941436293470407   .13173939960291836    3.170458234955559   1.5645989988822175  -1.6791808021333026    3.426015112514994 -.4000597  9.381433  -.8877165   -.6768553
12 542  1.4286671815798124   .20993915929853302   -.6129647277515083   -3.1597769450912585   .13657618968657204   3.1607899144266667   1.5528146473913176  -1.7210696712005649    3.413923577002241 -.4000597  9.298051  -.8877165   -.6768553
24 518  1.0849281548227325   1.5902525865297834   -1.201721506777245    1.4033398033972415    .5094632185513124   .39605018626281385   1.4964874413620533   .09975907506353998    .7394310750111355  .3676919 11.132304 -.51352525   .06835862
24 519   .8576518015446987   1.0395837490590194   -.7187290640754238    1.0070250155327574     .346551508766062   .34200401188499074   1.0699358067794342   .21335716336606358    .4683904989559322  .3676919  10.96274 -.51352525   .06835862
24 520    .619198112598521    .6002144061576349   -.4654560366692566     .5762243494938275    .1549451893371927    .3103419243113317    .6943805139033878   .06959242218941286    .4246376841470375  .3676919 10.947103 -.51352525   .06835862
24 521   .6349825811447389  -.24841785245411807    .6355387792310205     .3127930104572647   -.5328779048868046      .46635137120336  -.09335297286001387   .40825166552038317   .28529370602011084  .3676919 11.004182 -.51352525   .06835862
24 522    .644444708842612   -.1927561716753352    .9500962984387025    .10161018366375885   -.5988069706194974    .5734510324010819   .06339637300813872     .612468592632692      .33546054118171  .3676919 11.068382 -.51352525   .06835862
24 523   .7697853303262876  -.38455670488602567   1.2012255456461363   -.17080368676932234   -.6021785305483753    .8307343455374001 .0006340533618395261    .7314338960410725    .5329451403722646  .3676919 11.134664 -.51352525   .06835862
24 524   .6058562590256829     -.53223229882641   1.3022310357319196    -.3960817824486842   -.5883406407846419    .7845161210283393  -.06142469684829566    .7304297178707571    .4855090401218206  .3676919 11.189498 -.51352525   .06835862
24 525  1.1667857186363182   -.6411411057589604    1.032858878700492     .5781165523889458   -.3638712194747269    .8194717234719069   -.4519147430426934    .8866370316855878    .4140960279083173  .3676919 11.023984 -.51352525   .06835862
24 526  1.2887033755477029   -.6176738798054693   1.1385247155477938     .8758010775441138  -.26608127580926944    .7917098246592551   -.4792457607100179   1.1105903579242784   .38579445222435343  .3676919 10.988702 -.51352525   .06835862
24 527  1.7821961140834555   -.9823234658593454   2.0447971493371235     .9054846824424774  -.39583164828107886    1.294769646736412   -.8948078951703072   1.8732957486757271    .6222181470966961  .3676919  11.28522 -.25608787   .06835862
24 528  1.3006245194811679   -.5984988164491485    2.478614331594993   -.01734438340016875  -1.5337526680467706   1.3105613402279361   -.3173873450427383    1.512973189747977    .8553600985164364  .5298633 11.412585 -.45886245  -.02737811
24 529  1.3321100943460071   -.5611113986604401    2.867843781998552    .11631045655730188  -1.4008383819570827   1.2693138656342244    -.362048790181376   2.1049423124557083    .8614072592744672  .5298633 11.416216 -.45886245  -.02737811
24 530   .9837674694872144   -.5758925204229086    3.478654185514392    -.2767867076134647  -1.5830985424050854   1.1321228616371994  -.30668399641992833   2.5081694031587185    .7596577983816883  .5298633 11.384934 -.45886245  -.02737811
24 531  1.1193249431955294   -.7278681624623244   3.0994904238551735  -.009240845131881281  -1.9109646833199276   1.0752695698649646   -.5872513634071597     2.02995685177516    .8239707094511157  .5298633  11.51314 -.45886245  -.02737811
24 532  1.1326067189138591   -.6526414850236243   3.2121532027151645 -.0008781490951925708  -1.9094548871492805    1.087805320407431   -.5319245820678821    2.158785134157605    .8256734414424393  .5298633 11.397865 -.45886245  -.02737811
24 533  1.2258787815554313    .4960804690597711   2.8213995344322007     .6051026875570304   -2.531967447822173    .8833223809624103    .1689113946047499   1.9391368284086203    .7254526236003356  .5298633  11.44019 -.45886245  -.02737811
24 534   1.921114404122497   .27826199144723185   3.0841447524278487     1.699723597083649  -1.8645514325096473    .9528901413610174   -.3857599882584174   2.8465163583126563    .6227488233190823  .5298633 11.402142 -.45886245  -.02737811
24 535  1.8340009951383043    .3975214532549271    2.923051327274613    1.7952063077378013   -1.356907198322937    .7507032432493446   -.1819628764611369    3.014550603544895   .46554270324620084  .5298633 11.385097 -.45886245  -.02737811
24 536  1.5349211795882363   .05410553671091947    2.958629237629099    1.5733158440303257  -1.0953697350650116    .5025861424038139   -.5725808729697792   3.0084440891518294   .20805238622502492  .5298633 11.408693 -.45886245  -.02737811
24 537  1.6989083195817807    .5254939469589083    2.145351791271237    1.8507542421873688  -1.4152679843366835    .5022225859696582   -.5144034749295372    2.828993412901932   .30303388536918374  .5298633 11.439918 -.45886245  -.02737811
24 538   1.543770680238651    .2645930910581465    2.205610173833568    1.6629370189337793  -1.2988328802697713   .47850853071321653   -.6085137065995876    2.793614610206566   .22176467364543564  .5298633 11.433568 -.45886245  -.02737811
24 539  1.7063641597323194    .2262314709971071    2.172214006273747    1.6865589309585547   -1.386720424626006    .6165362764263501   -.7498444470085663    2.742940630716939   .34803079657149044  .5298633 11.727825  -.9014685  -.02737811
24 540  1.5856343507954462   .06695299125626464   2.4462673059645246    1.4998515062713833  -1.6638889209408165    .5694199215988389   -.9141848723023522    2.868368984510162   .27035115104925733  .8235081 11.976895  -.8967311  -.00933653
24 541   1.847678527998922   .08093063238019954   2.1898728654331663    2.2344875402885753   -.8214587082856881    .5335618815043692   -.9821379941032271   3.0059248081560073    .2576712357668589  .8235081 12.055917  -.8967311  -.00933653
24 542  1.7830465625735823 -.048631221941606295      2.0073076033922     2.280236908897838   -.7160932777325884   .45151844497861665  -1.1163147238904163    2.758271557762301    .1669528296503122  .8235081 12.033325  -.8967311  -.00933653
end
format %tm date_ts

capture program drop one_month_correlations
program define one_month_correlations
    local date = date_ts[1]
    ds date_ts ID, not
    local vbles `r(varlist)'
    pwcorr `vbles'
    matrix M = r(C)
    drop _all
    svmat M, names(col)
    gen date_ts = `date'
    format date_ts %tm
    local nvars: word count `vbles'
    gen variable = ""
    forvalues i = 1/`nvars' {
        replace variable = "`:word `i' of `vbles''" in `i'
    }    
    exit
end

order _all, alphabetic
ds ID date_ts, not
local vbles `r(varlist)'

runby one_month_correlations, by(date_ts)

replace variable = "_" + variable
reshape wide `vbles', i(date_ts) j(variable) string
ds date_ts, not
collapse (mean) `r(varlist)'
gen one = 1
reshape long `vbles', i(one) j(variable) string
assert substr(variable, 1, 1) == "_"
replace variable = subinstr(variable, "_", "", 1)
drop one
order variable, first
sort variable

Notes:

1. This uses the -runby- command, written by Robert Picard and me, and available from SSC.

2. This code is fairly complicated. If in your full data there are no missing values, or the patterns of missing values and overlaps of dates are such that no correlation coefficients at the month level would ever be missing, then this code could be greatly simplified. But I didn't want to make strong assumptions about your data. So I wrote the code in a way that would be robust to the possibility that in some months some (or all) of the correlations would not be calculated due to missing data.

3. To make the results look like a standard correlation table, where the 1's fall on the diagonal, I chose to re-order the variables being correlated alphabetically (or, actually, in ASCII sort order). This is different from the order you had them in your example data. I could have done it somewhat differently to maintain the original order of the variables, but this would have complicated the code even more and I didn't think it would be worth it.

At the end of this code, the data set in memory is a correlation table. You can then -list- it or export it to a text file or a spreadsheet, or whatever.

[John Hanser]

This works just perfectly. Thank you so much, Clyde!