topic Re: Proc Univariate for top n largest values for multiple variables (columns) in SAS Programming

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

kk13 — Tue, 07 May 2024 09:41:24 GMT

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

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

kk13 — Tue, 07 May 2024 10:06:23 GMT

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.

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

FreelanceReinh — Tue, 07 May 2024 10:20:11 GMT

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;

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

kk13 — Tue, 07 May 2024 13:04:59 GMT

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.

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

Tom — Tue, 07 May 2024 13:40:09 GMT

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

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

Tom — Tue, 07 May 2024 13:41:12 GMT

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

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

FreelanceReinh — Tue, 07 May 2024 13:56:52 GMT

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