DATA Step, Macro, Functions and more

How to segment variable based on correlation matrix?

Accepted Solution Solved
Reply
Contributor
Posts: 49
Accepted Solution

How to segment variable based on correlation matrix?

Hi All,

I need to do some segmentation on variables based on correlation matrix. The  correlation matrix is shown as below. If correlation coefficients of some variables in correlation matrix are larger than 0.75 or smaller than -0.75, they will be classified into one subgroup and be stored in one dataset, e.g. dataset_1. Then the variables in dataset_1 will be removed from correlation matrix. Then, if correlation coefficients of some variables in remaining correlation matrix are larger than 0.75 or smaller than -0.75,  they will be classified into another subgroup and be stored in another dataset, e.g. dataset_2. Then the variables in dataset_2 will be removed from correlation matrix....................And repeat until no variables have correlation coefficients of above 0.75 or below -0.75. The remaining variables will be stored in one datsets, dataset_n.

_NAME_V1V2V3V4V5V6V7V8V9V10V11
V110.7952830.6482280.7024340.8143560.8980340.721410.5455730.4105620.675730.79505
V20.79528288510.7851850.8526210.8303910.752020.8175560.6825240.5175090.86710.995331
V30.6482275210.78518510.7114660.6903160.600890.7167860.5522310.508970.6956450.824341
V40.7024337560.8526210.71146610.8460260.6625650.7486020.7016470.4575220.7578020.853448
V50.814356390.8303910.6903160.84602610.8178810.7722930.5236260.3989640.7781010.829205
V60.8980340250.752020.600890.6625650.81788110.7070770.4576410.3295650.6799270.750266
V70.7214095540.8175560.7167860.7486020.7722930.70707710.6040670.5173730.7880030.819051
V80.5455731470.6825240.5522310.7016470.5236260.4576410.60406710.5850930.5840040.681971
V90.4105621590.5175090.508970.4575220.3989640.3295650.5173730.58509310.4850060.522883
V100.6757304080.86710.6956450.7578020.7781010.6799270.7880030.5840040.48500610.860323
V110.7950501390.9953310.8243410.8534480.8292050.7502660.8190510.6819710.5228830.8603231

Is there one solution to do this?

Thanks in advance

MT.


Accepted Solutions
Solution
‎08-07-2012 08:21 AM
Super User
Posts: 9,691

Re: How to segment variable based on correlation matrix?

Are you doing some analysis Like decision tree ?

I pick up 0.2 as correlation coefficient benchmark .

data x;

array x{*} a1-a20;

do j=1 to 100;

do i=1 to dim(x);

x{i}=ranuni(1234);

end;

output;

end;

drop i j;

run;

%let corr=0.2;

proc sql noprint;

select name into : list separated by ' '

  from dictionary.columns

   where libname='WORK' and memname='X';

quit;

%macro decision;

%let i=1;

%do %while(1);

proc corr data=x outp=person(where=(_TYPE_='CORR')) noprint;

var &list ;

run;

data temp;

set person;

array x{*} a: ;

length var $ 40;

do i=1 to dim(x);

  if x{i} eq 1 then leave;

   else if abs(x{i}) ge &corr then do;

                                  corr=x{i};

                                  var=_name_; output;

                                  var=vname(x{i}); output;

                                 end;

end;

keep corr var;

run;

proc sql ;

create table data_&i as select distinct var from temp;

%if &sqlobs = 0 %then %do;

create table data_&i as select _name_ as var from person; quit;

%return;

%end;

select name into : list separated by ' '

  from dictionary.columns

   where libname='WORK' and memname='PERSON' and name not in (select distinct var from temp) and  name not in ('_NAME_' '_TYPE_');

quit;

%let i=%eval(&i+1) ;

%end;

%mend decision;

%decision

Ksharp

消息编辑者为:xia keshan

View solution in original post


All Replies
Solution
‎08-07-2012 08:21 AM
Super User
Posts: 9,691

Re: How to segment variable based on correlation matrix?

Are you doing some analysis Like decision tree ?

I pick up 0.2 as correlation coefficient benchmark .

data x;

array x{*} a1-a20;

do j=1 to 100;

do i=1 to dim(x);

x{i}=ranuni(1234);

end;

output;

end;

drop i j;

run;

%let corr=0.2;

proc sql noprint;

select name into : list separated by ' '

  from dictionary.columns

   where libname='WORK' and memname='X';

quit;

%macro decision;

%let i=1;

%do %while(1);

proc corr data=x outp=person(where=(_TYPE_='CORR')) noprint;

var &list ;

run;

data temp;

set person;

array x{*} a: ;

length var $ 40;

do i=1 to dim(x);

  if x{i} eq 1 then leave;

   else if abs(x{i}) ge &corr then do;

                                  corr=x{i};

                                  var=_name_; output;

                                  var=vname(x{i}); output;

                                 end;

end;

keep corr var;

run;

proc sql ;

create table data_&i as select distinct var from temp;

%if &sqlobs = 0 %then %do;

create table data_&i as select _name_ as var from person; quit;

%return;

%end;

select name into : list separated by ' '

  from dictionary.columns

   where libname='WORK' and memname='PERSON' and name not in (select distinct var from temp) and  name not in ('_NAME_' '_TYPE_');

quit;

%let i=%eval(&i+1) ;

%end;

%mend decision;

%decision

Ksharp

消息编辑者为:xia keshan

🔒 This topic is solved and locked.

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

Discussion stats
  • 1 reply
  • 186 views
  • 0 likes
  • 2 in conversation