Help using Base SAS procedures

Proc Freq: How to order row values differently from column values?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

Proc Freq: How to order row values differently from column values?

My usual mantra is: Anything Excel can do, SAS can do better.  But now I'm not so sure.  I haven't been able to figure out how to get Proc Freq
to order the rows by frequency while leaving the columns in alphabetical order.  Does anyone know how to do this?  Sample data and code shown
below.  Thanks in advance for any ideas you can suggest. 

data ElementarySchoolRegistration;

input id school $10.year;

datalines;

1 Washington 2012

2 Washington 2012

3 Washington 2013

4 Washington 2014

5 Washington 2014

6 Washington 2014

7 Jefferson  2012

8 Jefferson  2012

9 Jefferson  2013

10 Jefferson 2014

11 Lincoln   2013

12 Lincoln   2014

13 Lincoln   2014

;

run;

* Values of row and column variables ordered alphabetically *;

proc freq data=ElementarySchoolRegistration;

tables school*year;

run;

* Values of row and column variables ordered by frequency *;

proc freq data=ElementarySchoolRegistration order=freq;

tables school*year;

run;

* Would like rows ordered by frequency and columns ordered alphabetically *;

* How does one achieve that?                                              *;


Accepted Solutions
Solution
‎10-15-2014 05:21 PM
Respected Advisor
Posts: 3,799

Re: Proc Freq: How to order row values differently from column values?

Like this?

proc summary data=ElementarySchoolRegistration nway;
  
class school / order=freq;
   class year / order=internal;
   output out=counts;
   run;
proc freq order=data;
   tables school*year;
   weight _freq_;
   run;

10-15-2014 4-19-40 PM.png

View solution in original post


All Replies
Solution
‎10-15-2014 05:21 PM
Respected Advisor
Posts: 3,799

Re: Proc Freq: How to order row values differently from column values?

Like this?

proc summary data=ElementarySchoolRegistration nway;
  
class school / order=freq;
   class year / order=internal;
   output out=counts;
   run;
proc freq order=data;
   tables school*year;
   weight _freq_;
   run;

10-15-2014 4-19-40 PM.png
Occasional Contributor
Posts: 7

Re: Proc Freq: How to order row values differently from column values?

Posted in reply to data_null__

Data_Null_, this works great!  Thank you so much.

P.S. I ended up adding one small step to your clever code to get rid of the subtotal records in the counts dataset (because the subtotals were creating extra missing values in the freq step).  But now it's just right.  Thanks again.  

proc summary data=ElementarySchoolRegistration;

class school / order=freq;

class year / order=internal;

output out=counts;

run;

data countswosubtotals;

set counts;

where school ne '' AND year ne .;

run;

proc freq data=countswosubtotals order=data;

tables school*year;

weight _freq_;

run;

SAS Employee
Posts: 15

Re: Proc Freq: How to order row values differently from column values?

Why did you remove the nway option from proc summary? It did just that - removed the subtotals.

Occasional Contributor
Posts: 7

Re: Proc Freq: How to order row values differently from column values?

Posted in reply to Vladislaff

You're right, Vladislaff.  The omission was accidental.  Using nway is certainly a better way to get rid of the subtotals.  Thanks for pointing that out.  I'm learning so much from you all.

Super User
Posts: 10,023

Re: Proc Freq: How to order row values differently from column values?

Just for your data . simply add order=data is OK.

data ElementarySchoolRegistration;
input id school $10.year;
datalines;
1 Washington 2012
2 Washington 2012
3 Washington 2013
4 Washington 2014
5 Washington 2014
6 Washington 2014
7 Jefferson  2012
8 Jefferson  2012
9 Jefferson  2013
10 Jefferson 2014
11 Lincoln   2013
12 Lincoln   2014
13 Lincoln   2014
;
run;
 
* Values of row and column variables ordered alphabetically *;
proc freq data=ElementarySchoolRegistration order=data;
tables school*year;
run;
 


Xia Keshan

Super User
Posts: 10,023

Re: Proc Freq: How to order row values differently from column values?

If you don't know who has the maximize freq , try this one :

proc sql;

create table temp as

select *,count(*) as count from ElementarySchoolRegistration

  group by school

   order by count descending,year;

quit;

* Values of row and column variables ordered alphabetically *;

proc freq data=temp order=data;

tables school*year;

run;

Xia Keshan

Occasional Contributor
Posts: 7

Re: Proc Freq: How to order row values differently from column values?

Thanks for the suggestion, Ksharp.  BTW I'm trying to mark the great answers I've received as correct or helpful so that the original question will be marked answered; however, I'm not seeing the icons to click.  Is there something I have to do, beyond logging in, to get the correct/helpful icons to show up?

Super User
Posts: 10,023

Re: Proc Freq: How to order row values differently from column values?

Ou. That doesn't matter to mark whose answer is correct . as long as you got a solution.

And you don't need to do anything more. It seems that sometime the validated limitation is a couple of hours, once you are out of this range ,You will not find the CORRECT button any more .

Xia Keshan

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 903 views
  • 10 likes
  • 4 in conversation