topic Re: find variables whose sum minimizes distance with another variable in SAS Programming

find variables whose sum minimizes distance with another variable

ciro — Fri, 21 Sep 2012 14:59:05 GMT

hi everybody,

I need to find the variables in an array (x1-x6) whose sum has a minimum distance with a variable v. for instance to the following dataset

data before;

input x1-x6 v;

cards;

0.279567742 0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3

0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2

0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8

0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333

;

run;

I would like to add a string that indicates which variables provide the most similar value to variable v such as:

data after;

input x1-x6 v r $;

cards;

0.279567742 0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3 011100

0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2 100010

0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8 111000

0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333 111110

;

run;

in case of tie a random choice would be fine.

please consider that the dataset is quite big, so in case of different options I would like suggestions on which is less resource intensive.

any input is very appreciated

thank you very much in advance

Re: find variables whose sum minimizes distance with another variable

Haikuo — Fri, 21 Sep 2012 19:44:40 GMT

I have no idea how you come up with variable 'r', if you just want to find out ' (x1-x6) whose sum has a minimum distance with a variable v', then we could have the following approaches, my guess is that data step one maybe faster:

data before;

input x1-x6 v;

cards;

0.279567742 0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3
0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2
0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8
0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333

;

/*SQL*/

proc sql;

select *, abs(sum(x1,x2,x3,x4,x5,x6)-v) as dis from before having dis=min(dis);

quit;

/*data step*/

data want;

retain m_dis;

array t(6) _temporary_;

set before end=last;

array x x1-x6;

dis=abs(sum(of x1-x6)-v);

if dis < m_dis then do;

do i=1 to dim(t);

t(i)=x(i);

end;

m_dis=min(m_dis,dis);

if last then do;

do i=1 to dim(t);

x(i)=t(i);

end;

output;

end;

drop m_dis i;

run;

Haikuo

Re: find variables whose sum minimizes distance with another variable

ciro — Fri, 21 Sep 2012 20:30:02 GMT

thank you for the suggestions, guys. but sorry, probably my explanation was not very clear.

I need to find for each record the combination of variables (x1-x6) whose sum is closer to variable v.

so for instance for the first record this is obtained by variables (x2,x3,x4) or (x3,x4,x5) an in that case I can accept one of them randomly, say (x2,x3,x4). In this case variable r should be equal to 011100 signaling which are the variables.

for the second record the distance with variable v is minimized by sum(x1,x5) and in this case r should be 100010 and so on

hope this is clearer

Re: find variables whose sum minimizes distance with another variable

art297 — Fri, 21 Sep 2012 21:29:53 GMT

Ciro,

I thought your explanation was quite clear. Here is one way to do it, but I didn't do anything to optimize the code.

The macro to create the various combinations was stolen from: http://support.sas.com/techsup/technote/ts498.html

I didn't bother to optimize it but, rather, just extended it so that it would get all of your combinations in one call, and then used a datastep to convert the resulting file to represent numbers rather than the characters it was designed to work with.

Possibly others can come up with some suggestions regarding how to optimize the code. I also didn't include any comments in my own code, as I just to Chang's code and modified it to fit what I had been thinking.

data before;

input x1-x6 v;

cards;

0.279567742 0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3

0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2

0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8

.2 0.2 0.2 0.2 0.1 0.1 1

0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333

;

run;

%macro combo(r)/parmbuff;

%let i=2;

%let things=;

%do %while (%Qscan(&syspbuff,&i,%STR(,%))) ne );

%let p&i="%Qscan(&syspbuff,&i,%STR(,%)))";

%if &i=2 %then %let things=&&p&i;

%else %let things=&things,&&p&i;

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

%end;

%let n=%eval(&i-2);

data combo;

keep v1-v&r;

array word $8 w1-w&n (&things);

array rr (*) r1-r&r;

array v $8 v1-v&r;

%do r=1 %to 6;

%do i=1 %to &r; /* create the DO LOOPs */

%if &i=1 %then %do;

do r&i=1 to &n-(&r-&i);

%end;

%else %do;

do r&i=r%eval(&i-1)+1 to &n-(&r-&i);

%end;

do k=1 to &r; /* select subscripted items */

v(k)=word (rr(k)); /* for a SAS data set */

end;

output; /* writes to a SAS data set */

%do i=1 %to &r;

end; /* create ENDs for the DO LOOPs */

%end;

run;

%mend combo;

%combo (6,1,2,3,4,5,6)

