Solved
New Contributor
Posts: 4

# How to do partial correlation with a do loop macro?

I have 40 variables for which I want a partial correlation matrix in a way that for each two variables v1 and v2 the remaining variables are partialed out. For three variables I would normally do it as:

Proc corr data=test;

var v1 v2;

partial v3;

run;

I have to repeat it for all the combination that we can get using the method of combination i.e. N=40, k=2. The total combinations turns up to be a lot. Now I am wondering is there a short way of doing it i.e. with a macro do loop etc?

Accepted Solutions
Solution
‎04-11-2014 10:33 AM
Super User
Posts: 10,761

## Re: How to do partial correlation with a do loop macro?

Sure.

```data test;
input  salad     alcohol     grnveg     bread     smilk     fmilk     potato     fish     rmeat     butter     beans     fruits ;
cards;
0     215     57     402     133     163     94     70     84     73     50     120
0     163     73     381     274     150     25     37     76     43     70     123
88     174     89     91     210     79     35     19     76     45     105     10
35     193     43     60     75     225     77     76     31     97     142     8
0     44     191     128     261     208     57     25     0     96     75     104
66     440     14     108     89     164     92     95     54     64     131     32
0     477     182     371     150     267     111     40     50     33     138     0
0     326     18     357     104     80     47     84     12     66     84     129
74     343     20     250     198     5     136     88     30     50     144     63
36     447     79     172     235     35     87     71     35     56     128     0
77     180     27     69     117     195     136     40     94     40     140     152
0     149     170     14     38     102     44     71     12     36     121     54
0     272     187     453     173     27     59     36     40     71     76     0
82     351     101     468     129     166     100     84     73     87     68     93
43     212     80     431     20     282     52     99     18     80     35     55
35     86     183     106     253     247     62     63     52     82     25     148
49     32     9     104     294     39     118     21     41     13     84     0
0     428     35     465     173     131     74     41     44     29     150     44
0     102     12     273     13     34     128     55     93     71     10     189
0     398     187     147     110     248     55     71     92     91     143     132
0     71     50     248     85     268     125     6     76     82     11     94
33     399     108     142     79     260     132     67     8     88     60     0
37     296     94     332     115     102     76     70     25     33     3     89
11     166     160     94     258     250     74     38     12     31     46     18
0     106     163     182     69     107     54     92     69     69     73     2
0     188     88     475     187     144     66     78     48     1     36     95
13     72     90     459     268     266     5     47     55     98     37     0
0     269     50     436     296     5     67     10     7     73     69     125
0     425     42     350     203     24     82     91     40     80     46     174
17     430     175     277     69     21     145     35     67     33     146     34
35     105     128     470     37     277     124     4     3     30     20     10
18     87     181     265     275     113     69     69     7     48     97     0
0     267     78     181     94     13     58     52     67     28     41     96
51     46     33     108     173     55     135     80     0     63     57     59
100     273     178     301     96     260     102     98     79     89     148     153
0     333     8     271     203     237     25     31     92     73     64     78
0     435     106     184     114     249     46     11     75     20     10     199
54     178     151     469     116     152     77     79     47     94     142     0
42     318     195     282     116     69     132     11     30     39     42     68
0     357     154     392     252     1     32     75     90     58     33     177
8     76     14     370     234     237     15     52     46     28     65     46
84     348     27     174     202     162     130     16     74     39     116     0
0     26     54     441     159     136     29     16     2     71     50     106
0     18     108     131     220     42     23     76     73     75     74     61
34     490     183     21     14     130     82     63     31     27     3     99
0     123     55     329     59     162     66     69     45     58     49     165
12     400     11     80     29     57     38     23     9     37     119     5
8     223     70     186     35     194     53     15     58     44     66     0
86     343     176     202     268     294     139     81     71     0     70     25
;
run;

data temp;
set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='TEST')) end=last;
if last then call symputx('n',_n_);
run;
%put &n;
data want;
set temp  end=last;
length var \$ 80 partial \$ 2000;
array x{&n} \$ 40 _temporary_ ;
x{_n_}=name;
if last then do;
do i=1 to &n;
do j=i+1 to &n;
var=catx(' ',x{i},x{j});
do k=1 to &n;
if k ne i and k ne j then  partial=catx(' ',partial,x{k});
end;
output;
call missing(partial);
end;
end;
end;
keep var partial;
run;

data _null_;
set want;
call execute('
proc corr data=test;
var '||trim(var)||';
partial '||trim(partial)||';
run;'
);
run;
```

Xia Keshan

All Replies
Super User
Posts: 10,761

## Re: How to do partial correlation with a do loop macro?

Make a variables combination dataset , then call execute it.

