topic Re: Proc Freq: How to order row values differently from column values? in SAS Procedures

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

statmam — Wed, 15 Oct 2014 20:07:53 GMT

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

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

data_null__ — Wed, 15 Oct 2014 21:21:18 GMT

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;

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

statmam — Thu, 16 Oct 2014 13:14:59 GMT

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;

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

Vladislaff — Thu, 16 Oct 2014 13:28:59 GMT

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

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

Ksharp — Thu, 16 Oct 2014 13:30:52 GMT

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

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

statmam — Thu, 16 Oct 2014 13:36:53 GMT

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.

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

Ksharp — Thu, 16 Oct 2014 13:37:06 GMT

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

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

statmam — Thu, 16 Oct 2014 14:08:14 GMT

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?

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

Ksharp — Fri, 17 Oct 2014 12:29:09 GMT

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