data combo (keep=v:);

array t[6] $ t1-t6;

array v[6];

set combo (rename=(v1-v6=t1-t6));

do i=1 to 6;

v=input(t,8.);

end;

run;

data after (keep=x1-x6 v r);

array combos_in(*) v1-v6;

array combos[6,63];

array diff[63];

retain combos diff;

j=0;

do until (lastcombo);

set combo end=lastcombo;

j+1;

do i=1 to 6;

combos[i,j]=combos_in;

end;

do until (lastrow);

set before end=lastrow;

array x[1:6] x1-x6;

array xx[6];

retain x xx;

*drop i mindist;

do i=1 to 63;

call missing(of xx(*));

do j=1 to 6;

if not missing(combos[j,i]) then xx=x[combos[j,i]];

end;

diff=abs(sum(of xx

)-v);

end;

mindist = min(of diff

);

do i=1 to 63;

if mindist eq diff then do;

r = repeat("0", 6-1);

do j=1 to 6;

if not missing(combos[j,i]) then substr(r, combos[j,i], 1) = "1";

end;

leave;

end;

output;

end;

run;

Re: find variables whose sum minimizes distance with another variable

PGStats — Fri, 21 Sep 2012 21:51:46 GMT

I assumed that you meant L1 distance but you could change it for L2 easily :

data before;
input x1-x6 v;
cards;
0.279567742 0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3
0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2
0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8
0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333
;
run;

data x(keep=id x1-x6);
array x{6}(1 0 0 0 0 0);
k = 1;
do i = 2 to 2**6;
     id = cats(of x1-x6);
     output;
     rc = graycode(k, of x1-x6);
     end;
run;

data scoring;
set before;
obs + 1; _TYPE_="PARMS"; _NAME_="SUMX";
run;

proc score data=x score=scoring type=PARMS out=scored;
by obs v notsorted;
var x1-x6;
id id;
run;

proc sql;
create table a as
select obs, id
from scored
group by obs
having abs(v-sumx) = min(abs(v-sumx)); /* L1 distance */

create table after(drop=_type_ _name_) as
select s.*, id
from scoring as s, a
where s.obs=a.obs;
quit;

proc sort data=after nodupkey; by obs; run;

Re: find variables whose sum minimizes distance with another variable

Haikuo — Sat, 22 Sep 2012 00:06:26 GMT

Ok, a strenuous approach:

data before;

input x1-x6 v;

cards;

0.279567742 0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3

0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2

0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8

0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333

;

data want;

set before;

array x x1-x6;

/*one element*/

do _0=1 to dim(x);

diff=abs(x(_0)-v);

if diff<min_diff then pos=cats(_0);

min_diff=min(min_diff,diff);

end;

/*two elements*/

do _0=1 to dim(x)-1;

do _1=_0+1 to dim(x);

diff=abs(sum(x(_0),x(_1))-v);

if diff<min_diff then pos=cats(_0,_1);

min_diff=min(min_diff,diff);

end;

/*three elements*/

do _0=1 to dim(x)-2;

do _1=_0+1 to dim(x)-1;

do _2=_1+1 to dim(x);

diff=abs(sum(x(_0),x(_1),x(_2))-v);

if diff<min_diff then pos=cats(_0,_1,_2);

min_diff=min(min_diff,diff);

end;

/*four elements*/

do _0=1 to dim(x)-3;

do _1=_0+1 to dim(x)-2;

do _2=_1+1 to dim(x)-1;

do _3=_2+1 to dim(x);

diff=abs(sum(x(_0),x(_1),x(_2),x(_3))-v);

if diff<min_diff then pos=cats(_0,_1,_2,_3);

min_diff=min(min_diff,diff);

end;

/*Five elements*/

do _0=1 to dim(x)-4;

do _1=_0+1 to dim(x)-3;

do _2=_1+1 to dim(x)-2;

do _3=_2+1 to dim(x)-1;

do _4=_3+1 to dim(x);

diff=abs(sum(x(_0),x(_1),x(_2),x(_3),x(_4))-v);

if diff<min_diff then pos=cats(_0,_1,_2,_3,_4);

min_diff=min(min_diff,diff);

end;

/*Six elements*/

diff=abs(sum(of x(*)));

if diff<min_diff then pos=cats(1,2,3,4,5,6);

min_diff=min(min_diff,diff);

position='000000';

do i=1 to lengthn(pos);

substr(position,substr(pos,i,1),1)='1';

end;

