order=freq is not working with PROC REPORT

[Tom]

---

@yuwda01 wrote:

Hi,

Any update on this?

Thanks David

---

Please post a small example of the input and what you want as output with an explanation of what the output means.  Either post the data in the form of a data step, or take the time to make an example using one of the available datasets in SASHELP library.  Like SASHELP.CLASS, a small dataset with both numeric and character variables.

[yuwda01]

OPTIONS LS=132 SOURCE SOURCE2 NODATE NOFMTERR;                     
data log;                                                          
   infile datalines;                                               
   input @1  date  $8.                                             
         @19 replyqm $10.                                          
         ;                                                         
datalines;                                                         
20190929 05293375 WCCPLX14                                         
20190929 05302342 WCCPLX83                                         
20190929 05445041 WCCPLX11                                         
20190929 05554784 WCCPLX19                                         
20190929 05590764 WCCPLX17                                         
20190929 05593023 WCCPLX18                                         
20190929 05593233 WCCPLX14                                         
20190929 05594399 WCCPLX82                                         
20190929 05595242 WCCPLX16                                         
20191001 00013065 WCCPLX79                                         
20191001 00020855 WCCPLX79                                         
20191001 00020900 WCCPLX19                                         
20191001 00021958 WCCPLX11                                         
20191001 00022138 WCCPLX71                                         
20191001 00022927 WCCPLX73                                         
20191001 00023551 WCCPLX19                                         
20191001 00023930 WCCPLX17                                         
20191001 00024419 WCCPLX77                                         
20191001 00024556 WCCPLX11                                         
20191001 00032912 WCCPLX79                                         
20191001 00035343 WCCPLX71                                         
20191001 00040903 WCCPLX12                                         
20191001 00041510 WCCPLX18                                         
run;                                                               
DATA TEMP2;                                                        
  SET LOG;                                                         
  NUM = 1 ;                                                        
proc report data =temp2 headline headskip;                         
column date replyqm num;                                           
define date / group;                                               
define replyqm / group order=freq descending;                      
define num / analysis sum 'COUNT';                                 
break after date / ol skip summarize suppress;                     
rbreak after / dol skip summarize;                                 
run;                                                               

[yuwda01]

the output I am getting...

         The SAS System          
                                 
date      replyqm         COUNT  
-------------------------------  
                                 
20190929  WCCPLX11            1  
          WCCPLX19            1  
          WCCPLX14            2  
          WCCPLX17            1  
          WCCPLX18            1  
          WCCPLX83            1  
          WCCPLX82            1  
          WCCPLX16            1  
                      ---------  
                              9  
                                 
20191001  WCCPLX11            2  
          WCCPLX19            2  
          WCCPLX79            3  
          WCCPLX17            1  
          WCCPLX18            1  
          WCCPLX71            2  
          WCCPLX73            1  
          WCCPLX77            1  
          WCCPLX12            1  
                      ---------  
                             14  
                                 
                      =========  
                             23  

and I expected   the list of RPLYQM will list in "order=freq descending" on Frequency.

So I want to get output likes this.

WCCPLX14 for 10/29 should be first in the list because it has two occurrences. 

         The SAS System        
                               
date      replyqm         COUNT
-------------------------------
                               
20190929  WCCPLX14            2
          WCCPLX19            1
          WCCPLX11            1
          WCCPLX17            1
          WCCPLX18            1
          WCCPLX83            1
          WCCPLX82            1
          WCCPLX16            1
                      ---------
                              9
                               
20191001  WCCPLX79            3
          WCCPLX19            2
          WCCPLX71            2
          WCCPLX17            1
          WCCPLX18            1
          WCCPLX11            1
          WCCPLX73            1
          WCCPLX77            1
          WCCPLX12            1
                      ---------
                             14
                               
                      =========
                             23

[yuwda01]

Hi,

Any update on this issue?

Thanks David

[yuwda01]

Hi,

I have provided all the information you have requested.   Are you still waiting on more information to determine this root cause of this issue?

Thanks David

[Tom]

You appear to be trying to ask PROC REPORT to sort a column based on the value of a different column. And worse one that doesn't exist yet.

Just count first and then report.

data log;
  input date :$8. replyqm :$10.;
datalines;
20190929 WCCPLX14
20190929 WCCPLX83
20190929 WCCPLX11
20190929 WCCPLX19
20190929 WCCPLX17
20190929 WCCPLX18
20190929 WCCPLX14
20190929 WCCPLX82
20190929 WCCPLX16
20191001 WCCPLX79
20191001 WCCPLX79
20191001 WCCPLX19
20191001 WCCPLX11
20191001 WCCPLX71
20191001 WCCPLX73
20191001 WCCPLX19
20191001 WCCPLX17
20191001 WCCPLX77
20191001 WCCPLX11
20191001 WCCPLX79
20191001 WCCPLX71
20191001 WCCPLX12
20191001 WCCPLX18
;                                                               

proc freq data=log ;
  tables date*replyqm / noprint out=counts;
run;

proc print; 
run;

options formchar="|----|+|---+=|-/\<>*";
proc report data=counts headline ;
  column date count=orderby replyqm count ;
  define date / group ;
  define orderby / group descending noprint;
  define replyqm / group  ;
  define count / sum ;
  break after date / ol skip summarize suppress;
  rbreak after / dol skip summarize;
run;

                        Frequency
  date      replyqm         Count
  -------------------------------
  20190929  WCCPLX14            2
            WCCPLX11            1
            WCCPLX16            1
            WCCPLX17            1
            WCCPLX18            1
            WCCPLX19            1
            WCCPLX82            1
            WCCPLX83            1
                        ---------
                                9

  20191001  WCCPLX79            3
            WCCPLX11            2
            WCCPLX19            2
            WCCPLX71            2
            WCCPLX12            1
            WCCPLX17            1
            WCCPLX18            1
            WCCPLX73            1
            WCCPLX77            1
                        ---------
                               14

                        =========
                               23

[yuwda011]

Hi,

I understand that you have provided alternate solution for my request.  But I still would like to get example on how to use order=freq to solve my request. 

Thanks David