I have a data like below and I want to calculate the nth highest number for the below variables.
USA AUS ENG
1000 2000 500
3000 1000 200
1500 1500 1000
2200 1100 1500
700 150 900
3000 1000 500
I tried ranks but I couldn't get the output the way required.
Something like below should work
data have;
input USA AUS ENG;
datalines;
1000 2000 500
3000 1000 200
1500 1500 1000
2200 1100 1500
700 150 900
3000 1000 500
;
run;
proc rank data=have ties=dense out=ranked;
var USA AUS ENG;
ranks _rUSA _rAUS _rENG;
run;
data want(drop=_:);
set ranked(where=(_rUSA=2));
set ranked(where=(_rAUS=2));
set ranked(where=(_rENG=2));
output;
stop;
run;
If you have a lot of countries then you could also transpose your source data to a long structure with only two columns (country, value) and then create the output using Proc Rank with By Group processing.
Do you want the n'th highest value for each variable or just for one?
Each variable.
And how do you require the output? 🙂
lets say, I want the second highest of all the variables, The output should be below
USA AUS ENG
2200 1500 1000
Something like below should work
data have;
input USA AUS ENG;
datalines;
1000 2000 500
3000 1000 200
1500 1500 1000
2200 1100 1500
700 150 900
3000 1000 500
;
run;
proc rank data=have ties=dense out=ranked;
var USA AUS ENG;
ranks _rUSA _rAUS _rENG;
run;
data want(drop=_:);
set ranked(where=(_rUSA=2));
set ranked(where=(_rAUS=2));
set ranked(where=(_rENG=2));
output;
stop;
run;
If you have a lot of countries then you could also transpose your source data to a long structure with only two columns (country, value) and then create the output using Proc Rank with By Group processing.
data have;
input USA AUS ENG;
datalines;
1000 2000 500
3000 1000 200
1500 1500 1000
2200 1100 1500
700 150 900
3000 1000 500
;
run;
data want;
set have end=last;
array u{999999} _temporary_;
array a{999999} _temporary_;
array e{999999} _temporary_;
if usa not in u then u{_n_}=usa;
if aus not in a then a{_n_}=aus;
if eng not in e then e{_n_}=eng;
if last then do;
usa=largest(2,of u{*});
aus=largest(2,of a{*});
eng=largest(2,of e{*});
output;
end;
run;
@Patrick I have already considered about it. Check the code:
if usa not in u then u{_n_}=usa;
A little late in the game, but here is a single-pass solution, which gets the 4th highest values for HIGH LOW and CLOSE for IBM stocks in sashelp.stocks:
%let varlist=low high close;
%let NV=%sysfunc(countw(&varlist,%str( )));
%let ord=4;
%macro ordnals_list(size=);
%local size;
%do I=1 %to &size; ,ordnals{_v,&I} %end;
%mend;
options mprint;
data want (DROP=_v);
array ordnals {&nv,&ord} _temporary_;
array vars {&NV} &varlist;
set sashelp.stocks (keep=stock low high close where=(stock='IBM')) END=EOD;
do _V=1 to dim(vars);
if _n_<=&ord then ordnals{_V,_n_}=vars{_V};
else if vars{_V}>ordnals{_V,1} then call sortn(vars{_V} %ordnals_list(size=&ord));
end;
if EOD;
do _V=1 to dim(vars);
vars{_V}=ordnals{_V,1};
END;
run;
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.