drop _: i diff;

run;

Haikuo

Re: find variables whose sum minimizes distance with another variable

ciro — Mon, 24 Sep 2012 08:16:09 GMT

hi, thank you very much for introducing me all these tools.

I am learning a lot studying your examples. As a statistician (and not very good data step programmer) I liked very much the function graycode and the scoring approach, even if It might be not the more performance wise. I might probably mix some of your ideas.

Re: find variables whose sum minimizes distance with another variable

Ksharp — Mon, 24 Sep 2012 14:20:29 GMT

It looks like I am late.

data have;
input var1-var6 c;
cards;
0.279567742  0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3
0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2
0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8
0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333
;
run;






data x;
input (var1-var6) (: $40.);
cards;
var1 var2 var3 var4 var5 var6
;
run;
proc summary data=x ;
class var1-var6;
output out=temp(drop=_:);
run;
data exp(keep=expression _expression);
set temp end=last;
length expression _expression $ 32767;
retain expression;
expression=catx(' ',expression, catx('+',of var1-var6));
if last then do; _expression=expression;output;end;
run;
data have;
 set have;
if _n_ eq 1 then set exp;
run;

data want;
 set have;
 min=999999;
 array _v{*} var1-var6 ;
 flag=repeat('0',dim(_v)-1);
 do i=1 to dim(_v);
  expression=tranwrd(expression,strip(vname(_v{i})),strip(_v{i}));
 end;

 j=1;
 temp=scan(expression,j,' ');
 do while(not missing(temp));
  sum=resolve(cats('%sysevalf(',temp,')'));
  diff=abs(sum-c); 
  if diff lt min then do; want=scan(_expression,j,' ');min=diff; 
end;
  j+1;
  temp=scan(expression,j,' ');
 end;

 k=1;
 _temp=scan(want,k,'var+');
 do while(not missing(_temp));
 substr(flag,input(_temp,best8.),1)='1';
 k+1;
 _temp=scan(want,k,'var+');
 end;
drop i j k temp _temp sum  expression _expression min;
run;

Ksharp

Re: find variables whose sum minimizes distance with another variable

Haikuo — Mon, 24 Sep 2012 15:03:35 GMT

Thank you, PG for introducing graycode() to us and thank you, OP for initiating such an interesting question. IMHO, the use of graycode() wins hands-down, it saves tremendous brain power and makes the flow of programming like a happy spring.

Here is to use it in data step, just compare the length of my last post, and I am sure it can be better.

data before;

input x1-x6 v;

cards;

0.279567742 0.978487097 0.978487097 0.978487097 0.978487097 0.139783871 3
0.928564286 1.083325 1.083325 1.083325 0.154760714 0 1.2
0.838703226 0.978487097 0.978487097 0.978487097 0.559135484 0 2.8
0.43333 1.011103333 1.011103333 1.011103333 0.86666 0 4.333

;

data want;

set before;

array x x1-x6;

array _y(6) (1 1 1 1 1 1);

_k=0;

do _i=1 to 2**dim(_y)-1;

_sum=0;

_rc=graycode(_k, of _y

);

do _j=1 to dim(_y);

_sum+x(_j)*_y(_j);

end;

_diff=abs(_sum-v);

if _diff < min_diff then pos=cats(of _y(*));

min_diff=min(min_diff,_diff);

end;

drop _:;

run;

proc print;run;

Haikuo

Re: find variables whose sum minimizes distance with another variable

art297 — Mon, 24 Sep 2012 18:10:25 GMT

If it were me I would definitely adopt Haikuo's latest suggested code (i.e., the one using the graycode function). It is parsimonious, easy to read, and runs at least as fast (or faster) than any of the other suggested solutions.

While we all got to learn some new things from PGStat's and Ksharp's solutions, the performance of their respective solutions don't even begin to compare with Haikuo's code.

Re: find variables whose sum minimizes distance with another variable

Linlin — Mon, 24 Sep 2012 18:34:39 GMT

Art,

What is graycode function? I googled but nothing relevant came up. Thank you!

Re: find variables whose sum minimizes distance with another variable

Haikuo — Mon, 24 Sep 2012 18:37:35 GMT

LinLin,

http://support.sas.com/documentation/cdl/en/lefunctionsref/63354/HTML/default/viewer.htm#p1myrepjl958iin1ikhv6515cgp4.htm

Art,

I had to look up 'parsimonious'. Luckily, I probably don't have to take another GRE any more.

Haikuo

