Help using Base SAS procedures

Reorder one axis of a two way table in proc FREQ

Posts: 0

Reorder one axis of a two way table in proc FREQ

I currently have a two way table where each axis is made up of a set of scores from 1 to 5. I am displaying the frequency of each score combination. My rows (say, values of score1) and columns (say, values of score2) are both in ascending order by default.

I want the rows to be in DESCENDING order and the columns to be in ASCENDING order so that I have something like this:

/// 1 2 3 4 5

(/// is just for alignment)

Is this possible to do within proc freq, or is there another way to accomplish this?

Also, is there any way to suppress the 0 in cells with a 0 value and just display a blank cell? Message was edited by: TheWolff
Posts: 8,740

Re: Reorder one axis of a two way table in proc FREQ

If you sort your data and then use ORDER=DATA in the Proc FREQ statement, you can get the kind of table you want.

data frqexamp;
infile datalines;
input grp subgrp;
if grp = 1 then do; output; end;
else if grp = 3 then do; output; output; end;
else if grp = 5 then do; output; output; output; end;
else do; output; output; output; output; end;
1 1
1 2
1 3
1 4
1 5
2 1
2 2
2 3
2 4
2 5
3 1
3 2
3 3
3 4
3 5
4 1
4 2
4 3
4 4
4 5
5 1
5 2
5 3
5 4
5 5

ods listing;
proc sort data=frqexamp;
by grp descending subgrp;

proc freq data=frqexamp order=data;
tables subgrp * grp ;
Posts: 0

Re: Reorder one axis of a two way table in proc FREQ

That worked as far as reordering my rows, but with the way I have my data sorted, it didn't work out exactly how I wanted. Now my columns are unsorted. Let me better explain my code, because that is probably causing the difference.

My scores are actually ranges of scores, so my raw data was a range of scores from say, 301-400 for score1 and 201-900 for score2. Each score combination may occur once, multiple times or not at all, so I have grouped them into ranges.

Also with each score-range combination, there is one of three events tied to it: A, B or C. I want to create three two-way frequency tables, one for each event. So here is a summary of my code:

data raw;
set refined;

if statements here to group into ranges


proc sort data=refined(keep=score1 score2 event) out=sort;
by event descending score1 score2;

proc freq data=sort order=data;
tables score1*score2 / nocol norow;
by event;

I now have 3 freq tables, with score1 in descending order, but for some reason, score2 is unsorted. I'm sure it is because of the way the data is sorted, but I don't know how to tell SAS to sort both rows and columns. I am pretty sure that I need to sort by event first because that is how SAS determines how to separate the 3 tables, so that might be why the order=data isn't working exactly how I want it to. Any ideas?

Also, is there a way to suppress the 0's in the cells with no count and just show those cells to be blank?

Thanks! Message was edited by: TheWolff
Super Contributor
Super Contributor
Posts: 3,174

Re: Reorder one axis of a two way table in proc FREQ

Recommend you consider using PROC FREQ with the OUT=, and use PROC TRANSPOSE to convert desired row values (FREQ generated) to colums, and then use PROC PRINT and the BY and VAR statements to control your column order. With this approach, you can control the missing value condition you desire.

Scott Barry
SBBWorks, Inc.
Posts: 8,740

Re: Reorder one axis of a two way table in proc FREQ

I agree with Scott -- you probably need one pass to make an output dataset and a second procedure to present the information in the particular order you want. My only suggestion beyond Scott's is that as an alternative to PROC PRINT, PROC REPORT allows you to have order variables and the rows could be in descending order while the columns (ACROSS) could be in a differen order -- all this is possible because PROC REPORT has the ORDER= option on the DEFINE statement.

(Just had to put in a plug for PROC REPORT!)

Ask a Question
Discussion stats
  • 4 replies
  • 3 in conversation