title: Using group by in Proc SQL for SAS
body: <p>I am trying to summarize my data set using the proc sql, but I have repeated values in the output, a simple version of my code is:</p>
  
  <pre><code>PROC SQL;
  CREATE TABLE perm.rx_4 AS
  SELECT  patid,ndc,fill_mon,
  COUNT(dea) AS n_dea, 
  sum(DEDUCT) AS tot_DEDUCT
  FROM perm.rx 
  GROUP BY patid,ndc,fill_mon;
  QUIT;
  </code></pre>
  
  <p>Some sample output is:</p>
  
  <pre><code> Obs                  Patid    Ndc            FILL_mon    n_dea    DEDUCT
  
   3815            33003605204    00054465029    2000-05       2         0  
  3816            33003605204    00054465029    2000-05       2         0  
  12257            33004361450    00406035701    2000-06       2         0  
  16564            33004744098    00603128458    2000-05       2         0  
  16565            33004744098    00603128458    2000-05       2         0  
  16566            33004744098    00603128458    2000-06       2         0  
  16567            33004744098    00603128458    2000-06       2         0  
  46380            33008165116    00406035705    2000-06       2         0  
  85179            33013674758    00406035801    2000-05       2         0  
  89248            33014228307    00054465029    2000-05       2         0  
  107514            33016949900    00406035805    2000-06       2         0  
  135047            33056226897    63481062370    2000-05       2         0  
  213691            33065594501    00472141916    2000-05       2         0  
  215192            33065657835    63481062370    2000-06       2         0  
  242848            33066899581    60432024516    2000-06       2         0  
  </code></pre>
  
  <p>As you can see there are repeated out put, for example obs 3815,3816. I have saw some people had similar problem, but the answers didn't work for me. </p>
  
  <p>The content of the dataset is this:</p>
  
  <pre><code>                            The SAS System                               5
                                            17:01 Thursday, December 3, 2015
  
                          The CONTENTS Procedure
  
                    Engine/Host Dependent Information
  
       Data Set Page Size          65536                           
       Number of Data Set Pages    210                             
       First Data Page             1                               
       Max Obs per Page            1360                            
       Obs in First Data Page      1310                            
       Number of Data Set Repairs  0                               
       Filename                    /home/zahram/optum/rx_4.sas7bdat
       Release Created             9.0401M2                        
       Host Created                Linux                           
       Inode Number                424673574                       
       Access Permission           rw-r-----                       
       Owner Name                  zahram                          
       File Size (bytes)           13828096                        
  
  
                              The SAS System                               6
                                            17:01 Thursday, December 3, 2015
  
                          The CONTENTS Procedure
  
                Alphabetic List of Variables and Attributes
  
    #    Variable      Type    Len    Format    Informat    Label
  
    3    FILL_mon      Num       8    YYMMD.                Fill month
    2    Ndc           Char     11    $11.      $20.        Ndc       
    1    Patid         Num       8    19.                   Patid     
    4    n_dea         Num       8                                    
    5    tot_DEDUCT    Num       8                                    
  
  
                            Sort Information
  
                    Sortedby       Patid Ndc FILL_mon
                    Validated      YES               
                    Character Set  ASCII             
  
  
                              The SAS System                               7
                                            17:01 Thursday, December 3, 2015
  
                          The CONTENTS Procedure
  
                            Sort Information
  
                    Sort Option    NODUPKEY          
  </code></pre>
  
  <p>NOTE: PROCEDURE CONTENTS used (Total process time):
        real time           0.08 seconds
        cpu time            0.01 seconds</p>