Finding Closest Value

[SASACC]

Hi,

I have a requirement in which I should find closest value of amount.

Ex:
Amount col1 col2 col3 col4 col5 col6 col7 col8 col9 col0 -------col100
10 11 12 13 56 60 78 87 9 11 14 ------ 80

Now I should get value from Col8 as it is 9 and closest to 10 (always closest value that is less than the amount value).
Please let me know how can I achieve this. I have a table of 100,000 from which
i should find closest value of amount in each row.

Thanks in Advance

[RickM]

Not sure if there are any functions to make this easy but a brute force way using arrays is.

1 make an array of col1-col100
2 if col(i) gt amount then col(i)=0
3 closest=max( of col1-col100)

but this is only good if there is atleast one value less than amount and I understand you correctly about wanting the closest value without going over.

Message was edited by: RickM

[RickM]

On second thought, in step 2 it would probably be better to set col(i) to .

[deleted_user]

Hi, the ORDINAL function may be used to do this, but I don't think it is a better method if you have a large amount of variables.

data have;
input amount col1-col5;
cards;
10 11 18 19 9 8
;

data want(drop=i);
set have;
do i=1 to 6;
if i>1 and ordinal(i,of amount--col5)=amount then do;
closest=ordinal(i-1,of amount--col5);
continue;
end;
end;
run;

[SASACC]

Thanks for the replies.

Looking for some more ideas. Many Thanks...

[Peter_C]

try this, within your data step[pre]
array col(50) ;
minG = 9e9 ;
item = . ;
do _i_ = 1 to dim(cols) ;
gap = amount - cols(_i_) ;
if gap < 0 then continue ;
if minG > gap then do;
minG = gap ;
item = _i_ ;
end ;
end ;
putlog 'closest to amount ' amount ' is ' item= cols(item)=
[/pre]
Of course, if you have SAS/IML available, then it could be performed in proc iml with matrix operations instead of array element handling. The syntax would be a whole lot more brief.

Peter

[Bryan]

Think this will work for you, just would need to extend the counter (i.e., 10) to the number of columns.  Also, if there is not lower record, you get -999999999.

data Clsst_Amnt_Dt;
infile datalines delimiter='09'x dsd firstobs=1;
input Amount col1 col2 col3 col4 col5 col6 col7 col8 col9 col10;
datalines;
10 8 12 13 56 60 78 87 9 11 5
11 8 12 13 56 60 78 87 9 11 5
84 8 12 13 56 60 78 87 9 11 5
62 8 12 13 56 60 78 87 9 11 5
4 8 12 13 56 60 78 87 9 11 5
57 8 12 13 56 60 78 87 9 11 5
1 8 12 13 56 60 78 87 9 11 5
8 8 12 13 56 60 78 87 9 11 5
88 8 12 13 56 60 78 87 9 11 5
14 8 12 13 56 60 78 87 9 11 5
;
run;

%macro ClsstAmnt;
Data Clsst_Amnt;
  Set Clsst_Amnt_Dt;
   ClsstAmntChk=-999999999;
   %do i=1 %to 10;
    if col&i-Amount < 0 then
     do;
      if col&i-Amount > ClsstAmntChk then
       Do;
        ClsstAmntChk = col&i-Amount;
        ClsstAmnt = col&i;
       End;
     end;
   %end;
run;
%Mend;
%ClsstAmnt;

[Ksharp]

data Clsst_Amnt_Dt;
input Amount col1 col2 col3 col4 col5 col6 col7 col8 col9 col10;
datalines;
10 8 12 13 56 60 78 87 9 11 5
11 8 12 13 56 60 78 87 9 11 5
84 8 12 13 56 60 78 87 9 11 5
62 8 12 13 56 60 78 87 9 11 5
4 8 12 13 56 60 78 87 9 11 5
57 8 12 13 56 60 78 87 9 11 5
1 8 12 13 56 60 78 87 9 11 5
8 8 12 13 56 60 78 87 9 11 5
88 8 12 13 56 60 78 87 9 11 5
14 8 12 13 56 60 78 87 9 11 5
;
run;
data want;
 set Clsst_Amnt_Dt;
 array x{*}Amount col1-col10;
 array y{*}_Amount _col1-_col10;
 do i=1 to dim(x);
  y{i}=x{i};
 end;
call sortn(of y{*});
 do i=1 to dim(x);
  if y{i}=Amount then do;j=i-1;if j ne 0 then min=y{j};leave;end;
 end;
 drop i _:;
run;

Xia Keshan