Help using Base SAS procedures

How to do partial correlation with a do loop macro?

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

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?

Thanks in advance


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

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

Posted in reply to Philanthrope

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

View solution in original post


All Replies
Super User
Posts: 10,023

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

Posted in reply to Philanthrope

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

Respected Advisor
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?

Posted in reply to SteveDenham

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

Respected Advisor
Posts: 2,655

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

Posted in reply to Philanthrope

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.

saladalcoholgrnvegbreadsmilkfmilkpotatofishrmeatbutterbeansfruits
0215574021331639470847350120
0163733812741502537764370123
881748991210793519764510510
35193436075225777631971428
044191128261208572509675104
6644014108891649295546413132
04771823711502671114050331380
032618357104804784126684129
7434320250198513688305014463
364477917223535877135561280
771802769117195136409440140152
014917014381024471123612154
02721874531732759364071760
823511014681291661008473876893
432128043120282529918803555
35861831062532476263528225148
4932910429439118214113840
0428354651731317441442915044
010212273133412855937110189
039818714711024855719291143132
0715024885268125676821194
333991081427926013267888600
372969433211510276702533389
1116616094258250743812314618
01061631826910754926969732
01888847518714466784813695
1372904592682665475598370
0269504362965671077369125
042542350203248291408046174
17430175277692114535673314634
351051284703727712443302010
18871812652751136969748970
0267781819413585267284196
51463310817355135800635759
10027317830196260102987989148153
03338271203237253192736478
04351061841142494611752010199
54178151469116152777947941420
42318195282116691321130394268
035715439225213275905833177
87614370234237155246286546
84348271742021621301674391160
02654441159136291627150106
01810813122042237673757461
34490183211413082633127399
012355329591626669455849165
124001180295738239371195
8223701863519453155844660
86343176202268294139817107025
Solution
‎04-11-2014 10:33 AM
Super User
Posts: 10,023

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

Posted in reply to Philanthrope

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 377 views
  • 10 likes
  • 3 in conversation