order=freq is not working with PROC REPORT

[yuwda01]

Hi,

I am using order=freq in PROC REPORT and expect report will be displayed in descending order with frequency on REPLYQM.

from the output below you can tell it is not in right order.

Thanks David

proc report data =temp2 nofs headline headskip;             
column date num;                                            
define date / group;                                        
define num / analysis sum 'COUNT';                          
run;                                                        
proc report data =temp2 headline headskip;                  
column date replyqm num num=num_1;                          
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;                                                        

date      replyqm         COUNT        NUM 
------------------------------------------ 
                                           
                      ---------  --------- 
                              6          6 
                                           
20191022  WCCPLX76            8          8 
          WCCPLX11            6          6 
          WCCPLX12            5          5 
          WCCPLX18            8          8 
          WCCPLX78            6          6 
          WCCPLX77            5          5 
          WCCPLX82            7          7 
          WCCPLX75            8          8 
          WCCPLX73            8          8 
          WCCPLX17            7          7 
          WCCPLX81            5          5 
          WCCPLX80            6          6 
          WCCPLX15            7          7 
          WCCPLX83            8          8 
          WCCPLX13            7          7 
          WCCPLX71            7          7 
          WCCPLX19            6          6 
          WCCPLX16            6          6 
          WCCPLX79            4          4 
          WCCPLX20            5          5 
          WCCPLX74            4          4 
          WCCPLX14            3          3 
          WCCPLX72            3          3 
          WCCPLX36            1          1 
                      ---------  --------- 
                            140        140      

[PaigeMiller]

please paste the text of the output from PROC REPORT into the window that appears when you click on the {i} icon. THis will preserve the formatting of the output, and make it readable.

[yuwda01]

proc report data =temp2 nofs headline headskip;              
column date num;                                             
define date / group;                                         
define num / analysis sum 'COUNT';                           
run;                                                         
proc report data =temp2 headline headskip;                   
column date replyqm num num=num_1;                           
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;                           

[PaigeMiller]

---

@yuwda01 wrote:

proc report data =temp2 nofs headline headskip;              
column date num;                                             
define date / group;                                         
define num / analysis sum 'COUNT';                           
run;                                                         
proc report data =temp2 headline headskip;                   
column date replyqm num num=num_1;                           
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;                           

---

I would like to see the OUTPUT of PROC REPORT pasted into the {i} window.

[yuwda01]

date      replyqm         COUNT        NUM  
------------------------------------------  
                                          
 
                                            
20191022  WCCPLX76            8          8  
          WCCPLX11            6          6  
          WCCPLX12            5          5  
          WCCPLX18            8          8  
          WCCPLX78            6          6  
          WCCPLX77            5          5  
          WCCPLX82            7          7  
          WCCPLX75            8          8  
          WCCPLX73            8          8  
          WCCPLX17            7          7  
          WCCPLX81            5          5  
          WCCPLX80            6          6  
          WCCPLX15            7          7  
          WCCPLX83            8          8  
          WCCPLX13            7          7  
          WCCPLX71            7          7  
          WCCPLX19            6          6  
          WCCPLX16            6          6  
          WCCPLX79            4          4  
          WCCPLX20            5          5  
          WCCPLX74            4          4  
          WCCPLX14            3          3  
          WCCPLX72            3          3  
          WCCPLX36            1          1  
                      ---------  ---------  
                            140        140     

[ballardw]

Example input data is likely going to be needed to answer this.

Since you are showing the SUM of variable Num under a label of "Count" it is very likely that if any of your values for Num are 0 that the frequency used for ordering in the variable replyqm could be higher than the displayed Sum.

[Tom]

Looks to me like 140 is larger than 6.

What is the question?

[yuwda01]

Thanks for the quick response. 

define replyqm / group order=freq descending;

I thought this statement is sorting FREQ on number of occurrence on REPLYQM.

the report shows

20191022  WCCPLX76            8          8 
          WCCPLX11            6          6 
          WCCPLX12            5          5 
          WCCPLX18            8          8 
          WCCPLX78            6          6 
          WCCPLX77            5          5 
          WCCPLX82            7          7 
          WCCPLX75            8          8 

and I expect all number 8 should be on top then followed by 7,6,5

20191022  WCCPLX76            8          8 

          WCCPLX18            8          8

          WCCPLX82            7          7 
          WCCPLX11            6          6  
          WCCPLX78            6          6 
          WCCPLX77            5          5  
          WCCPLX12            5          5

