Solved
New Contributor
Posts: 4

# Variable wise nth highest using SAS

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.

Accepted Solutions
Highlighted
Solution
‎10-07-2017 11:23 AM
Posts: 4,278

## Re: Variable wise nth highest using SAS

[ Edited ]

@vijaysas777zx

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.

All Replies
PROC Star
Posts: 1,071

## Re: Variable wise nth highest using SAS

Do you want the n'th highest value for each variable or just for one?

New Contributor
Posts: 4

Each variable.

PROC Star
Posts: 1,071

## Re: Variable wise nth highest using SAS

And how do you require the output?

New Contributor
Posts: 4

## Re: Variable wise nth highest using SAS

lets say, I want the second highest of all the variables, The output should be below

USA   AUS   ENG
2200 1500   1000

Highlighted
Solution
‎10-07-2017 11:23 AM
Posts: 4,278

## Re: Variable wise nth highest using SAS

[ Edited ]

@vijaysas777zx

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.

Super User
Posts: 10,315

## Re: Variable wise nth highest using SAS

``````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;
``````
Posts: 4,278

## Re: Variable wise nth highest using SAS

@Ksharp

``````data test;
array test {3} 8 (9,9,1);
want=largest(2,of test[*]);
run;
``````

Super User
Posts: 10,315

## Re: Variable wise nth highest using SAS

``if usa not in u then u{_n_}=usa;``
Posts: 1,148

## Re: Variable wise nth highest using SAS

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;``````
☑ This topic is solved.