topic Re: Sorting a PROC tabulate by two variables? in SAS Programming

Sorting a PROC tabulate by two variables?

spcoman — Thu, 01 Feb 2018 15:34:56 GMT

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;

Re: Sorting a PROC tabulate by two variables?

ballardw — Thu, 01 Feb 2018 16:20:03 GMT

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;

Re: Sorting a PROC tabulate by two variables?

spcoman — Thu, 01 Feb 2018 17:03:28 GMT

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

Re: Sorting a PROC tabulate by two variables?

ChrisNZ — Mon, 05 Feb 2018 01:25:09 GMT

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	.	.	.	.	1
2	.	.	.	1	.
3	.	.	1	.	.
4	.	1	.	.	.
5	1	.	.	.	.