Thanks David 

[ballardw]

---

@yuwda01 wrote:

Thanks for the quick response. 

define replyqm / group order=freq descending;

I thought this statement is sorting FREQ on number of occurrence on REPLYQM.

the report shows

20191022  WCCPLX76            8          8 
          WCCPLX11            6          6 
          WCCPLX12            5          5 
          WCCPLX18            8          8 
          WCCPLX78            6          6 
          WCCPLX77            5          5 
          WCCPLX82            7          7 
          WCCPLX75            8          8 

and I expect all number 8 should be on top then followed by 7,6,5

20191022  WCCPLX76            8          8 

          WCCPLX18            8          8

          WCCPLX82            7          7 
          WCCPLX11            6          6  
          WCCPLX78            6          6 
          WCCPLX77            5          5  
          WCCPLX12            5          5

Thanks David 

---

Can't diagnose why output is not as expected without the input data.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

[yuwda01]

OPTIONS LS=132 SOURCE SOURCE2 NODATE NOFMTERR;      
data log;                                           
   infile"('ZKQMMM9.REPORT(TEST)')";                
   input @1  date  $8.                              
         @19 replyqm $10.                           
         ;                                          
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;                                                

         The SAS System                                                          1

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                   20190929  WCCPLX83            1
                                                             WCCPLX18            1
                                                             WCCPLX11            1
                                                             WCCPLX17            1
                                                             WCCPLX82            1
                                                             WCCPLX19            1
                                                             WCCPLX14            2
                                                             WCCPLX16            1
                                                                         ---------
                                                                                 9

                                                   20191001  WCCPLX83            2
                                                             WCCPLX18            2
                                                             WCCPLX78            3
                                                             WCCPLX80            1
                                                             WCCPLX11            3
                                                             WCCPLX17            3
                                                             WCCPLX19            3
                                                             WCCPLX77            4
                                                             WCCPLX76            1
                                                             WCCPLX12            1
                                                             WCCPLX13            1
                                                             WCCPLX73            2
                                                             WCCPLX72            1
                                                             WCCPLX79            5
                                                             WCCPLX74            3
                                                             WCCPLX75            1
                                                             WCCPLX71            5
                                                             WCCPLX20            1
                                                             WCCPLX14            1
                                                             WCCPLX81            3
                                                                         ---------
                                                                                46

                                                   20191002  WCCPLX11            3
                                                             WCCPLX17            2
                                                             WCCPLX82            1
                                                             WCCPLX19            2
                                                             WCCPLX77            2
                                                             WCCPLX76            2
                                                             WCCPLX12            2
                                                             WCCPLX13            2
                                                             WCCPLX73            2
                                                             WCCPLX79            1
                                                             WCCPLX74            2
                                                             WCCPLX75            5
                                                             WCCPLX71            1
                                                                         ---------
                                                                                27

                                                   20191003  WCCPLX83            7
                                                             WCCPLX18            1
                                                             WCCPLX78            4
                                                             WCCPLX80            5
                                                             WCCPLX11            3
1                                                           The SAS System                                                          2

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                   20191003  WCCPLX17            2
                                                             WCCPLX82            3
                                                             WCCPLX19            2
                                                             WCCPLX77            4
                                                             WCCPLX76            2
                                                             WCCPLX12            5
                                                             WCCPLX13            3
                                                             WCCPLX15            2
                                                             WCCPLX73            3
                                                             WCCPLX72            5
                                                             WCCPLX79            3
                                                             WCCPLX74            2
                                                             WCCPLX75            6
                                                             WCCPLX71            3
                                                             WCCPLX20            3
                                                             WCCPLX14            1
                                                             WCCPLX16            3
                                                             WCCPLX81            3
                                                                         ---------
                                                                                75

                                                   20191004  WCCPLX83            7
                                                             WCCPLX18            8
                                                             WCCPLX78            8
                                                             WCCPLX80            8
                                                             WCCPLX11            6
                                                             WCCPLX17            7
                                                             WCCPLX82            8
                                                             WCCPLX19            9
                                                             WCCPLX77            4
                                                             WCCPLX76            9
                                                             WCCPLX12            4
                                                             WCCPLX13            6
                                                             WCCPLX15            9
                                                             WCCPLX73            4
                                                             WCCPLX72            3
                                                             WCCPLX79            6
                                                             WCCPLX74            4
                                                             WCCPLX75            5
                                                             WCCPLX71            2
                                                             WCCPLX20            5
                                                             WCCPLX14            3
                                                             WCCPLX16            2
                                                             WCCPLX81            5
                                                                         ---------
                                                                               132

                                                   20191005  WCCPLX85            6
                                                             WCCPLX34            7
                                                             WCCPLX92            2
                                                             WCCPLX89            2
                                                             WCCPLX36            2
                                                             WCCPLX38            3
                                                             WCCPLX39            2
                                                             WCCPLX33            2
