Create correlation for specific pair of variable and value

[hhchenfx]

Hi,

I want to calculate correlation for each pair of (variable and value) specified in a seperate file. 

I show the code for 1 combination so you know what I am trying to do.

So could you please help me with that?

Many thanks,

HHC

data have; 
input date a b c d;
datalines;
1 4 4 5 5
2 4 4 5 9
3 4 5 5 0
4 3 6 8 9
5 3 5 0 0
6 4 5 1 2
7 6 5 0 1
;run;

data have_var; 
input a_name $ a_value;
datalines;
a 4
b 5
c 9
d 9
;run;

** RUN FOR THE FIRST 2 CONDITIONS-------------;
data w1; set have;
if a=4 OR b=5; run;

proc corr data=w1 outp=cor1;
var a b; run;

data cor1; set cor1;
keep var1 var2 corr;
lname=lag(_name_);
if _N_=5;
var1=_name_; 
var2=lname;
corr=a; run;
*---------------------------------------------;

[Reeza]

How are you defining the conditions in the second data set? 

[Ksharp · Accepted solution]

It is easy for IML.





data have; 
input date a b c d;
datalines;
1 4 4 5 5
2 4 4 5 9
3 4 5 5 0
4 3 6 8 9
5 3 5 0 0
6 4 5 1 2
7 6 5 0 1
;run;

data have_var; 
input a_name $ a_value;
datalines;
a 4
b 5
c 9
d 9
;run;

proc iml;
use have(drop=date);
read all var _all_ into have[c=vname];
close;

use have_var;
read all var{a_name a_value};
close;

n=nrow(a_name);
do i=1 to n-1;
 do j=i+1 to n;
   temp=have[,a_name[i]]||have[,a_name[j]];
   idx=loc(temp[,1]=a_value[i] | temp[,2]=a_value[j]);
   corr=corr(temp[idx,]);
   var1=var1//a_name[i];
   var2=var2//a_name[j];
   cor=cor//corr[2];
 end;
end;

create want var {var1 var2 cor};
append;
close;
quit;

proc print noobs;run;

[hhchenfx]

Thank you so much.

HC

[hhchenfx]

Hi Ksharp,
Can you please help me to include the information of Total_N (number of record) and N_non_missing (number of record that has both value to be used to calculate correlation)?
I am totally blank with that method.
Thanks a lot.
HHC

[Ksharp]

OK. Here is.



data have; 
input date a b c d;
datalines;
1 4 4 5 5
2 . 5 5 9
3 4 5 5 0
4 3 6 8 9
5 3 5 0 0
6 4 5 1 2
7 6 5 0 1
;run;

data have_var; 
input a_name $ a_value;
datalines;
a 4
b 5
c 9
d 9
;run;

proc iml;
use have(drop=date);
read all var _all_ into have[c=vnames];
close;

use have_var;
read all var{a_name a_value};
close;

n=nrow(a_name);
do i=1 to n-1;
 do j=i+1 to n;
   temp=have[,a_name[i]]||have[,a_name[j]];
   idx=loc(temp[,1]=a_value[i] | temp[,2]=a_value[j]);
   want=temp[idx,];
   corr=corr(want);
   var1=var1//a_name[i];
   var2=var2//a_name[j];
   cor=cor//corr[2];
   Total_N=Total_N//nrow(want);
   N_non_missing=N_non_missing//ncol(loc(countmiss(want,'row')=0));
 end;
end;

create want var {var1 var2 cor Total_N N_non_missing};
append;
close;
quit;



Actually , you could use CALL EXECUTE() to get it.
make a macro to hold your first code and;


data _null_;
 set have_var end=last;
 array name{99999} $ _temporary_;
array value{99999} _temporary_;

name{_n_}=a_name;
value{_n_}=a_value;

if last then do;
 do i=1 to _n_-1;
 do j=i+1 to _n_;
    call execute(YOUR MACRO);
 end;
 end;
end;
run;