Help needed in loops for correlation analysis

[Wee Yang Ng]

Hi,

I am using Stata 17 and need some help in my loops. Below is an example of my dataset:

* Example generated by -dataex-. For more info, type help dataex
clear
input double actual_price int(person_1 person_2 person_3) byte person_4 int person_5 byte person_6 int(person_7 person_8 person_9 person_10)
     35  50 30 20 30  45 30  50  85  30 40
   57.1  20 57 50 25  75 25  60  30  28 34
 41.995  30 45 20 35  40 28 100  50  29 55
   34.4  25 55 20 30  65 22  70 100  30 38
 26.295  25 52 20 25  45 30  50  65  25 32
  62.09  50 38 15 35  40 35 100  90  27 44
   21.3  25 17 20 20  30 18  30  30  30 35
 39.995  75 52 40 50  65 50 125  65  60 65
   24.8  40 33 40 22  50 38  70  55  40 50
 86.995  50 75 40 50  70 38  80 115  75 60
   53.1  25 32 19 20  40 22  40  45  30 45
   45.2  75 32 50 50  65 29  70 125  70 65
   56.4  75 65 25 80  80 60  80  80  75 65
   60.1  75 40 20 60  55 45  80 100  50 60
100.295  75 85 60 80  85 50  50  65  74 65
 27.395  15 37 15 24  50 27  50  75  35 50
   25.2 100 70 30 50 100 50 125 100  72 70
   52.1  35 42 15 30  40 28 100  90  38 60
  18.99  25 35 15 20  40 33  50  40  39 50
 55.495  45 80 15 60  75 48  60  95  65 70
 23.295  75 58 20 40  85 55 120  75  63 70
   46.6  20 34 15 15  40 24  50  65  28 55
  24.25  15 34 15 15  40 18  40  45  35 38
  28.11  25 35 15 20  45 38  80  75  30 57
   33.4  45 60 35 50  45 22  50  85  78 65
   42.6  19 28 15 25  30 19  80  75  36 55
 27.445  25 30 20 30  35 17  50  85  67 60
 38.495  65 30 15 35  45 32 120 110  41 55
 33.995  45 32 15 50  35 23  70  90  49 58
 59.775  75 90 50 80  80 42 100 120  75 67
  31.95  15 43 10 15  40 23  40  28  28 51
 67.135 100 60 60 35  65 48 100  65  38 55
 65.495  35 45 15 25  45 36  70  40  63 55
  76.35  25 48 50 30  75 31  50  65  53 45
   41.4  75 60 15 50  75 56 120 100  63 72
  54.65  55 26 20 30  50 23  80  75  40 50
  37.92  50 60 40 40  35 32  80  70 100 72
 23.685  40 29 20 20  40 29  80  60  31 60
  24.59  10 23 15 20  35 16  60  29  23 24
   32.6  15 32 15 15  45 21  50  45  28 35
end

I use the following codes for correlation analysis:

forvalues i = 1/10 {
    corr person_`i' actual_price if auto_id < 41
    putexcel set correlationMatrix1.xls, sheet(Round1 Correlations) modify
    putexcel G`i' = `r(rho)'
}

This codes is for a survey. It worked but what I realised is that once the number of people who participated in the survey changes, I would have to change the values in `i' . The maximum number in `i' would have to change. I would like a loop that does not require me to change it once the number of participants in the survey changes.

Any help in this area would be appreciated. Thanks!

[William Lisowski]

Applied to your example data, this seems to do what you want.

generate auto_id = 40 // omitted from example data
putexcel set correlationMatrix1.xls, sheet(Round1 Correlations) modify
foreach p of varlist person_* {
    corr `p' actual_price if auto_id < 41
    local i : subinstr local p "person_" ""
    putexcel G`i' = `r(rho)'
}
putexcel save
putexcel clear