title: Summary function in HAVING clause in SAS
body: <p>I have the following query which returns a customer's username, the year and half-year, a count of bets for the first semi-year, a count of bets for the second semi-year and a profit calc. </p>
  
  <blockquote>
    <p>proc sql;</p>
  </blockquote>
  
  <pre><code>  create table avg
    as
    select 
    username as username,
    year(datepart(betdate))*10 + floor( (qtr(datepart(betdate))+1)/2) as yearsemiyear, 
    count(bet_id) as betcount,
  
    sum( case when floor( (qtr(datepart(betdate))+1)/2) = 1 then 1 else 0 end ) as firstHalfBetcount,
    sum( case when floor( (qtr(datepart(betdate))+1)/2) = 2 then 1 else 0 end ) as secondHalfBetcount,
  
    round(mean((winnings - stake) / stake) * 100, 0.01) as averageMargin
  
    from &amp;dsn
    WHERE datepart(BETDATE) &gt; '31DEC2010'd AND datepart(BETDATE) &lt;'01JAN2012'd 
    AND bet_type = 'SGL'   
    group by 1,2
   /* HAVING sum( case when floor( (qtr(datepart(betdate))+1)/2) = 1 then 1 else 0 end ) &gt;= 4 */
    order by username desc, yearsemiyear asc;
  </code></pre>
  
  <p>quit;</p>
  
  <p>How can I restrict this to customers who had at least 4 bets in the first half of the sample? If I uncomment the HAVING clause I have there, it only seems to return rows for the first half, and no second half rows are returned. I tried putting the restriction into the WHERE clause, but apparently you can't put a summary function in that CLAUSE. </p>
  
  <p>How should I do this? </p>
  
  <p>Thanks</p>