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

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

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
  • 1515 views
  • 2 likes
  • 3 in conversation