I would agree with Arthur, arrays are easier for lots of variables as you can then apply the same logic across any number of variables, something like:
data have;
infile datalines dlm=',';
input var1 var2 var3 range1 range2 range3 range4;
datalines;
10,30,15,12,8,14,19
20,35,15,12,8,14,19
16,30,17,12,8,14,19
;
run;
data want;
set have;
array vars{3} 8. var1 var2 var3;
array range{4} 8. range1-range4;
array res{3} 8.;
do i=1 to 3;
do j=2 to 4;
if range{j-1} <= vars{i} <= range{j} then do;
if (vars{i}-range{j-1}) / (range{j}-range{j-1}) < 0.5 then res{i}=range{j-1};
end;
end;
end;
run;
I still think that using arrays in a datastep would be your best solution but, based on the code offered by jh-ti-bw, here is one way that you could do it using a macro:
%macro FIND_CLOSEST_VALUE(limOrDedOption,limorDedValue);
%let numOptions = %Sysfunc(Countw(&limOrDedOption.,%str( )));
limOrDedSetFlag = 'N';
/* probe of less or equal as the smallest value */
if &limOrDedValue. <= %Scan(&limOrDedOption., 1,%str( )) then do;
lwrVal = %Scan(&limOrDedOption., 1,%str( ));
uprVal = %Scan(&limOrDedOption., 1,%str( ));
limOrDedSetFlag = 'Y';
end;
/* probe of equal or greater as the highest value */
else if &limOrDedValue. >= %Scan(&limOrDedOption., -1,%str( )) then do;
lwrVal = %Scan(&limOrDedOption., -1,%str( ));
uprVal = %Scan(&limOrDedOption., -1,%str( ));
limOrDedSetFlag = 'Y';
end;
else do;
/* the value must be between 2 values or equal a value of the list*/
%do curLimOrDed = 2 %to &numOptions.;
if limOrDedSetFlag = 'N' THEN DO;
lwrVal =%Scan(&limOrDedOption., %eval(&curLimOrDed. - 1));
uprVal =%Scan(&limOrDedOption., &curLimOrDed.);
IF (lwrval <= &limOrDedValue.) and (&limOrDedValue.) <= uprVal THEN
limOrDedSetFlag = 'Y';
end;
%end;
end;
if lwrVal eq uprVal then closest=lwrVal;
else do;
if &limOrDedValue.+(uprVal-lwrVal)/2 gt uprVal
then closest=uprVal;
else closest=lwrVal;
end;
drop lwrVal uprVal limOrDedSetFlag;
%mend FIND_CLOSEST_VALUE;
/* usage */
data test;
input testvalue1 testvalue2;
cards;
34000 20
249000 24
249999 25
250000 26
250001 30
251000 31
340000 35
1400000 40
2400000 44
;
data want;
set test;
%FIND_CLOSEST_VALUE(200000 300000 500000 1000000 2000000, testValue1);
closest1=closest;
%FIND_CLOSEST_VALUE(15 20 22 24 26 28 30 32 34 36 38 41, testValue2);
closest2=closest;
drop closest;
run;
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.