computing a similarity matrix

[Rick_SAS]

Please open a new question instead of appending to a question that has been closed for 3 years.

[Ksharp]

Hi. Rick.

I think SQL can get what you want. But Due to your large number of variables, you need another macro to walk through all these variables.

NOTE: This way create about 3000 datasets to hold the similarity ,so it is less efficient than IML.

data a; input investor $ companyID $ wt;
cards;
A IBM 0.50
A MSFT 0.40
A GOOG 0.10
A GRPN 0.00
B IBM 0.40
B MSFT 0.60
B GOOG 0.00
B GRPN 0.00
C IBM 0.50
C MSFT 0.00
C GOOG 0.00
C GRPN 0.50
;
run;


proc sort data=a; by companyID;run;
proc transpose data=a out=temp(drop=_name_);
 by companyid;
 id investor;
 var wt;
run;

options nomprint nomlogic nosymbolgen;

%macro across;
proc sql ;
 select distinct investor from a;
 select distinct investor into :investor1 - :investor&sqlobs. from a;

%let obs=&sqlobs ;
%do i=1 %to %eval(&obs. - 1);
create table _&i as
 select 
          %do j=%eval(&i+1) %to &obs.;
           sum(&&investor&i*&&investor&j)/(sqrt(uss(&&investor&i))*sqrt(uss(&&investor&j))) as &&investor&i..&&investor&j
           %if &j ne &obs. %then %do;,%end;
          %end;
  from temp;
%end;
quit;

%do k=1 %to %eval(&obs. - 1);
data __&k;
length name $ 40;
 set _&k;
 array _x{*} _numeric_;
 do i=1 to dim(_x);
  name=vname(_x{i});value=_x{i};output;
 end;
keep name value;
run;
%end;

data want; set __:; run;
%mend across;

%across

Ksharp

[coug914]

ksharp - just wanted to say thanks once more for all your help on this project. Results came out nicely ...

Rick