BookmarkSubscribeRSS Feed
yuwda01
Calcite | Level 5

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      

21 REPLIES 21
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
yuwda01
Calcite | Level 5
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
Diamond | Level 26

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

--
Paige Miller
yuwda01
Calcite | Level 5
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
Super User

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
Super User Tom
Super User

Looks to me like 140 is larger than 6.

What is the question?

yuwda01
Calcite | Level 5

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
Super User

@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
Calcite | Level 5
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
Calcite | Level 5

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
Super User Tom
Super User

@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
Diamond | Level 26

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

--
Paige Miller
yuwda01
Calcite | Level 5

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

yuwda01
Calcite | Level 5

Hi,

 

Any update on this?

 

Thanks David

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!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 21 replies
  • 1365 views
  • 0 likes
  • 5 in conversation