topic Re: Variable wise nth highest using SAS in SAS Programming

Variable wise nth highest using SAS

vijaysas777zx — Sat, 07 Oct 2017 10:32:04 GMT

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.

Re: Variable wise nth highest using SAS

PeterClemmensen — Sat, 07 Oct 2017 10:34:22 GMT

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

Re: Variable wise nth highest using SAS

vijaysas777zx — Sat, 07 Oct 2017 10:42:17 GMT

Each variable.

Re: Variable wise nth highest using SAS

PeterClemmensen — Sat, 07 Oct 2017 10:44:10 GMT

And how do you require the output? 🙂

Re: Variable wise nth highest using SAS

vijaysas777zx — Sat, 07 Oct 2017 10:51:00 GMT

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

USA AUS ENG
2200 1500 1000

Re: Variable wise nth highest using SAS

Patrick — Sat, 07 Oct 2017 13:09:05 GMT

@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.

Re: Variable wise nth highest using SAS

Ksharp — Sun, 08 Oct 2017 06:36:18 GMT

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;

Re: Variable wise nth highest using SAS

Patrick — Sun, 08 Oct 2017 08:07:54 GMT

@Ksharp

What about ties?

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

Re: Variable wise nth highest using SAS

Ksharp — Mon, 09 Oct 2017 12:05:20 GMT

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

if usa not in u then u{_n_}=usa;

Re: Variable wise nth highest using SAS

mkeintz — Wed, 11 Oct 2017 18:18:28 GMT

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;