```data test;
array a{*} v1-v10 ;
do i=1 to 100;
do j=1 to dim(a);
a{j}=ranuni(-1);
output;
end;
end;
drop i j;
run;

data temp;
set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='TEST')) end=last;
if last then call symputx('n',_n_);
run;
%put &n;
data want;
set temp  end=last;
length var \$ 80 partial \$ 2000;
array x{&n} \$ 40 _temporary_ ;
x{_n_}=name;
if last then do;
do i=1 to &n;
do j=i+1 to &n;
var=catx(' ',x{i},x{j});
do k=1 to &n;
if k ne i and k ne j then  partial=catx(' ',partial,x{k});
end;
output;
call missing(partial);
end;
end;
end;
keep var partial;
run;

data _null_;
set want;
call execute('
proc corr data=test;
var '||trim(var)||';
partial '||trim(partial)||';
run;'
);
run;

```

Xia Keshan

Message was edited by: xia keshan

Posts: 2,655

## Re: How to do partial correlation with a do loop macro?

If you have access to SAS/STAT, use PROC GLM to get the partial correlation matrix.  Something like:

proc glm data=yourdata;

model v1-v40=;

manova / printe;

run;

quit;

should produce a 40x40 matrix, with the partial correlations needed in the lower triangular.  ODS can be used to get these in a dataset.

Steve Denham

New Contributor
Posts: 4

## Re: How to do partial correlation with a do loop macro?

Many thanks for the advice. I got a correlation matrix with the code. However, just to clarify could you confirm that this approach give me the partial correlation between V1 and V2 controlling for 38 variables, then V1 and V3 and controlling for the rest and so on until I get partial correlation between all possible 2 pairs of variables controlling for the rest. Moreover, probably I won't get Spearman Rank Partial Correlation with proc glm. Is it correct?

Thanks

Posts: 2,655

## Re: How to do partial correlation with a do loop macro?

Yes, that matrix gives the partial Pearson correlation between V(row variable) and V(column variable), holding all others constant.  As you surmised, it does not give the partial Spearman.  You could get it by applying PROC RANK, to rank each of the variables, being sure to use TIES=MEAN, and then applying PROC GLM to the ranked dataset.

Steve Denham

New Contributor
Posts: 4

## Re: How to do partial correlation with a do loop macro?

Many thanks for proposed solution. I am getting various errors (perhaps due to my little understanding of the code). I am pasting a part of the data set (not original), would appreciate if you could run the code with this. It will be helpful for me to understand. These are approx 50 obs.

 salad alcohol grnveg bread smilk fmilk potato fish rmeat butter beans fruits 0 215 57 402 133 163 94 70 84 73 50 120 0 163 73 381 274 150 25 37 76 43 70 123 88 174 89 91 210 79 35 19 76 45 105 10 35 193 43 60 75 225 77 76 31 97 142 8 0 44 191 128 261 208 57 25 0 96 75 104 66 440 14 108 89 164 92 95 54 64 131 32 0 477 182 371 150 267 111 40 50 33 138 0 0 326 18 357 104 80 47 84 12 66 84 129 74 343 20 250 198 5 136 88 30 50 144 63 36 447 79 172 235 35 87 71 35 56 128 0 77 180 27 69 117 195 136 40 94 40 140 152 0 149 170 14 38 102 44 71 12 36 121 54 0 272 187 453 173 27 59 36 40 71 76 0 82 351 101 468 129 166 100 84 73 87 68 93 43 212 80 431 20 282 52 99 18 80 35 55 35 86 183 106 253 247 62 63 52 82 25 148 49 32 9 104 294 39 118 21 41 13 84 0 0 428 35 465 173 131 74 41 44 29 150 44 0 102 12 273 13 34 128 55 93 71 10 189 0 398 187 147 110 248 55 71 92 91 143 132 0 71 50 248 85 268 125 6 76 82 11 94 33 399 108 142 79 260 132 67 8 88 60 0 37 296 94 332 115 102 76 70 25 33 3 89 11 166 160 94 258 250 74 38 12 31 46 18 0 106 163 182 69 107 54 92 69 69 73 2 0 188 88 475 187 144 66 78 48 1 36 95 13 72 90 459 268 266 5 47 55 98 37 0 0 269 50 436 296 5 67 10 7 73 69 125 0 425 42 350 203 24 82 91 40 80 46 174 17 430 175 277 69 21 145 35 67 33 146 34 35 105 128 470 37 277 124 4 3 30 20 10 18 87 181 265 275 113 69 69 7 48 97 0 0 267 78 181 94 13 58 52 67 28 41 96 51 46 33 108 173 55 135 80 0 63 57 59 100 273 178 301 96 260 102 98 79 89 148 153 0 333 8 271 203 237 25 31 92 73 64 78 0 435 106 184 114 249 46 11 75 20 10 199 54 178 151 469 116 152 77 79 47 94 142 0 42 318 195 282 116 69 132 11 30 39 42 68 0 357 154 392 252 1 32 75 90 58 33 177 8 76 14 370 234 237 15 52 46 28 65 46 84 348 27 174 202 162 130 16 74 39 116 0 0 26 54 441 159 136 29 16 2 71 50 106 0 18 108 131 220 42 23 76 73 75 74 61 34 490 183 21 14 130 82 63 31 27 3 99 0 123 55 329 59 162 66 69 45 58 49 165 12 400 11 80 29 57 38 23 9 37 119 5 8 223 70 186 35 194 53 15 58 44 66 0 86 343 176 202 268 294 139 81 71 0 70 25
