title: SAS sumifs - Finding number of pills of certain types used by patients
body: <p>Input Data:</p>
  
  <pre><code>user    DOB                 UseDate            numPills Type
  1   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 4   A
  1   2013-01-01 00:00:00.000 2013-07-20 00:00:00.000 5   A
  1   2013-01-01 00:00:00.000 2014-01-02 00:00:00.000 1   A
  2   2013-01-02 00:00:00.000 2013-04-12 00:00:00.000 1   A
  3   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 5   A
  3   2013-01-01 00:00:00.000 2013-07-20 00:00:00.000 5   A
  4   2013-01-02 00:00:00.000 2013-04-12 00:00:00.000 1   A
  5   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 9   A
  5   2013-01-01 00:00:00.000 2013-07-20 00:00:00.000 1   B
  6   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 1   A
  </code></pre>
  
  <p>Desired output:</p>
  
  <pre><code>user    DOB                  UseDate            numPills Type      sumifs
  1   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 4   A   9
  1   2013-01-01 00:00:00.000 2013-07-20 00:00:00.000 5   A   5
  1   2013-01-01 00:00:00.000 2014-01-02 00:00:00.000 1   A   0
  2   2013-01-02 00:00:00.000 2013-04-12 00:00:00.000 1   A   1
  3   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 5   A   10
  3   2013-01-01 00:00:00.000 2013-07-20 00:00:00.000 5   A   5
  4   2013-01-02 00:00:00.000 2013-04-12 00:00:00.000 1   A   1
  5   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 9   A   9
  5   2013-01-01 00:00:00.000 2013-07-20 00:00:00.000 1   B   1
  6   2013-01-01 00:00:00.000 2013-04-11 00:00:00.000 1   A   1
  </code></pre>
  
  <p>I'm trying to implement Excel's SUMIFS in SAS Enterprise Guide. I've already done a similar thing in MS SQL Server 2008 using cursors.</p>
  
  <p>My ultimate goal is to be able to traverse tuple by tuple. I'm also considering somehow grouping relevant records into sets, and see if I can use any set operations.</p>
  
  <p>Data:
  My sample records are drug prescriptions of patients, with each record detailing how many pills were bought. Hence, I need to count the number of pills for each patient, ideally into different bins.
  ie.</p>
  
  <p>Patient #1, Pill A, 3 pills</p>
  
  <p>Patient #1, Pill A, 2 pills</p>
  
  <p>Patient #1, Pill B, 1 pill</p>
  
  <p>I need to create a new column (that uses "SUMIFS") that would produce for the first row:
  Patient #1, Pill A, 5 pills</p>
  
  <p>Thank y'all!!!</p>
  
  <p>As requested (sorry about the formatting, I'm new to this forum):</p>
  
  <pre><code>/****** Script for SelectTopNRows command from SSMS  ******/
  /* Cursor code begins here */
  DECLARE @baseRWpointer CURSOR                       -- Base (read, write) cursor
  DECLARE @offsetRpointer CURSOR                      -- Offset (read only) cursor
  
  DECLARE @baseDate datetime
  DECLARE @baseUser integer
  DECLARE @baseType varchar(5)
  DECLARE @baseDOB datetime
  
  DECLARE @cumulative integer
  DECLARE @offsetCount integer
  
  DECLARE @offsetDate datetime
  DECLARE @offsetUser integer
  DECLARE @offsetType varchar(5)
  DECLARE @offsetNumPills integer
  DECLARE @offsetDOB datetime
  
  SET @baseDate = '1900-01-01'
  SET @baseUser = -1
  SET @baseType = NULL
  
  SET @cumulative = 0
  SET @offsetCount = 0
  
  SET @offsetRpointer = CURSOR SCROLL KEYSET
      FOR
          SELECT D.[user], D.[DOB], D.[UseDate], D.[numPills], D.[Type]   
          FROM [Charles_DB].[dbo].[Table1] D
          ORDER BY D.[ID], D.[Type], D.[UseDate]
      --FOR READ ONLY
  OPEN @offsetRpointer
  
  SET @baseRWpointer = CURSOR
      FOR
          SELECT D.[user], D.[DOB], D.[UseDate], D.[numPills], D.[Type]   
          FROM [Charles_DB].[dbo].[Table1] D
          ORDER BY D.[user], D.[Type], D.[UseDate]
      FOR update of D.[sumifs]
  
  OPEN @baseRWpointer
  
  FETCH NEXT from @baseRWpointer
  INTO @offsetUser, @offsetDOB, @offsetDate, @offsetNumPills, @offsetType
  
  FETCH NEXT from @offsetRpointer
  INTO @offsetUser, @offsetDOB, @offsetDate, @offsetNumPills, @offsetType
  
  WHILE   (@@FETCH_STATUS = 0)
  BEGIN
  
      SET @baseUser = @offsetUser
      SET @baseType = @offsetType
      SET @baseDOB = @offsetDOB
      SET @cumulative = 0
      SET @offsetCount = 0
  
      /*  Main "SUMIFS" loop  */
      while ( 
              @@FETCH_STATUS = 0 AND 
              (@baseUser = @offsetUser)
              AND (@baseType = @offsetType)
              AND ((DATEDIFF(day,@baseDOB,@offsetDate)) &lt;= 365)  )
      BEGIN
          Set @cumulative = @cumulative + @offsetNumPills
          Set @offsetCount = @offsetCount + 1
  
          FETCH NEXT from @offsetRpointer
          INTO @offsetUser, @offsetDOB, @offsetDate, @offsetNumPills, @offsetType
  
      END
  
      /*  Update the column "sumifs" for base row  
          Recall that D is [Charles_DB].[dbo].[Table1] */
      UPDATE [Charles_DB].[dbo].[Table1] 
          Set [Charles_DB].[dbo].[Table1].[sumifs] = @cumulative
          WHERE CURRENT OF @baseRWpointer
  
      /*  Make offset pointer catch up to base pointer */
      Set @offsetCount = -(@offsetCount - 1)
      FETCH RELATIVE @offsetCount from @offsetRpointer
      INTO @offsetUser, @offsetDOB, @offsetDate, @offsetNumPills, @offsetType
  
      /* Place this at the end to get the correct @@FETCH_STATUS output to avoid infinite loop  */
      FETCH NEXT from @baseRWpointer
      INTO @offsetUser, @offsetDOB, @offsetDate, @offsetNumPills, @offsetType
  
  
  
  
  END
  
  CLOSE @baseRWpointer
  CLOSE @offsetRpointer
  DEALLOCATE @baseRWpointer
  DEALLOCATE @offsetRpointer
  </code></pre>