Think this will work for you, just would need to extend the counter (i.e., 10) to the number of columns. Also, if there is not lower record, you get -999999999.
data Clsst_Amnt_Dt;
infile datalines delimiter='09'x dsd firstobs=1;
input Amount col1 col2 col3 col4 col5 col6 col7 col8 col9 col10;
datalines;
10 8 12 13 56 60 78 87 9 11 5
11 8 12 13 56 60 78 87 9 11 5
84 8 12 13 56 60 78 87 9 11 5
62 8 12 13 56 60 78 87 9 11 5
4 8 12 13 56 60 78 87 9 11 5
57 8 12 13 56 60 78 87 9 11 5
1 8 12 13 56 60 78 87 9 11 5
8 8 12 13 56 60 78 87 9 11 5
88 8 12 13 56 60 78 87 9 11 5
14 8 12 13 56 60 78 87 9 11 5
;
run;
%macro ClsstAmnt;
Data Clsst_Amnt;
Set Clsst_Amnt_Dt;
ClsstAmntChk=-999999999;
%do i=1 %to 10;
if col&i-Amount < 0 then
do;
if col&i-Amount > ClsstAmntChk then
Do;
ClsstAmntChk = col&i-Amount;
ClsstAmnt = col&i;
End;
end;
%end;
run;
%Mend;
%ClsstAmnt;
data Clsst_Amnt_Dt; input Amount col1 col2 col3 col4 col5 col6 col7 col8 col9 col10; datalines; 10 8 12 13 56 60 78 87 9 11 5 11 8 12 13 56 60 78 87 9 11 5 84 8 12 13 56 60 78 87 9 11 5 62 8 12 13 56 60 78 87 9 11 5 4 8 12 13 56 60 78 87 9 11 5 57 8 12 13 56 60 78 87 9 11 5 1 8 12 13 56 60 78 87 9 11 5 8 8 12 13 56 60 78 87 9 11 5 88 8 12 13 56 60 78 87 9 11 5 14 8 12 13 56 60 78 87 9 11 5 ; run; data want; set Clsst_Amnt_Dt; array x{*}Amount col1-col10; array y{*}_Amount _col1-_col10; do i=1 to dim(x); y{i}=x{i}; end; call sortn(of y{*}); do i=1 to dim(x); if y{i}=Amount then do;j=i-1;if j ne 0 then min=y{j};leave;end; end; drop i _:; run;
Xia Keshan
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.