Solution
‎04-11-2014 10:33 AM
Super User
Posts: 10,761

## Re: How to do partial correlation with a do loop macro?

Sure.

```data test;
input  salad     alcohol     grnveg     bread     smilk     fmilk     potato     fish     rmeat     butter     beans     fruits ;
cards;
0     215     57     402     133     163     94     70     84     73     50     120
0     163     73     381     274     150     25     37     76     43     70     123
88     174     89     91     210     79     35     19     76     45     105     10
35     193     43     60     75     225     77     76     31     97     142     8
0     44     191     128     261     208     57     25     0     96     75     104
66     440     14     108     89     164     92     95     54     64     131     32
0     477     182     371     150     267     111     40     50     33     138     0
0     326     18     357     104     80     47     84     12     66     84     129
74     343     20     250     198     5     136     88     30     50     144     63
36     447     79     172     235     35     87     71     35     56     128     0
77     180     27     69     117     195     136     40     94     40     140     152
0     149     170     14     38     102     44     71     12     36     121     54
0     272     187     453     173     27     59     36     40     71     76     0
82     351     101     468     129     166     100     84     73     87     68     93
43     212     80     431     20     282     52     99     18     80     35     55
35     86     183     106     253     247     62     63     52     82     25     148
49     32     9     104     294     39     118     21     41     13     84     0
0     428     35     465     173     131     74     41     44     29     150     44
0     102     12     273     13     34     128     55     93     71     10     189
0     398     187     147     110     248     55     71     92     91     143     132
0     71     50     248     85     268     125     6     76     82     11     94
33     399     108     142     79     260     132     67     8     88     60     0
37     296     94     332     115     102     76     70     25     33     3     89
11     166     160     94     258     250     74     38     12     31     46     18
0     106     163     182     69     107     54     92     69     69     73     2
0     188     88     475     187     144     66     78     48     1     36     95
13     72     90     459     268     266     5     47     55     98     37     0
0     269     50     436     296     5     67     10     7     73     69     125
0     425     42     350     203     24     82     91     40     80     46     174
17     430     175     277     69     21     145     35     67     33     146     34
35     105     128     470     37     277     124     4     3     30     20     10
18     87     181     265     275     113     69     69     7     48     97     0
0     267     78     181     94     13     58     52     67     28     41     96
51     46     33     108     173     55     135     80     0     63     57     59
100     273     178     301     96     260     102     98     79     89     148     153
0     333     8     271     203     237     25     31     92     73     64     78
0     435     106     184     114     249     46     11     75     20     10     199
54     178     151     469     116     152     77     79     47     94     142     0
42     318     195     282     116     69     132     11     30     39     42     68
0     357     154     392     252     1     32     75     90     58     33     177
8     76     14     370     234     237     15     52     46     28     65     46
84     348     27     174     202     162     130     16     74     39     116     0
0     26     54     441     159     136     29     16     2     71     50     106
0     18     108     131     220     42     23     76     73     75     74     61
34     490     183     21     14     130     82     63     31     27     3     99
0     123     55     329     59     162     66     69     45     58     49     165
12     400     11     80     29     57     38     23     9     37     119     5
8     223     70     186     35     194     53     15     58     44     66     0
86     343     176     202     268     294     139     81     71     0     70     25
;
run;

data temp;
set sashelp.vcolumn(keep=libname memname name where=(libname='WORK' and memname='TEST')) end=last;
if last then call symputx('n',_n_);
run;
%put &n;
data want;
set temp  end=last;
length var \$ 80 partial \$ 2000;
array x{&n} \$ 40 _temporary_ ;
x{_n_}=name;
if last then do;
do i=1 to &n;
do j=i+1 to &n;
var=catx(' ',x{i},x{j});
do k=1 to &n;
if k ne i and k ne j then  partial=catx(' ',partial,x{k});
end;
output;
call missing(partial);
end;
end;
end;
keep var partial;
run;

data _null_;
set want;
call execute('
proc corr data=test;
var '||trim(var)||';
partial '||trim(partial)||';
run;'
);
run;
```

Xia Keshan

🔒 This topic is solved and locked.