BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
5
4
3
2
1

(/// 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
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
Hi:
If you sort your data and then use ORDER=DATA in the Proc FREQ statement, you can get the kind of table you want.

cynthia
[pre]
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;
output;
return;
datalines;
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
;
run;

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

proc freq data=frqexamp order=data;
tables subgrp * grp ;
run;
[/pre]
deleted_user
Not applicable
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
*/

run;

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

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

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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super 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!)

cynthia

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
  • 4 replies
  • 1009 views
  • 0 likes
  • 3 in conversation