As the first step in the decommissioning of sasCommunity.org the site has been converted to read-only mode.


Here are some tips for How to share your SAS knowledge with your professional network.


Sorting output rows in PROC REPORT

From sasCommunity
Jump to: navigation, search

I had a question the other day about how to sort the rows in PROC REPORT in a way that is not visibly sequential. That's fairly easy to do in PROC TABULATE with ORDER=DATA and PRELOADFMT, but it's a bit harder in PROC REPORT, which doesn't have those options. The example below shows how to do it. The example is contrived.

Let's look at PROC REPORT and SASHELP.CLASS. The code below prints the age values in SASHELP.CLASS and calculates the mean value for each age. It also shows how the value of Age would be printed as an English word, and as a day of the week. As I said, the example is contrived.

  1. title 'Class Heights By Age with Word for Age';
  2. proc report nofs missing data=classplus;
  3.     columns age age=ageword age=ageday name height;
  4.     define age      / order     ;
  5.     define ageword  / order     format=words10.;
  6.     define ageday   / order   format=weekdate3.;
  7.     define height   / display   format=6.1;
  8. run;
Class Heights By Age with Word for Age

        Age         Age  Age  Name      Height
         11  eleven      Tue  Joyce       51.3
                              Thomas      57.5
         12  twelve      Wed  James       57.3
                              Jane        59.8
                              John        59.0
                              Louise      56.3
                              Robert      64.8
         13  thirteen    Thu  Alice       56.5
                              Barbara     65.3
                              Jeffrey     62.5
         14  fourteen    Fri  Alfred      69.0
                              Carol       62.8
                              Henry       63.5
                              Judy        64.3
         15  fifteen     Sat  Janet       62.5
                              Mary        66.5
                              Ronald      67.0
                              William     66.5
         16  sixteen     Sun  Philip      72.0

There are only 6 ages in the data set, and they are sequential, so no day name maps to more than one age.


Here's a simple calculation of the means:

  1. title 'Class Mean Heights By Age';
  2. proc report nofs missing data=sashelp.class;
  3.     columns age height;
  4.     define age      / group     ;
  5.     define height   / mean      format=6.1;
  6. run;
Class Mean Heights By Age

        Age  Height
         11    54.4
         12    59.4
         13    61.4
         14    64.9
         15    65.6
         16    72.0

If we use a format on the group variable, we get the output sorted by the value of the formatted value:

  1. title 'Class Mean Heights By Word for Age';
  2. proc report nofs missing data=sashelp.class;
  3.     columns age height;
  4.     define age      / group     format=words10.;
  5.     define height   / mean      format=6.1;
  6. run;
Class Mean Heights By Word for Age

         Age  Height
  eleven        54.4
  fifteen       65.6
  fourteen      64.9
  sixteen       72.0
  thirteen      61.4
  twelve        59.4

This might not be what you want. If you want the values in the unformatted order, but displayed with a format, you can use both FORMAT= and ORDER=INTERNAL:

  1. title 'Class Mean Heights By Age With Word for Age';
  2. proc report nofs missing data=sashelp.class;
  3.     columns age height;
  4.     define age      / group     format=words10. order=internal;
  5.     define height   / mean      format=6.1;
  6. run;
Class Mean Heights By Age With Word for Age

         Age  Height
  eleven        54.4
  twelve        59.4
  thirteen      61.4
  fourteen      64.9
  fifteen       65.6
  sixteen       72.0


Often, this is all you will need. But suppose you want the rows sorted in a way that is by neither internal value nor sorted value? For example, you might want the rows ordered by the name of the day of the week, but formatted as the word for the value.

One solution is to order the rows by the name of the day of the week, but suppress the printing of that column, so only the word for the value is shown. To do this, reuse the AGE variable as an alias, and sort by the alias. Then, use NOPRINT so the day name doesn't appear:

  1. title 'Class Mean Heights - Alias Version with NOPRINT';
  2. proc report nofs missing data=sashelp.class;
  3.     columns age=ageday age=ageword height;
  4.     define ageday   / group     format=weekdate3.   order=formatted noprint;
  5.     define ageword  / group     format=words10.     ;  
  6.     define height   / mean      format=6.1;
  7. run;
Class Mean Heights - Alias Version with NOPRINT

         Age  Height
  fourteen      64.9
  fifteen       65.6
  sixteen       72.0
  thirteen      61.4
  eleven        54.4
  twelve        59.4


Another method to achieve this result would be to sort the data set in the desired way, and use ORDER=DATA.