02-01-2018 10:32 AM - edited 02-01-2018 10:34 AM
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.
'1' = 'Spring 2018'
'2' = 'Fall 2017'
'3' = 'Summer 2017'
'4' = 'Spring 2017'
'5' = 'Fall 2016';
CREATE TABLE top_cities AS SELECT *,count(perm_city) AS city_count FROM transform_1
GROUP BY perm_city ORDER BY city_count DESC;
DATA top10_cities; SET top_cities;
WHERE city_count >= 11;
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.;
02-01-2018 11:20 AM
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;
02-04-2018 08:25 PM
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|