1                                                           The SAS System                                                          3

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                   20191005  WCCPLX87            1
                                                             WCCPLX32            2
                                                             WCCPLX37            3
                                                             WCCPLX88            1
                                                             WCCPLX40            2
                                                             WCCPLX91            2
                                                             WCCPLX84            2
                                                             WCCPLX90            1
                                                             WCCPLX35            1
                                                             WCCPLX86            1
                                                             WCCPLX95            1
                                                                         ---------
                                                                                43

                                                   20191007  WCCPLX89            1
                                                             WCCPLX36            1
                                                             WCCPLX88            1
                                                             WCCPLX91            2
                                                                         ---------
                                                                                 5

                                                   20191008  WCCPLX83            2
                                                             WCCPLX18            1
                                                             WCCPLX78            3
                                                             WCCPLX80            2
                                                             WCCPLX11            1
                                                             WCCPLX17            1
                                                             WCCPLX82            1
                                                             WCCPLX19            1
                                                             WCCPLX77            2
                                                             WCCPLX13            1
                                                             WCCPLX73            1
                                                             WCCPLX72            3
                                                             WCCPLX74            1
                                                             WCCPLX75            1
                                                             WCCPLX20            1
                                                             WCCPLX14            1
                                                             WCCPLX16            1
                                                             WCCPLX81            1
                                                             WCCPLX85            4
                                                             WCCPLX34            2
                                                             WCCPLX92            3
                                                             WCCPLX89            3
                                                             WCCPLX36            2
                                                             WCCPLX38            2
                                                             WCCPLX39            1
                                                             WCCPLX33            1
                                                             WCCPLX87            4
                                                             WCCPLX32            3
                                                             WCCPLX31            3
                                                             WCCPLX37            1
                                                             WCCPLX88            1
                                                             WCCPLX40            1
                                                             WCCPLX96            4
                                                             WCCPLX84            1
1                                                           The SAS System                                                          4

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                   20191008  WCCPLX90            1
                                                             WCCPLX93            3
                                                             WCCPLX94            1
                                                             WCCPLX35            1
                                                                         ---------
                                                                                67

                                                   20191009  WCCPLX83            2
                                                             WCCPLX18            1
                                                             WCCPLX78            4
                                                             WCCPLX80            2
                                                             WCCPLX11            1
                                                             WCCPLX82            3
                                                             WCCPLX19            2
                                                             WCCPLX77            2
                                                             WCCPLX76            1
                                                             WCCPLX12            3
                                                             WCCPLX13            1
                                                             WCCPLX15            1
                                                             WCCPLX73            1
                                                             WCCPLX72            2
                                                             WCCPLX79            2
                                                             WCCPLX74            2
                                                             WCCPLX71            2
                                                             WCCPLX14            1
                                                             WCCPLX16            1
                                                             WCCPLX81            1
                                                                         ---------
                                                                                35

                                                   20191010  WCCPLX83            2
                                                             WCCPLX18            2
                                                             WCCPLX78            1
                                                             WCCPLX80            1
                                                             WCCPLX11            1
                                                             WCCPLX17            1
                                                             WCCPLX82            1
                                                             WCCPLX19            2
                                                             WCCPLX77            2
                                                             WCCPLX13            1
                                                             WCCPLX73            1
                                                             WCCPLX72            1
                                                             WCCPLX74            2
                                                             WCCPLX75            2
                                                             WCCPLX20            2
                                                             WCCPLX14            1
                                                             WCCPLX16            2
                                                             WCCPLX81            2
                                                                         ---------
                                                                                27

                                                   20191011  WCCPLX83            3
                                                             WCCPLX18            2
                                                             WCCPLX78            2
                                                             WCCPLX80            2