Re: find variables whose sum minimizes distance with another variable

Linlin — Mon, 24 Sep 2012 18:39:42 GMT

Thank you Haikuo! - Linlin

Re: find variables whose sum minimizes distance with another variable

art297 — Mon, 24 Sep 2012 18:50:18 GMT

Linlin: take a look at:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a003121538.htm

Just FYI, you should have been able to find it with the following search string on Google:

graycode site:support.sas.com

Re: find variables whose sum minimizes distance with another variable

art297 — Mon, 24 Sep 2012 18:56:28 GMT

Haikuo, I first had 'simplistic' in the sentence but was concerned that the term might be taken the wrong way. I was very impressed with your suggested code.

Re: find variables whose sum minimizes distance with another variable

Linlin — Mon, 24 Sep 2012 18:57:40 GMT

Thank you Art! I searched "graycode function,sas" .

Re: find variables whose sum minimizes distance with another variable

art297 — Mon, 24 Sep 2012 20:22:50 GMT

Linlin: This also would have worked:

"graycode function" sas

Re: find variables whose sum minimizes distance with another variable

MikeZdeb — Mon, 24 Sep 2012 22:32:43 GMT

hi ... never having seen the GRAYCODE function, I was interested in your code versus that posted by Pierre since they differed in the initialization of the 0/1 array and in the initial value of the variable K and I'd wanted to know what happens when those values change (did it make any difference)

I added a few lines of code to your data step and created a data set (HAIKUO) of the 63 different combinations of 0/1 in the array _Y that get used to find the minimum difference

I compared the 63 combinations to those generated by Pierre's posted code (data set PIERRE) ... I think I got this correct ...

* HAIKUO posted code ... make a new data set with ID and _Y1 - _Y6;

data want (drop=_:) haikuo (keep=id _y1-_y6);

set before;

array x x1-x6;

array _y(6) (1 1 1 1 1 1);

_k=0;

do _i=1 to 2**dim(_y)-1;

_sum=0;

_rc=graycode(_k, of _y

);

* create variable ID from current values of _Y1 - _Y6 and write to data set;

id = cats(of _y(*));

if _n_ eq 1 then output haikuo;

do _j=1 to dim(_y);

_sum+x(_j)*_y(_j);

end;

_diff=abs(_sum-v);

if _diff < min_diff then pos=cats(of _y(*));

min_diff=min(min_diff,_diff);

end;

output want;

run;

* Pierre's code ... create 0/1 variables;

data pierre (keep=id x1-x6);

array x{6}(1 0 0 0 0 0);

k = 1;

do i = 2 to 2**6;

id = cats(of x1-x6);

output;

rc = graycode(k, of x1-x6);

end;

run;

* merge data sets, look for differences;

proc sort data=haikuo;

by id;

run;

proc sort data=pierre;

by id;

run;

data not_the_same;

merge haikuo (in=h) pierre (in=p);

by id;

if not (h and p);

run;

there are differences ...

_y1 _y2 _y3 _y4 _y5 _y6 id x1 x2 x3 x4 x5 x6

0 0 0 0 0 0 000000 . . . . . .

. . . . . . 000001 0 0 0 0 0 1

. . . . . . 100001 1 0 0 0 0 1

evaluates the combination '000000' that is not evaluated by Pierre

never use two of the possible combinations (they are used by Pierre)

there is also one combination that gets evaluated twice (a duplicate ... no dups in Pierre's data) ...

proc sort data=haikuo out=h dupout=dp nodupkey;

by id;

run;

data set DP ...

_y1 _y2 _y3 _y4 _y5 _y6 id

0 1 1 1 1 1 011111

my conclusion is that I'm not sure that I understand GRAYCODE ... it remains another of my SAS "gray areas"

ps disclaimer ... I did say that "I think I got this correct"

Re: find variables whose sum minimizes distance with another variable

art297 — Mon, 24 Sep 2012 23:00:29 GMT

Mike: I don't think (again, like you, I'm only thinking on this thread) that Haikuo's code is wrong, If you run PGStat's code over multiple iterations of the same data, sometimes it accepts zeros as part of the needed calculations and sometimes it doesn't.

The graycode function is simply building a set of all combinations. How it is applied, on the other hand, is what I (think) you are questioning.

Re: find variables whose sum minimizes distance with another variable

MikeZdeb — Mon, 24 Sep 2012 23:54:25 GMT

hi ... if you are not sure that all the possible combinations are tested, can you rely on the answer