Sorting a PROC tabulate by two variables?

Reply
Occasional Contributor
Posts: 14

Sorting a PROC tabulate by two variables?

[ Edited ]

How can I can I sort a table by two variables?

 

I have a table that starts as a PROC SQL to create the table and variables, moves through a DATA step to limit the range of data, and then into a PROC TABULATE to present the information. I ordered the data in descending order for my numeric variable in the PROC SQL step and passed that to my PROC TAB using ORDER=DATA. This works fine.

 

However, in my PROC TAB I also need my top row of variables to be in order of semester (Fall 2015, Spring 2016, Summer 2017 etc.) with my numeric variable still in descending order. By default it sorts my top row alphabetically. I tried creating a PROC FORMAT and adding that to the PROC TAB but it doesn't work (code sample below). I've also tried some various combinations to no avail.

 

 

PROC FORMAT;
VALUE $term_order
'1' = 'Spring 2018'
'2' = 'Fall 2017'
'3' = 'Summer 2017'
'4' = 'Spring 2017'
'5' = 'Fall 2016';
RUN;

PROC SQL;
CREATE TABLE top_cities AS SELECT *,count(perm_city) AS city_count FROM transform_1
GROUP BY perm_city ORDER BY city_count DESC;
quit;

DATA top10_cities; SET top_cities;
WHERE city_count >= 11;
run;

PROC TABULATE FORMAT=COMMA10.0 DATA= top10_cities ;
CLASS perm_city semester;
TABLE perm_city = 'County' ALL= 'Total',semester = 'Semester' ALL='Total';
TITLE 'Top Event Attendee Cities' '(Based on Attendee Home Address)';
FORMAT semester $term_order.;
run;

 

Super User
Posts: 12,148

Re: Sorting a PROC tabulate by two variables?

With proc tabulate you can provide different ORDER = option for each class variable.

 

perhaps this will work for your data:

 

class perm_city /order=data;

class semester /order=unformatted;

Occasional Contributor
Posts: 14

Re: Sorting a PROC tabulate by two variables?

[ Edited ]

No, it's still sorting my semester variable alphabetically. 

PROC Star
Posts: 1,926

Re: Sorting a PROC tabulate by two variables?

The order option works.

proc format;
  value $term_order
  '1' = 'Spring 2018'
  '2' = 'Fall 2017'
  '3' = 'Summer 2017'
  '4' = 'Spring 2017'
  '5' = 'Fall 2016';
run;

data TOP10_CITIES; 
  do PERM_CITY=1 to 5; SEMESTER=cat(PERM_CITY); output; end;
run;

proc tabulate data=TOP10_CITIES ;
  class PERM_CITY ;
  class SEMESTER / order=unformatted descending;
  table PERM_CITY,SEMESTER=''*n='';
  format SEMESTER $term_order.;
run;
\
  Fall 2016 Spring 2017 Summer 2017 Fall 2017 Spring 2018
PERM_CITY . . . . 1
1
2 . . . 1 .
3 . . 1 . .
4 . 1 . . .
5 1 . . . .
Ask a Question
Discussion stats
  • 3 replies
  • 94 views
  • 0 likes
  • 3 in conversation