DATA Step, Macro, Functions and more

Finding Closest Value

Reply
Occasional Contributor
Posts: 15

Finding Closest Value

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
Regular Contributor
Posts: 165

Re: Finding Closest Value

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
Regular Contributor
Posts: 165

Re: Finding Closest Value

On second thought, in step 2 it would probably be better to set col(i) to .
N/A
Posts: 0

Re: Finding Closest Value

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;
Occasional Contributor
Posts: 15

Re: Finding Closest Value

Thanks for the replies.

Looking for some more ideas. Many Thanks...
Valued Guide
Posts: 2,177

Re: Finding Closest Value

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
Contributor
Posts: 28

Re: Finding Closest Value

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;

Super User
Posts: 10,018

Re: Finding Closest Value


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

Ask a Question
Discussion stats
  • 7 replies
  • 2437 views
  • 0 likes
  • 6 in conversation