DATA Step, Macro, Functions and more

Variable wise nth highest using SAS

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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
a week ago
Respected Advisor
Posts: 4,130

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. 

View solution in original post


All Replies
PROC Star
Posts: 653

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: 3

Re: Variable wise nth highest using SAS

Each variable.

PROC Star
Posts: 653

Re: Variable wise nth highest using SAS

And how do you require the output? Smiley Happy

New Contributor
Posts: 3

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
a week ago
Respected Advisor
Posts: 4,130

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: 9,856

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;
Respected Advisor
Posts: 4,130

Re: Variable wise nth highest using SAS

@Ksharp

What about ties?

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

Capture.JPG

Super User
Posts: 9,856

Re: Variable wise nth highest using SAS

@Patrick I have already considered about it. Check the code:

if usa not in u then u{_n_}=usa;
Valued Guide
Posts: 947

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 270 views
  • 2 likes
  • 5 in conversation