Spearman's correlation in a table

[Jun Yoon]

Hi. I'm trying to calculate the Spearman rank correlation for two of my variables namely V and Price.
The problem is that I want to get the correlation for the two variables by year, which ranges from 1963 to 2013.

I've tried the code

bys year: spearman Veps Price

I'm getting the correlations by year but they are all separated from each other and it would take too long to copy and paste each and every variable to my table.

Is there a way I can get the correlations nicely in a table in the following manner?

year Observation# Price
1963 x correlation1963
1964 y correlation1964
.......
2013 z correlation2013


Thanks!

[Dimitriy V. Masterov]

In cases like this, I either reshape the data or write my own program. In this case, the latter seems pretty easy: you just need to loop over the values of the by variable, calculating the correlation each time.

sysuse auto, clear
capture ssc install levelsof
capture program drop mycorr

program mycorr
syntax varlist(min=2 max=2 numeric), by(varname)

    tempname levels

    qui levelsof `by', local(`levels')

    di _newline(1) "`by' rho"

    foreach i of local `levels' {
        qui spearman `varlist' if `by'==`i', stats(rho)
        di `i' _dup(`=length("`by'")')" " %-9.4f r(rho)
    }

end

mycorr price mpg, by(rep78)

[Nick Cox]

Dimitriy's code is careful enough that no problems will be caused, but note that levelsof is not, and never has been, a package on SSC.

A previous user-written command levels was published on SSC and remains visible there. levels was adopted as an official command within Stata 8 but renamed levelsof in Stata 9, presumably not to compromise intentions of introducing factor variables.

All that said, I would reach for statsby here. Sure, it destroys the dataset in memory, but I get a dataset of results I can manipulate and plot, and so forth.

 
. sysuse auto, clear
(1978 Automobile Data)

. statsby corr=r(rho) N=r(N), by(rep78) : spearman mpg weight
(running spearman on estimation sample)

      command:  spearman mpg weight
         corr:  r(rho)
            N:  r(N)
           by:  rep78

Statsby groups
----+--- 1 ---+--- 2 ---+--- 3 ---+--- 4 ---+--- 5 
.....

. l

     +------------------------+
     | rep78        corr    N |
     |------------------------|
  1. |     1          -1    2 |
  2. |     2   -.9394112    8 |
  3. |     3   -.8382531   30 |
  4. |     4   -.9200891   18 |
  5. |     5   -.9153534   11 |
     +------------------------+

[Dimitriy V. Masterov]

Nick is right. Just remove the line

capture ssc install levelsof