topic Re: Frequency of observation by descending order in SAS Programming

Frequency of observation by descending order

fdb00004 — Mon, 13 Jul 2020 23:34:29 GMT

Hello Guys,

I would like to sort my table based on frequency od values. This is my table and code

Data df;
    Input Players $  Clubs 9-25$;
    datalines;
Ronaldo     ManUnited
Ronaldo     ManUnited
Ronaldo     RealMadrid
Suarez      Liverpool
Suarez      Liverpool
;
run;

proc freq data=df;
table Players*Clubs / out=counts;
run;
proc sort data=counts;
by players descending Count;
run;

I have tried this code but no result. Below is my desired output.

Players     Clubs
Ronaldo     ManUnited
Suarez      Liverpool

Thanks.

Re: Frequency of observation by descending order

Reeza — Mon, 13 Jul 2020 21:06:21 GMT

The RealMadrid line disappears? What is the logic to remove that line?

Re: Frequency of observation by descending order

ballardw — Mon, 13 Jul 2020 21:55:15 GMT

The code you show will not generate the output. Your data step will create the Clubs with only 8 characters, so the result will be "ManUnite".

And correcting that the result will have 3 records.

Players      Clubs       COUNT    PERCENT

Ronaldo    ManUnited       2         50
Ronaldo    RealMadrid      1         25
Suarez     Liverpool       1         25

So you have likely done more than you are showing in the code.

Re: Frequency of observation by descending order

fdb00004 — Mon, 13 Jul 2020 23:41:50 GMT

My mistake. I have edited the code. RealMadrid should not appear but I only want rows with 2 or more frequent outcome like Liverpool to appear on the table.

Re: Frequency of observation by descending order

Reeza — Tue, 14 Jul 2020 02:26:45 GMT

You can add data set options to any data set.
This adds a where filter to your summary so only records with 2 or more counts are displayed.

proc freq data=df;
table Players*Clubs / out=counts (where = (counts >=2));
run;