order proc report output by the frequency column

[Batman]

I'm trying to order the rows by the frequency column "Count" but this code doesn't seem to work.

PROC REPORT DATA=SURG_CD;                                              

  COL SURG_DX SURG_DX=DESC N;                                          

  DEFINE SURG_DX / GROUP 'ICD-10-PCS CODE';                            

  DEFINE DESC / GROUP 'DESCRIPTION' FORMAT=$ICDDESC.;                  

  DEFINE N / 'COUNT' STYLE(COLUMN)={TAGATTR='FORMAT:#,##0'} ORDER=FREQ;                         

RUN;                                                                   

[ballardw]

From the documentation of the Define statement for Proc Report

ORDER=DATA | FORMATTED | FREQ | INTERNAL

orders the values of a group, order, or across variable according to the specified order.

Your N variable is not a group, order or across variable. If you define it as an ORDER variable you might get what you expect. Maybe. Depends on what you want. Since the N variable is going to be within two other group variables the order would be within those variable groups.

[Tom]

Shouldn't the ORDER=FREQ option be on one of the GROUP variables?

[Cynthia_sas]

Hi:

  Thanks @ballardw for pointing out the PROC REPORT documentation. It's always a good idea to verify that what you want is available "out of the box". But there's always more than one way to do something with PROC REPORT. For example -- using some FAKE data:

Of course you could have used MEANS or TABULATE or even PROC SQL to pre-summarize the data, but I like to give PROC REPORT a chance to do it. Of course, PROC SORT played a part. Notice how the second PROC REPORT differs from the first PROC REPORT (where I created WORK.PASS1):

data fakedata(keep=DX_Code Product Sales);
  set sashelp.shoes ;
  if region in ('United States', 'Canada', 'Western Europe') 
     then DX_code = 'DX 1111.11';
  else DX_Code = 'DX 2222.22';
run;

title; footnote;
  
PROC REPORT DATA=Fakedata out=pass1(drop=_break_); 
  title 'Before: Create PASS1 -- summarize the data';
  COLUMN DX_Code Product N;                                       
  DEFINE DX_Code/ GROUP 'ICD-10-PCS CODE';                            
  DEFINE Product / GROUP 'DESCRIPTION' ;                  
  DEFINE N / 'COUNT';                     
RUN;     

proc sort data=pass1 out=pass1;
  by DX_Code descending N;
run;

PROC REPORT data=pass1;
  title 'AFTER: Display in sorted order';
  COLUMN DX_Code Product N;                                       
  DEFINE DX_Code/ ORDER order=data 'ICD-10-PCS CODE';                            
  DEFINE Product / display 'DESCRIPTION' ;                  
  DEFINE N / display 'COUNT'; 
run; 

I figured you were using ODS EXCEL because of the use of TAGATTR, however, the destination wasn't relevant to the ordering, so I just took the default HTML destination to take the screen shots.

Cynthia

[Tom]

Order=FREQ seems to work when applied in the right place.

So increasing DX_CODE and decreasing FREQ

data fakedata(keep=DX_Code Product Sales);
  set sashelp.shoes ;
  if region in ('United States', 'Canada', 'Western Europe') 
     then DX_code = 'DX 1111.11';
  else DX_Code = 'DX 2222.22';
run;
 
PROC REPORT DATA=Fakedata out=pass1(drop=_break_); 
  title 'Before: Create PASS1 -- summarize the data';
  COLUMN DX_Code Product N;                                       
  DEFINE DX_Code/ GROUP 'ICD-10-PCS CODE' ;                            
  DEFINE Product / GROUP 'DESCRIPTION' order=freq descending;                  
  DEFINE N / 'COUNT';                     
RUN;  

Results

  ICD-10-PCS
  CODE        DESCRIPTION         COUNT
  DX 1111.11  Boot                   18
              Slipper                18
              Sport Shoe             18
              Women's Dress          18
              Men's Dress            17
              Sandal                 16
              Men's Casual           17
              Women's Casual         17
  DX 2222.22  Boot                   34
              Slipper                34
              Sport Shoe             33
              Women's Dress          33
              Men's Dress            33
              Sandal                 33
              Men's Casual           28
              Women's Casual         28

 But why dies it place the Sandal (16) before the Causals (17) ?

It is because there are more total SANDALS across all "DX_CODE" values.

That probably will NOT be a problem for the actual problem since the DESCRIPTIONS should be 1 to 1 with the CODES.

[Cynthia_sas · Accepted solution]

Tom:
It's a "feature". Once the order for product was used for the first group, that same order was used for the second group. That's why I pre-sorted and used ORDER=DATA.
Cynthia