BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
statmam
Calcite | Level 5

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?                                              *;

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19
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

8 REPLIES 8
data_null__
Jade | Level 19
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
statmam
Calcite | Level 5

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;

Vladislaff
SAS Employee

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

statmam
Calcite | Level 5

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.

Ksharp
Super User

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

Ksharp
Super User

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

statmam
Calcite | Level 5

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?

Ksharp
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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