1                                                           The SAS System                                                          5

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                   20191011  WCCPLX11            3
                                                             WCCPLX17            3
                                                             WCCPLX82            2
                                                             WCCPLX77            2
                                                             WCCPLX76            2
                                                             WCCPLX12            2
                                                             WCCPLX13            3
                                                             WCCPLX15            2
                                                             WCCPLX72            2
                                                             WCCPLX74            1
                                                             WCCPLX75            1
                                                             WCCPLX14            2
                                                             WCCPLX16            1
                                                             WCCPLX81            1
                                                                         ---------
                                                                                36

                                                   20191012  WCCPLX15            1
                                                             WCCPLX81            1
                                                                         ---------
                                                                                 2

                                                   20191015  WCCPLX79            1
                                                                         ---------
                                                                                 1

                                                   20191016  WCCPLX83           11
                                                             WCCPLX18           11
                                                             WCCPLX78           11
                                                             WCCPLX80           11
                                                             WCCPLX11            7
                                                             WCCPLX17           10
                                                             WCCPLX82            9
                                                             WCCPLX19            8
                                                             WCCPLX77            7
                                                             WCCPLX76           11
                                                             WCCPLX12           10
                                                             WCCPLX13           10
                                                             WCCPLX15           11
                                                             WCCPLX73           15
                                                             WCCPLX72           12
                                                             WCCPLX79           11
                                                             WCCPLX74            9
                                                             WCCPLX75            7
                                                             WCCPLX71           10
                                                             WCCPLX20           10
                                                             WCCPLX14            7
                                                             WCCPLX16           11
                                                             WCCPLX81            7
                                                                         ---------
                                                                               226

                                                   20191017  WCCPLX83            3
                                                             WCCPLX18            3
                                                             WCCPLX78            2
1                                                           The SAS System                                                          6

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                   20191017  WCCPLX80            2
                                                             WCCPLX11            5
                                                             WCCPLX17            4
                                                             WCCPLX82            3
                                                             WCCPLX19            1
                                                             WCCPLX77            3
                                                             WCCPLX76            1
                                                             WCCPLX12            4
                                                             WCCPLX13            3
                                                             WCCPLX15            5
                                                             WCCPLX73            1
                                                             WCCPLX72            3
                                                             WCCPLX79            1
                                                             WCCPLX74            2
                                                             WCCPLX75            1
                                                             WCCPLX71            5
                                                             WCCPLX20            3
                                                             WCCPLX14            4
                                                             WCCPLX81            2
                                                                         ---------
                                                                                61

                                                   20191018  WCCPLX83            1
                                                             WCCPLX18            4
                                                             WCCPLX80            3
                                                             WCCPLX11            1
                                                             WCCPLX17            2
                                                             WCCPLX82            3
                                                             WCCPLX19            2
                                                             WCCPLX77            1
                                                             WCCPLX76            3
                                                             WCCPLX12            3
                                                             WCCPLX13            2
                                                             WCCPLX15            1
                                                             WCCPLX73            3
                                                             WCCPLX72            1
                                                             WCCPLX79            1
                                                             WCCPLX74            2
                                                             WCCPLX71            1
                                                             WCCPLX20            1
                                                             WCCPLX14            3
                                                             WCCPLX16            3
                                                             WCCPLX81            1
                                                                         ---------
                                                                                42

                                                   20191019  WCCPLX85            2
                                                             WCCPLX34            2
                                                             WCCPLX92            3
                                                             WCCPLX89            1
                                                             WCCPLX36            1
                                                             WCCPLX38            2
                                                             WCCPLX39            3
                                                             WCCPLX33            3
                                                             WCCPLX87            1
1                                                           The SAS System                                                          7

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                   20191019  WCCPLX32            1
                                                             WCCPLX31            3
                                                             WCCPLX88            2
                                                             WCCPLX40            2
                                                             WCCPLX96            1
                                                             WCCPLX84            1
                                                             WCCPLX90            2
                                                             WCCPLX93            1
                                                             WCCPLX94            3
                                                                         ---------
                                                                                34

                                                   20191020  WCCPLX60            2
                                                             WCCPLX25            1
                                                             WCCPLX62            1
                                                             WCCPLX61            1
                                                             WCCPLX22            1
                                                             WCCPLX69            1
                                                             WCCPLX29            1
                                                             WCCPLX65            1
                                                                         ---------
                                                                                 9

                                                   20191021  WCCPLX39            1
                                                             WCCPLX37            1
                                                                         ---------
                                                                                 2

                                                   20191022  WCCPLX83            4
                                                             WCCPLX18            6
                                                             WCCPLX78            2
                                                             WCCPLX80            2
                                                             WCCPLX11            2
                                                             WCCPLX17            1
                                                             WCCPLX82            2
                                                             WCCPLX19            3
                                                             WCCPLX77            3
                                                             WCCPLX76            4
                                                             WCCPLX12            1
                                                             WCCPLX13            2
                                                             WCCPLX15            3
                                                             WCCPLX73            1
                                                             WCCPLX72            1
                                                             WCCPLX79            1
                                                             WCCPLX74            1
                                                             WCCPLX75            2
                                                             WCCPLX71            1
                                                             WCCPLX20            4
                                                             WCCPLX14            1
                                                             WCCPLX16            2
                                                             WCCPLX36            1
                                                                         ---------
                                                                                50

