[Sartaj Hussain]

Very simple. The counts of average number of variables across monthly calculations that are used in correlation.

[Sartaj Hussain]

Moreover, when i run the code on large data csv file, it works but then collapses midway displaying this error:

no observation
r(2000);

How can i resolve it?

[Sartaj Hussain]

P.S. My data has missing values that are basically not observed for the given variables on certain dates.

[Clyde Schechter]

Re #16: This doesn't make any sense. The same variables are used in every month, so the number never changes.

Re #17. I suspect this is happening because there are some months where every observation contains missing values for at least one of the variables, so when -corr- tries to run, it has nothing to work with. You can get around this by changing the code as follows:

clear*
sysuse auto, clear

replace headroom = . if rep78 == 5

local vbles price mpg headroom length weight
local vbles: list sort vbles

frame create correlations
frame create cumulative


levelsof rep78, local(rep78s)
foreach r of local rep78s {
    capture corr `vbles' if rep78 == `r'
    if c(rc) == 0 {
        matrix C = r(C)
        frame correlations {
            svmat C, names(col)
            gen rep78 = `r'
            gen vble = ""
            local i = 1
            foreach v of local vbles {
                replace vble = `"`v'"' in `i'
                local ++i
            }
        }
        frame cumulative: frameappend correlations
        frame correlations: clear
    }
    else if c(rc) != 2000 { // UNEXPECTED ERROR
        display as error "Unexpected error in -corr-"
        exit `c(rc)'
    }
    else {
        continue
    }
}

frame drop correlations
frame change cumulative
frame put _all, into(averages)
frame change averages
collapse (mean) `vbles', by(vble)

Changes shown in bold face. (Replace the auto.dta variables with the actual variables you are using. Remember, the role of rep78 is served by your monthly date variable.

This will allow the code to skip over any month where the pattern of missing values leaves nothing for -corr- to work with.

[Sartaj Hussain]

This is fine, but the new code doesn't show month-wise correlation matrices in STATA console as was given by previous code. Please check.

[Clyde Schechter]

Replace -capture- by -capture noisily- and you will get those. With this change, you will again get the no observations error message, but the code will not stop--it will just proceed to the next month.

[Sartaj Hussain]

I am really grateful to STATA community for the timely support.

In the next part, from the given monthly cross sectional data stock return dataset, I want to create 25 equal and value weighted portfolios returns series using bi-variate dependent sort technique. First sort will be done using one of these control variables, like beta, size, bmratio, mom, rev, illiq, coskew and idioskew and the second sort in all cases is on idiovol. In the first sort, five portfolios will be sliced and then in the second sort within each of these portfolio five portfolio will be carved. For each of the 5*5 = 25 portfolio returns series, both equal weighted average and value weighted average returns should be the final output. The calculations should ignore observations with missing values. Please note market capitalization data which is essential to create value weighted portfolio return series is yet not added to the data file. It will be added before any code is run for the given purpose.

Regards,
Sartaj

[Sartaj Hussain]

I want to winsorize the variables in example data set in each cross-section (month) at the 0.5 and 99.5 % -tiles. I found a code that is suggested by Nick Cox. Just wanted to know that will it serve the right purpose. The code that is modified to suit one of my variables: rt is given below: The mdate is the month year variable.

egen rank = rank(rt), by(mdate)
egen count = count(rt), by(mdate)
gen pp = (rank - 0.5)/count
egen p005 = min(rt / (pp >= 0.005)), by(mdate)
egen p995 = max(rt / (pp <= 0.995)), by(mdate)
gen rtW = max(p005, min(rt, p995))

If a month month_id is required to be there instead of mdate, then how can the code be modified to create such id or else will mdate work in the given case?

[Clyde Schechter]

That code should work as it is.

The question raised in #23 is off the topic of the original thread. In the future, please start a new thread when asking a different question. It is easy to think of these threads as a dialog between a questioner and an answerer, but other people follow along looking for , or come and search the forum for, specific questions. When the content of a thread does not match the thread title, people either cannot find what they are looking for, or waste their time reading irrelevant material. So please keep threads on topic.

[Sartaj Hussain]

Ok. Thanks for advice.

[Sartaj Hussain]

Request you to kindly update the below code so that it generates Spearman's rank correlations instead of Pearson's product momentum correlations. The example dataset is also appended.

local vbles rt idiovol beta size bmratio mom rev illiq coskew idioskew
local vbles: list sort vbles

frame create correlations
frame create cumulative

levelsof mdate, local(mdates)
foreach r of local mdates {
    capture corr `vbles' if mdate == `r'
    if c(rc) == 0 {
        matrix C = r(C)
        frame correlations {
            svmat C, names(col)
            gen mdate = `r'
            gen vble = ""
            local i = 1
            foreach v of local vbles {
                replace vble = `"`v'"' in `i'
                local ++i
            }
        }
        frame cumulative: frameappend correlations
        frame correlations: clear
    }
    else if c(rc) != 2000 { // UNEXPECTED ERROR
        display as error "Unexpected error in -corr-"
        exit `c(rc)'
    }
    else {
        continue
    }
}

frame drop correlations
frame change cumulative
frame put _all, into(averages)
frame change averages
collapse (mean) `vbles', by(vble)

* Example generated by -dataex-. To install: ssc install dataex
clear
input int stock_id str52 stock float(mdate rt idiovol beta size bmratio mom rev illiq coskew idioskew)
1 "3M India Ltd." 510 .0520779 .0916615 .7510074 5.772126 .2301245 -.2421852 .0086664 .568561 -11.246819 .6554044
1 "3M India Ltd." 511 .0533333 .1182759 .7796013 5.822898 .2346667 -.08882007 .0520779 .0834102 -12.68292 .7936354
1 "3M India Ltd." 512 -.0031646 .033802 .7886676 5.874875 .2227848 -.03028263 .0533333 .1184029 -12.682983 .8757973
1 "3M India Ltd." 513 .0031746 .0558377 .6494458 5.871695 .2379683 .1995213 -.0031646 .0922577 -16.301088 1.004618
1 "3M India Ltd." 514 -.0082279 .0225026 .7004792 5.874875 .2372152 .18131915 .0031746 .4863775 -16.789318 1.0411569
1 "3M India Ltd." 516 -.0228061 .0447686 .6646857 5.831355 .2478599 .1798179 -.0346203 .0671644 -17.989431 1.2790872
1 "3M India Ltd." 517 -.0630813 .0643021 .6528904 5.808293 .2536445 .17160204 -.0228061 .1301709 -17.13694 1.502053
1 "3M India Ltd." 518 -.1326715 .084525 .59209836 5.743131 .270722 .10628778 -.0630813 2.364254 -12.111776 1.1316483
1 "3M India Ltd." 519 .1862643 .1171338 .5532332 5.60079 .3351925 -.10605558 -.1326715 1.962032 -15.268206 .28582826
1 "3M India Ltd." 520 .1052632 .1162964 .7139871 5.771597 .2825614 -.2477172 .1862643 2.644915 -17.685534 .3838049
1 "3M India Ltd." 521 .0609524 .0747315 .700519 5.871695 .2556508 .03894482 .1052632 .0748728 -21.36768 .3219067
2 "A B B India Ltd." 510 .0310697 .0908318 .4928325 7.037915 .3767442 .0203469 .0927018 .0644927 -7.955564 .8680918
2 "A B B India Ltd." 511 .0193072 .066539 .4566708 7.068512 .3931433 .1042416 .0310697 .0393069 -7.885917 .8717429
2 "A B B India Ltd." 512 -.0049567 .0326465 .4516272 7.087632 .3856966 .22836693 .0193072 .0314682 -8.348626 .8894266
2 "A B B India Ltd." 513 -.1465931 .047887 .360816 7.082666 .4021704 .3833118 -.0049567 .1093975 -2.665329 1.035254
2 "A B B India Ltd." 514 -.0002085 .1316279 .4027637 6.92414 .4712529 .4084741 -.1465931 .0762896 -2.551017 .4909603
2 "A B B India Ltd." 515 .0369058 .0800709 .4821406 6.923934 .4713511 .19272646 -.0002085 .0238648 -.5813355 .5721269
2 "A B B India Ltd." 516 .1230645 .039354 .4618599 6.960177 .4709431 .19666737 .0369058 .0246835 -1.375635 .57083863
2 "A B B India Ltd." 517 .147538 .0784511 .5004291 7.07624 .4193375 .14288685 .1230645 .0576087 -1.888188 .6426939
2 "A B B India Ltd." 518 -.1015758 .0698819 .4680085 7.213857 .3654236 .11156937 .147538 .00926 17.036205 .2437781
2 "A B B India Ltd." 519 .0703369 .0813389 .4644257 7.106745 .4139284 .22523545 -.1015758 .0459729 9.548818 .1989993
2 "A B B India Ltd." 520 .1148791 .071224 .5211874 7.174716 .3867272 .12083534 .0703369 .0758109 16.906883 .05292072
2 "A B B India Ltd." 521 .0733518 .0411988 .5875958 7.283462 .3468782 .2675898 .1148791 .0236384 21.06798 .01546109
3 "A C C Ltd." 510 -.1629956 .0494911 1.0422283 7.906322 .3757709 .1532081 .0505785 .0001693 -5.548719 .575628
3 "A C C Ltd." 511 .037594 .0576206 1.0449023 7.728468 .4576692 .18472496 -.1629956 .0003263 -5.354321 .6685103
3 "A C C Ltd." 512 -.0018116 .0478863 1.0404295 7.76537 .4410869 .10122582 .037594 .0003022 -5.677112 .675517
3 "A C C Ltd." 513 -.0366606 .0545035 .927909 7.763659 .4440653 .16578344 -.0018116 .0004162 -.763524 .3999104
3 "A C C Ltd." 514 .2324793 .0545389 .9890953 7.726332 .4609646 .09409234 -.0366606 .0004073 -.05508276 .0857189
3 "A C C Ltd." 515 .0094772 .0679346 .9965018 7.93543 .3740752 -.22372445 .2324793 .0003013 .6842356 .12869345
3 "A C C Ltd." 516 -.1341612 .048411 .9468574 7.944861 .3777105 .11921484 .0094772 .0003085 -.6181256 .1951404
3 "A C C Ltd." 517 .0776496 .0739987 .9150216 7.801068 .4360965 .07467366 -.1341612 .0003754 -1.0836273 .01052911
3 "A C C Ltd." 518 -.1009413 .085914 .9575378 7.875917 .4047387 -.0813625 .0776496 .0003803 1.8999788 .3630732
3 "A C C Ltd." 519 -.0548737 .0731908 .8841012 7.769539 .4547292 .05469688 -.1009413 .000468 -6.26982 .4172138
3 "A C C Ltd." 520 .1100077 .0552034 .8759887 7.713101 .4811307 -.02480884 -.0548737 .0005921 -3.216651 .2910111
3 "A C C Ltd." 521 .1493462 .0546524 .888853 7.817489 .433448 -.08366531 .1100077 .0003609 2.0965748 .25523832
4 "A D C India Communications Ltd." 510 -.1504425 .0905394 1.5756077 3.663049 .8875517 -.2089349 -.0280963 .1526467 -11.18617 .9999411
4 "A D C India Communications Ltd." 511 .0229167 .0694774 1.543413 3.500137 1.064583 .04492957 -.1504425 .4789313 -9.748292 1.0794683
4 "A D C India Communications Ltd." 512 -.0902919 .0908449 1.514939 3.522825 1.040733 -.3168728 .0229167 .7972752 -7.856839 1.2683494
4 "A D C India Communications Ltd." 515 .0135135 .0816317 1.2613417 3.422306 1.168619 -.3657852 .0918033 1.403588 -14.12266 1.5074098
4 "A D C India Communications Ltd." 516 -.1525926 .0751573 1.1426127 3.435599 1.179704 -.2180948 .0135135 .5731306 -20.8343 1.769466
4 "A D C India Communications Ltd." 517 -.0821678 .0859583 1.1992506 3.269949 1.392133 -.18733987 -.1525926 2.918928 -19.735325 1.8279516
4 "A D C India Communications Ltd." 518 -.0895238 .1204351 1.2922428 3.184284 1.516762 -.3042599 -.0821678 1.175537 -16.862587 1.8053592
4 "A D C India Communications Ltd." 519 .1757323 .1155414 1.3370316 3.090588 1.741423 -.3827892 -.0895238 .414616 -10.48587 1.98192
4 "A D C India Communications Ltd." 520 .1868327 .1650361 1.1820819 3.252311 1.481139 -.6628183 .1757323 .2605021 6.146185 .8412775
4 "A D C India Communications Ltd." 521 .1124438 .198927 .9547688 3.423611 1.247976 -.3787015 .1868327 .1113622 1.0847142 1.0265048
5 "A G C Networks Ltd." 510 -.2367297 .0690548 1.8930784 5.452411 .3992679 .9802982 -.0006098 .0126685 -9.191351 1.3381217
5 "A G C Networks Ltd." 511 .1151079 .1420509 1.9465487 5.182289 .5319744 1.089498 -.2367297 .0193325 -9.93711 1.3835753
5 "A G C Networks Ltd." 512 -.0605018 .1398574 1.9666855 5.291192 .4770609 .9171872 .1151079 .0258063 -10.012973 1.3671902
5 "A G C Networks Ltd." 513 -.1571799 .1039934 1.9659363 5.228753 .5256772 1.3431653 -.0605018 .0527003 -15.934067 1.386089
5 "A G C Networks Ltd." 514 .1819663 .0866767 1.9043994 5.057582 .6238117 1.1347044 -.1571799 .0330671 -13.073632 1.4675443
5 "A G C Networks Ltd." 515 .1331189 .1175809 1.785625 5.224725 .5277671 .8423392 .1819663 .0228892 -18.27487 1.4467278
5 "A G C Networks Ltd." 516 -.153035 .0672813 1.8865 5.349865 .4909091 .8579108 .1331189 .0083676 -14.44452 1.397789
5 "A G C Networks Ltd." 517 -.0721469 .1157434 1.8121196 5.183692 .5796488 .3868339 -.153035 .0217256 -9.040719 1.550805
5 "A G C Networks Ltd." 518 -.176845 .0434617 1.726426 5.108729 .6247742 .0829706 -.0721469 .0481924 -1.276427 1.0308088
5 "A G C Networks Ltd." 519 .0654128 .0687536 1.6099684 4.914198 .7905957 -.27523685 -.176845 .0937928 -8.940083 .9531655
5 "A G C Networks Ltd." 520 .2004708 .1633577 1.6187953 4.977492 .7421286 -.5336088 .0654128 .0379236 -8.3060665 .8918641
5 "A G C Networks Ltd." 521 -.001634 .1845316 1.503249 5.160262 .6181373 -.3614422 .2004708 .0178599 -18.830147 .9789375
6 "Aarti Industries Ltd." 512 -.0592734 .0837323 .54238987 4.332705 1.748526 .6621372 .0717213 4.852063 -2.51095 .2593939
6 "Aarti Industries Ltd." 513 .0670732 .0846717 .3043987 4.272769 1.998985 .730141 -.0592734 19.9523 -1.573169 .27582726
6 "Aarti Industries Ltd." 514 -.0342857 .0633011 .2653327 4.337421 1.873751 .57827497 .0670732 7.869865 -4.0800953 .26782554
6 "Aarti Industries Ltd." 516 .023077 .105984 .2590122 4.32625 2.001283 .6654727 .025641 8.844965 -6.870831 .25357485
6 "Aarti Industries Ltd." 517 -.075188 .1041367 .2115522 4.349245 1.955799 .6445187 .023077 5.612283 -9.697611 -.02749905
6 "Aarti Industries Ltd." 518 -.0345529 .051413 .3444578 4.271095 2.114915 .49636275 -.075188 7.338284 -11.18653 .06150553
6 "Aarti Industries Ltd." 519 .0694737 .1425339 .4426504 4.236567 2.103158 .4036309 -.0345529 20.23219 -11.708984 -.00124911
6 "Aarti Industries Ltd." 521 .2765567 .0532722 .4167594 4.375505 1.830229 .09885359 .0748032 6.17208 -19.861423 -.01269575
7 "Aban Offshore Ltd." 510 -.0145471 .1671569 .786585 4.364499 1.677616 1.2047646 .3487341 .275687 1.4518754 .3233959
7 "Aban Offshore Ltd." 511 .1190476 .1594215 .9475352 4.349761 1.752 1.134144 -.0145471 .1244985 1.7804716 .3959191
7 "Aban Offshore Ltd." 512 -.0719149 .159507 .9419548 4.462223 1.565617 1.1239259 .1190476 .1111811 2.2743268 .413595
7 "Aban Offshore Ltd." 513 .0729023 .0699517 .9861982 4.387636 1.738377 1.481017 -.0719149 .2772467 16.4047 .4144061
7 "Aban Offshore Ltd." 514 .1435898 .0848001 1.111411 4.457945 1.620256 1.344908 .0729023 .2621013 16.005941 .4511779
7 "Aban Offshore Ltd." 515 .107997 .1112043 1.1869622 4.592186 1.416816 1.3816173 .1435898 .2400481 18.010456 .4047494
7 "Aban Offshore Ltd." 516 .1129848 .0938856 1.1931382 4.694736 1.244317 1.5549613 .107997 .164567 21.16874 .4284045
7 "Aban Offshore Ltd." 517 -.0842424 .0936677 1.2640696 4.801805 1.118 1.7029583 .1129848 .1848726 20.92513 .4412946
7 "Aban Offshore Ltd." 518 -.1528789 .0507528 1.4281996 4.713756 1.220847 1.704832 -.0842424 .6507712 .6502991 .6223256
7 "Aban Offshore Ltd." 519 .0609375 .0702427 1.4455564 4.547859 1.448594 .8668396 -.1528789 .7413276 1.863197 .5414214
7 "Aban Offshore Ltd." 520 .4554492 .1726061 1.4533905 4.606968 1.36539 .7503114 .0609375 .4639567 -.680744 .7698011
7 "Aban Offshore Ltd." 521 .4950671 .1588599 1.377457 4.982305 .938123 .6426098 .4554492 .1463009 -14.740382 .8392516
8 "Abbott India Ltd." 510 -.0880134 .0400271 .470697 6.180348 .3274099 .0607995 -.0076526 .0850838 -8.396762 1.0109442
8 "Abbott India Ltd." 511 .0137868 .0384877 .48466095 6.088228 .3590074 .14496128 -.0880134 .1205587 -8.26308 1.193236
8 "Abbott India Ltd." 513 .0564663 .0438338 .3456013 6.097377 .3832787 .1589364 -.0045331 1.718449 -5.416703 1.446219
8 "Abbott India Ltd." 514 -.0103448 .0468644 .28133413 6.152307 .3627931 .1486656 .0564663 .141572 -6.230135 1.4597585
8 "Abbott India Ltd." 515 -.0254355 .0409994 .2814446 6.141908 .3665854 .1533796 -.0103448 .151724 -4.753911 1.7025014
8 "Abbott India Ltd." 516 .0961744 .0455454 .28902167 6.116135 .3687164 .22343 -.0254355 .2158099 -3.2050874 1.6151198
8 "Abbott India Ltd." 517 .0260926 .0724427 .29424652 6.207966 .3363666 .1590225 .0961744 .0152713 -3.329112 1.8826425
8 "Abbott India Ltd." 518 -.1371583 .0446494 .2225012 6.233724 .3480292 .00621077 .0260926 .0122063 -3.963056 .6325775
8 "Abbott India Ltd." 519 -.0471542 .0464687 .29565042 6.027748 .425419 .03717353 -.1371583 .114091 -4.4661913 .5295486
8 "Abbott India Ltd." 520 .0477479 .0512873 .303145 5.979443 .4464721 -.10210545 -.0471542 .0304516 -2.774276 .58890784
8 "Abbott India Ltd." 521 .0789668 .0499418 .3377357 6.026083 .4261255 -.1277718 .0477479 .0181718 -2.489107 .5782541
9 "Accelya Solutions India Ltd." 510 -.352459 .089253 2.504547 4.345881 .6719821 .8174614 -.0400573 .0078417 -7.170419 1.0033356
9 "Accelya Solutions India Ltd." 511 .2543153 .0797726 2.491825 3.911423 1.040046 1.0159469 -.352459 .025954 -5.988738 1.0148292
9 "Accelya Solutions India Ltd." 512 -.1348624 .1268215 2.525519 4.137883 .8291743 .6850929 .2543153 .0175791 -6.570995 1.0163718
9 "Accelya Solutions India Ltd." 513 -.1410393 .0755205 2.5902946 3.99305 .9664899 1.2154334 -.1348624 .0152915 -6.270335 1.0201876
9 "Accelya Solutions India Ltd." 514 .2209877 .0760453 2.539881 3.840956 1.125185 .43133375 -.1410393 .0280572 -2.594969 1.0481699
9 "Accelya Solutions India Ltd." 515 -.041456 .0705575 2.496209 4.040768 .9215369 .2321703 .2209877 .0410044 -7.490185 1.0953172
9 "Accelya Solutions India Ltd." 516 -.2099156 .1099722 2.4161656 3.998384 .9698312 -.0050194 -.041456 .0168685 .4846404 .9823444
9 "Accelya Solutions India Ltd." 517 -.0921228 .0985367 2.3392482 3.762827 1.227503 -.12609863 -.2099156 .0556873 -1.2859864 1.0380969
9 "Accelya Solutions India Ltd." 518 -.3205882 .0484582 2.499194 3.666122 1.352059 -.3019144 -.0921228 .0920183 -8.733504 .901312
9 "Accelya Solutions India Ltd." 519 .1363636 .0632388 2.696745 3.279783 2.03723 -.4818861 -.3205882 .1479528 -1.8722357 .7565902
9 "Accelya Solutions India Ltd." 520 .2038095 .0869685 2.84804 3.407511 1.792762 -.8492668 .1363636 .1763114 8.656399 .7973647
9 "Accelya Solutions India Ltd." 521 .1787975 .2288317 2.633117 3.592919 1.489241 -.720834 .2038095 .0644834 -11.662564 1.1499352
end
format %tm mdate
[/CODE]

[Nick Cox]

Cross-reference to https://www.statalist.org/forums/for...nk-correlation shows suggestions already given.

[Sartaj Hussain]

That doesn't help me. I need to know precisely how to update code to make it useful.

[Nick Cox]

That's evident, but it would be good practice on your part to acknowledge previous attempts to help you, partly as a matter of courtesy and partly because hints you don't understand may make sense to others.