DATA Step, Macro, Functions and more

Choosing observation based on condition of being closest to target number

Reply
Frequent Contributor
Posts: 123

Choosing observation based on condition of being closest to target number

[ Edited ]

Here is some sample data:

 

data Have;
  format Target_Cost Total_Factor_Cost dollar10.2 Factor 4.2; 
  input Target_Cost Total_Factor_Cost Factor;
  datalines;
459 487.22 0.81
459 493.23 0.82
459 481.20 0.80
459 499.25 0.83
;
run;

And here is what I want for the output:

 

data Want;
  format Target_Cost Total_Factor_Cost dollar10.2 Factor 4.2; 
  input Target_Cost Total_Factor_Cost Factor;
  datalines;
459 481.20 0.80
;;
run;

What I'm trying to accomplish is: return only the observation where the Total_Factor_Cost is closest to the Target_Cost (out of all the Total_Factor_Costs, which one is closest to the Target_Cost?), whether that be greater than, less than, or equal to the Target_Cost.

PROC Star
Posts: 7,363

Re: Choosing observation based on condition of being closest to target number

Good task for proc sql:

 

data Have;
  format Target_Cost Total_Factor_Cost dollar10.2 Factor 4.2; 
  input Target_Cost Total_Factor_Cost Factor;
  datalines;
459 487.22 0.81
459 493.23 0.82
459 481.20 0.80
459 499.25 0.83
;
run;

proc sql;
  create table want as
    select *
      from have
        having abs(Target_Cost-Total_Factor_Cost) eq min(abs(Target_Cost-Total_Factor_Cost))
  ;
quit;

Art, CEO, AnalystFinder.com

 

PROC Star
Posts: 253

Re: Choosing observation based on condition of being closest to target number

something like this should work

 

proc sql;

select Target_Cost, Total_Factor_Cost, Factor from

(select Target_Cost, Total_Factor_Cost , Factor, abs(Target_Cost-Total_Factor_Cost) as dif

from have

having dif =min(dif));

Ask a Question
Discussion stats
  • 2 replies
  • 105 views
  • 0 likes
  • 3 in conversation