1                                                           The SAS System                                                          8

                                                   date      replyqm         COUNT
                                                   -------------------------------

                                                                         =========
                                                                               929

[yuwda01]

Hi,

I am getting the following error when I tried to attach the input file.

The contents of the attachment doesn't match its file type.

Thanks David

[Tom]

---

@yuwda01 wrote:

Hi,

I am getting the following error when I tried to attach the input file.

The contents of the attachment doesn't match its file type.

Thanks David

---

Don't "attach" anything. Paste the text of a data step that creates JUST ENOUGH data to explain your issue into the pop-up box that appears when you push in the Insert Code or Insert SAS Code button.

Example:

data have;
  input date $ replyqm $ ;
cards;
20190929  WCCPLX83
20190929  WCCPLX18
20190929  WCCPLX11
20190929  WCCPLX17
20190929  WCCPLX82
20190929  WCCPLX19
20190929  WCCPLX14
20190929  WCCPLX14
20190929  WCCPLX16
20191001  WCCPLX83
20191001  WCCPLX83
20191001  WCCPLX18
20191001  WCCPLX18
20191001  WCCPLX78
20191001  WCCPLX78
20191001  WCCPLX78
20191001  WCCPLX80
20191001  WCCPLX11
20191001  WCCPLX17
20191001  WCCPLX17
20191001  WCCPLX17
20191001  WCCPLX19
20191001  WCCPLX19
20191001  WCCPLX19
;

proc freq data=have order=freq;
  by date ;
  tables replyqm;
run;

Result:

The SAS System 

date=20190929

The FREQ Procedure

                                     Cumulative    Cumulative
replyqm     Frequency     Percent     Frequency      Percent
-------------------------------------------------------------
WCCPLX14           2       22.22             2        22.22
WCCPLX11           1       11.11             3        33.33
WCCPLX16           1       11.11             4        44.44
WCCPLX17           1       11.11             5        55.56
WCCPLX18           1       11.11             6        66.67
WCCPLX19           1       11.11             7        77.78
WCCPLX82           1       11.11             8        88.89
WCCPLX83           1       11.11             9       100.00

The SAS System 

date=20191001

The FREQ Procedure

                                     Cumulative    Cumulative
replyqm     Frequency     Percent     Frequency      Percent
-------------------------------------------------------------
WCCPLX17           3       20.00             3        20.00
WCCPLX19           3       20.00             6        40.00
WCCPLX78           3       20.00             9        60.00
WCCPLX18           2       13.33            11        73.33
WCCPLX83           2       13.33            13        86.67
WCCPLX11           1        6.67            14        93.33
WCCPLX80           1        6.67            15       100.00

[PaigeMiller]

---

@yuwda01 wrote:

Thanks for the quick response. 

define replyqm / group order=freq descending;

I thought this statement is sorting FREQ on number of occurrence on REPLYQM.

the report shows

20191022  WCCPLX76            8          8 
          WCCPLX11            6          6 
          WCCPLX12            5          5 
          WCCPLX18            8          8 
          WCCPLX78            6          6 
          WCCPLX77            5          5 
          WCCPLX82            7          7 
          WCCPLX75            8          8 

and I expect all number 8 should be on top then followed by 7,6,5

20191022  WCCPLX76            8          8 

          WCCPLX18            8          8

          WCCPLX82            7          7 
          WCCPLX11            6          6  
          WCCPLX78            6          6 
          WCCPLX77            5          5  
          WCCPLX12            5          5

Thanks David 

---

You want the variable NUM (which has label COUNT) to be ordered. You don't want REPLYQM to be ordered.

[yuwda01]

So what is the change I need to make to get what I need? 

[yuwda01]

Hi,

Any update on this?

Thanks David