Create correlation for specific pair of variable and value

Solved
Super Contributor
Posts: 503

Create correlation for specific pair of variable and value

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.

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;
*---------------------------------------------;``````

Accepted Solutions
Solution
‎03-17-2017 08:59 AM
Super User
Posts: 10,698

Re: Create correlation for specific pair of variable and value

```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;

```

All Replies
Super User
Posts: 23,346

Re: Create correlation for specific pair of variable and value

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

Solution
‎03-17-2017 08:59 AM
Super User
Posts: 10,698

Re: Create correlation for specific pair of variable and value

```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;

```
Super Contributor
Posts: 503

Re: Create correlation for specific pair of variable and value

Thank you so much.

HC

Super Contributor
Posts: 503

Re: Create correlation for specific pair of variable and value

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
Super User
Posts: 10,698

Re: Create correlation for specific pair of variable and value

```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_;
end;
end;
end;
run;

```
☑ This topic is solved.

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

Discussion stats
• 5 replies
• 239 views
• 2 likes
• 3 in conversation