topic Re: Getting Top and Bottom 5 distinct values in SAS Procedures

Getting Top and Bottom 5 distinct values

jerry898969 — Thu, 03 Feb 2011 14:45:07 GMT

I have 32 variables that I need to get the top 5 and bottom five distinct values. I'm not sure what would be the cleanest way to do this. Any help pointing me in the right direction would be greatly appreciated.

Thank you

Re: Getting Top and Bottom 5 distinct values

Peter_C — Thu, 03 Feb 2011 15:48:43 GMT

proc means
topic IDGROUP

see Example 12: Identifying the Top Three Extreme Values with the Output Statistics
at http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a001016379.htm added link to example Message was edited by: Peter.C

Re: Getting Top and Bottom 5 distinct values

NickR — Thu, 03 Feb 2011 16:01:01 GMT

Alternatively,

You may use proc sql or proc sort to select distinct values, order them by their key variables, and use point option in data step to select the records of your choice.

proc sql noprint;
create table two as select distinct * from one order by var1,var2;
select count(*) into :n from two;
quit;

data three;
do rcrd=1,2,3,4,5,(&n-4),(&n-3),(&n-2),(&n-1),&n;
set two point=rcrd;
output;
end;
stop;
run;

Re: Getting Top and Bottom 5 distinct values

polingjw — Thu, 03 Feb 2011 16:12:43 GMT

You could also easily transpose the dataset and use arrays to find the 5 smallest and largest. Here is an example with a made up dataset:

[pre]
data test;
array var{32};
do x=1 to 1000;
do i=-15 to 16;
var{i+16} = i*ranuni(0);
end;
output;
end;
drop i x;
run;

proc transpose data=test out=test_transpose name=var;
run;

data results;
set test_transpose;
array col{*} col:;
array max_{5};
array min_{5};
do i=1 to 5;
max_{i} = max(of col

);
min_{i} = min(of col

);
do j=1 to dim(col);
if col{j}=min_{i} then col{j}= .;
if col{j}=max_{i} then col{j}=.;
end;
end;
keep var max_: min_:;
run;
[/pre]

Re: Getting Top and Bottom 5 distinct values

jerry898969 — Thu, 03 Feb 2011 16:41:24 GMT

Thank you everyone for your help.

This is how I ended up doing it:

proc sql outobs=5 ;
create table temp_top5
as
select distinct variable1, 'TOP' as source
from temp
order by variable1
;
quit ;

I do the same for the bottom and it gave me what I was looking for. Thank you for all of your suggestions.

Re: Getting Top and Bottom 5 distinct values

polingjw — Thu, 03 Feb 2011 16:59:49 GMT

Are you planning to repeat those two SQL steps for all 32 variables in your dataset? Why use 64 steps to accomplish something that could be done so much more concisely? Or, maybe I just misunderstood your original posting.

Re: Getting Top and Bottom 5 distinct values

Peter_C — Thu, 03 Feb 2011 17:01:19 GMT

does that mean running once for each variable? 32times !

If your input data set will fit in memory, before the 32 runs, do
sasfile work.temp open ;

Re: Getting Top and Bottom 5 distinct values

SASKiwi — Thu, 03 Feb 2011 21:23:01 GMT

Here is another way of doing this:

ods output ExtremeValues = extreme;

proc univariate data = sashelp.class;
var age;
run;

ods output close;

The ODS statement creates a dataset EXTREME with the top and bottom 5 values.

If you have more than one variable just list them in the VAR statement. Message was edited by: SASKiwi

Re: Getting Top and Bottom 5 distinct values

chang_y_chung_hotmail_com — Thu, 03 Feb 2011 21:24:27 GMT

Here is a one-pass solution that does not involve transposing variables. There are many ways to make my solution run even faster, but left as an exercise for those who are interested. 🙂

/* modified polingjw^s test data */

data test;

array var{32} var01-var32;

do x=1 to 1e6;

do i=-15 to 16;

var{i+16} = i*ranuni(0);

end;

output;

end;

drop i x;

run;

/* polingjw */

proc transpose data=test out=test_transpose name=var;

run;

data polingjw;

set test_transpose;

array col{*} col:;

array max_{5};

array min_{5};

do i=1 to 5;

max_{i} = max(of col

);

min_{i} = min(of col

);

do j=1 to dim(col);

if col{j}=min_{i} then col{j}= .;

if col{j}=max_{i} then col{j}=.;

end;

keep var max_: min_:;

run;

/* re-shape to long for comparisons */

data polingjwLong;

set polingjw;

length var $32 type $6 num value 8;

keep var type num value;

array max_(5) max_1 - max_5;

array min_(5) min_1 - min_5;

do num = 1 to 5;

type = "top";

value = max_(num);

output;

end;

do num = 1 to 5;

type = "bottom";

value = min_(num);

output;

end;

run;

/* chang */

%let maxNumVars = 3000;

%let top = 5;

data chang;

set test end=end;

array top(1:&maxNumVars,-&top:&top) _temporary_;

array cur(*) _numeric_;

do k = 1 to dim(cur);

if missing(cur(k)) then leave;

link doTop;

link doBottom;

end;

if end then link doOutput;

return;

doTop:

do j = 1 to &top;

if missing(top(k,j)) then do;

top(k,j) = cur(k);

leave;

end;

if (cur(k) < top(k,j)) then continue;

