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.
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
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));
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.