Thank you everyone, I really appreciate the help! I tried the simple SQL code and it worked well, I just need to refine my results a little bit more now that I'm seeing the output. this is the sql code that I ran from PG's answer: %MACRO value(year=,mon=,a=,t=,d=,dataset=); proc sql; create table mylib.&dataset._&year as select * from mylib.master_&year group by security_id, date, mat having abs(&mon-&a) = min(abs(&mon-&a)) or abs(&mon-&o) = min(abs(&mon-&o)) or abs(&mon-&d) = min(abs(&mon-&d)); quit; %MEND value; this is the dataset that I end up with: data mylib.monvalues_2010; infile datalines dsd truncover; input date:DATE9. SECURITY_ID:32. c_vol:32. mat:32. p_vol:32. mon:32.; datalines4; 04JAN2010,100892,,12,1.544622,0.5255413075 04JAN2010,100892,,12,0.602076,0.7988227875 04JAN2010,100892,0.301887,12,0.285932,1.0090393105 04JAN2010,100892,,47,0.749772,0.5255413075 04JAN2010,100892,0.351766,47,0.420842,0.7988227875 04JAN2010,100892,0.304283,47,0.30528,1.0090393105 04JAN2010,100892,0.383761,138,0.398305,0.7988227875 04JAN2010,100892,,138,0.524082,0.5255413075 04JAN2010,100892,0.322661,138,0.333717,1.0090393105 04JAN2010,100892,0.326735,229,0.336752,1.0090393105 04JAN2010,100892,0.362469,229,0.378213,0.8408660921 04JAN2010,100892,,229,0.494307,0.5255413075 04JAN2010,100892,,383,0.474313,0.5255413075 04JAN2010,100892,0.325869,383,0.338696,1.0510826151 04JAN2010,100892,0.367339,383,0.37962,0.8408660921 04JAN2010,100892,0.369302,747,0.394957,0.8408660921 04JAN2010,100892,0.38087,747,0.432982,0.6306495691 04JAN2010,100892,0.348698,747,0.361181,1.0510826151 05JAN2010,100892,,11,0.635303,0.7967313585 05JAN2010,100892,0.300998,11,0.301864,1.0010214505 05JAN2010,100892,,11,1.680322,0.5107252298 05JAN2010,100892,,46,0.786692,0.5107252298 05JAN2010,100892,,46,0.40056,0.7967313585 05JAN2010,100892,0.305781,46,0.309152,1.0010214505 05JAN2010,100892,0.321738,137,0.322548,1.0010214505 05JAN2010,100892,0.380163,137,0.391764,0.7967313585 05JAN2010,100892,,137,0.544914,0.5107252298 ;;;; In one of my macro scenarios i need to add keep the min observation within the interval. I can use an if-then macro but i'm not sure how to add it to this sql code. For the example values d=0.5, t=0.8, a=1, I need to choose the observations with the values closest to 1 and 0.8 but within [0.8,1], and the value closest to 0.5 but within [0.5, 1]. Additionally, c_vol can't be missing for the observation that is selected as the closest to a (but it definitely can be missing for the other values selected), so I need to select the closest to a with a value for c_vol. This doesn't come up in the first 25 obs above, but it does occur in the dataset.
... View more