BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Rhodochrosite | Level 12

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

5 REPLIES 5
Reeza
Super User

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

Ksharp
Super User
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
Rhodochrosite | Level 12

Thank you so much.

HC

hhchenfx
Rhodochrosite | Level 12
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
Super User
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;



hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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