topic Choosing observation based on condition of being closest to target number in SAS Programming

Choosing observation based on condition of being closest to target number

JediApprentice — Fri, 12 May 2017 18:53:10 GMT

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.

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

art297 — Fri, 12 May 2017 19:41:24 GMT

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

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

kiranv_ — Fri, 12 May 2017 19:43:12 GMT

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));