BookmarkSubscribeRSS Feed
spcoman
Fluorite | Level 6

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;

 

3 REPLIES 3
ballardw
Super User

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;

spcoman
Fluorite | Level 6

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

ChrisNZ
Tourmaline | Level 20

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 . . . .

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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