if (cur(k) = top(k,j)) then leave;

do i = &top to j+1 by -1;

top(k, i) = top(k, i-1);

end;

top(k, j) = cur(k);

leave;

end;

return;

doBottom:

do j = 1 to &top;

if missing(top(k,-j)) then do;

top(k,-j) = cur(k);

leave;

end;

if (cur(k) > top(k,-j)) then continue;

if (cur(k) = top(k,-j)) then leave;

do i = &top to j+1 by -1;

top(k,-i) = top(k,-i+1);

end;

top(k,-j) = cur(k);

leave;

end;

return;

doOutput:

length var $32 type $6;

do k = 1 to dim(cur);

var = vname(cur(k));

type = "top";

do num = 1 to &top;

value = top(k, num);

output;

end;

type = "bottom";

do num = 1 to &top;

value = top(k,-num);

output;

end;

keep var type num value;

return;

run;

proc compare base=polingjwLong comp=chang;

run;

/* on log, in part

NOTE: No unequal values were found. All values compared are exactly equal.

Re: Getting Top and Bottom 5 distinct values

polingjw — Fri, 04 Feb 2011 16:02:30 GMT

Chang,

I’m impressed by your much more efficient program. In a dataset with one million observations, your program runs in about 1/4th the time that mine does. I actually thought about using a multi-dimensional array solution when I first read the original posting but quickly gave up once I started getting confused by my own program. Thanks for demonstrating the better path forward!

Re: Getting Top and Bottom 5 distinct values

Peter_C — Fri, 04 Feb 2011 16:19:37 GMT

polingjw and chang
how does the array approach compare with the ods output approach from SASKIWI at http://support.sas.com/forums/thread.jspa?messageID=49801#49801

peterC
(an interested by-stander)

Re: Getting Top and Bottom 5 distinct values

polingjw — Fri, 04 Feb 2011 16:33:03 GMT

On my computer, Chang’s solution took 32.54 seconds, real time. The univariate procedure took 1:59.84, almost four times as long. However, the results are not equivalent. The original posting asked for the top five and bottom five distinct values. The univariate procedure does not produce distinct results.

Re: Getting Top and Bottom 5 distinct values

Peter_C — Fri, 04 Feb 2011 17:50:58 GMT

thank you
that is a stunning comparison!

Re: Getting Top and Bottom 5 distinct values

data_null__ — Sat, 05 Feb 2011 14:42:34 GMT

As mentioned the DISTINCT restriction means that to do this with PROC SUMMARY would require the data be summarized and then the top and bottom five chosen. With lots of levels of the analysis variables which are used as CLASS variables in the first step this would lose the performance race.

[pre]
%let vars=Age Height Weight;
proc summary data=sashelp.class chartype;
class &vars;
ways 1;
output out=freqs(rename=(_type_=type _freq_=N));
run;
proc print data=freqs;
run;
proc summary data=freqs;
by type;
freq N;
output out=bot idgroup(min(&vars) obs missing out[5](&vars N)=);
output out=top idgroup(max(&vars) obs missing out[5](&vars N)=);
run;
data five;
length position $3;
set bot top indsname=indsname;
by type;
position = scan(indsname,-1);
run;
proc print data=five;
run;
[/pre]

Re: Getting Top and Bottom 5 distinct values

Ksharp — Thu, 17 Feb 2011 04:25:10 GMT

Hi.
polingjw .
You are wrong ,
In the documentation about proc univariate ,there is an option which can select distinct top and bottom value.
If you would ,can search it.
proc univariate is more powerful than you image.

Ksharp

Re: Getting Top and Bottom 5 distinct values

polingjw — Thu, 17 Feb 2011 13:59:46 GMT

Your right. The documentation for the NEXTRVAL=n option on proc univariate states that it

"specifies the number of extreme values that PROC UNIVARIATE lists in the table of extreme values. The table lists the n lowest unique values and the n highest unique values. By default, n=0 and no table is displayed."

I use the univariate procedure rather infrequently. Thanks for pointing out my mistake.

Re: Getting Top and Bottom 5 distinct values

SASKiwi — Thu, 17 Feb 2011 19:42:33 GMT

Thanks for pointing out the NEXTRVAL=n option. It will still be slower to run than the ARRAY technique, but a whole lot quicker to write in the first place. My view of efficiency involves both code development and run times. This UNIVARIATE technique is an example of coding effiiciency being more important than run-time efficiency.

Re: Getting Top and Bottom 5 distinct values

Ksharp — Fri, 18 Feb 2011 06:59:30 GMT

You are welcome. No man can be a perfect coder.

Ksharp

Re: Getting Top and Bottom 5 distinct values

YR — Thu, 09 Jun 2016 06:03:58 GMT

Thank you for sharing this code. Can you advise how using arrays to rename max_1-max_5 based on the columns where information came from?

Re: Getting Top and Bottom 5 distinct values

nar_sas — Sun, 25 Oct 2020 03:35:27 GMT

I tried the UNIVARIATE approach on a wide table of 7500 columns (35 rows), and UNIVARIATE ran in 300s. All other techniques I tried on this table ran much much slower. I think the Univariate approach is very powerful. Here is my code just as an example.

ods listing close;
ods output frequencies=_afreq;
proc univariate data=sashelp.cars NEXTRVAL=20 freq;
var _numeric_;
run;
ods listing;