topic Re: How to do partial correlation with a do loop macro? in SAS Procedures

How to do partial correlation with a do loop macro?

Philanthrope — Fri, 11 Apr 2014 07:54:03 GMT

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

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

Ksharp — Fri, 11 Apr 2014 11:02:06 GMT

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

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

SteveDenham — Fri, 11 Apr 2014 12:42:23 GMT

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

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

Philanthrope — Fri, 11 Apr 2014 13:41:38 GMT

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

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

Philanthrope — Fri, 11 Apr 2014 13:48:27 GMT

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

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

Ksharp — Fri, 11 Apr 2014 14:33:14 GMT

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

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

SteveDenham — Fri, 11 Apr 2014 14:46:55 GMT

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