Proc Univariate for top n largest values for multiple variables (columns)

[kk13]

I have a data set with multiple variables (columns).  I'm trying to get the top 10 values of each variable.  However, I would like the output to be in a new dataset with a new variable called: variable_names.  The row shows the top 10 values from smallest to largest.

The data below is just an example.  I have more than 4 input variables.  Here, I am just looking at the top 3.  For a bigger data set, I may look at the top 10. 

data example;
input a b c d;
datalines;
100 120 123 140
12 23 43 42
12 12 23 23
5 7 5 2
80 88 98 2
3 4 101 4
;

%let nTop = 3;  
proc univariate data = new_data NExtrObs=&nTop;
ods select ExtremeObs;
run;

The proc univariate gives a separate output for each. 

The UNIVARIATE Procedure

Variable: a

Extreme Observations

Lowest        Highest

Value Obs Value Obs

3          6        12       3

5          4        80    5

12        3         100  1

I would like to create a new data set where output with column names: Variables and the top values.  For example,

Variables   Top1 Top2 Top3

a                    12      80      100

b                    23      88      120

c                     5         6        1     

[kk13]

I forgot to add that I would like to look at the top 10 distinct values.  The proc univariate gives does not give out the top distinct values.

[FreelanceReinh]

Hello @kk13,

Use the NEXTRVAL= option: 

%let nTop = 3;  
ods select none;
ods output ExtremeValues=extrval(keep=varname high);
proc univariate data=example NExtrVal=&nTop;
run;
ods select all;

proc transpose data=extrval out=want(drop=_:) prefix=Top;
by varname;
run;

[kk13]

Is there an option to get the number of count in the top 3. For example, the variable a has 2 counts of 12, 1 count of 80, and 1 count of 100.

[Tom]

The count is there (remove the KEEP= dataset option from the ODS OUTPUT statement).

[FreelanceReinh · Accepted solution]

---

@kk13 wrote:
Is there an option to get the number of count in the top 3. For example, the variable a has 2 counts of 12, 1 count of 80, and 1 count of 100.

---

You can keep the additional variable HighFreq in the EXTRVAL dataset and then use PROC SUMMARY instead of PROC TRANSPOSE to create dataset WANT:

%let nTop = 3;  
ods select none;
ods output ExtremeValues=extrval(keep=varname high highfreq);
proc univariate data=example NExtrVal=&nTop;
run;
ods select all;

proc summary data=extrval;
by varname;
output out=want(drop=_:) idgroup(out[&nTop] (high:)=);
run;

[Tom]

Add the NOTSORTED option to the BY statement in the PROC TRANSPOSE step if your variables are not in the dataset in alphabetical order.