title: SAS SQL: How to do (group base on Case) without pulling data first?
body: <p>I need to count the data of a particular massive table without pulling the data. Normally I group the data and perform a count...</p>
  
  <pre><code>select a.type,
         a.color,
         count(a.claimnumber) as count_of_claim
  from mytable as a
  group by a.type, a.color
  </code></pre>
  
  <p>But in this instance, the color is determined by a <code>CASE WHEN.. ELSE</code> statements. Sort of like this: (I made up this example, but the structure of my production problem is same).</p>
  
  <pre><code>SELECT b.type, 
         b.color, 
         Count(claimnumber) AS claimnumber_count 
  FROM   (
          SELECT a.type, 
                 CASE 
                   WHEN a.company = 'honda' 
                        AND Substr(a.modelnum, 1, 5) &lt;&gt; 'AAAAA' THEN 'red' 
                   WHEN Substr(a.modelnum, 1, 6) = 'BBBBBB' THEN 'blue' 
                   WHEN Substr(a.modelnum, 1, 5) = 'CCCCC' THEN 'white' 
                   ELSE 'black' 
                   END AS color, 
                 Substr(a.claimnumber, 1, 10) AS claimnumber 
          FROM   mytable AS a
          ) AS b 
  GROUP  BY b.type, b.color
  </code></pre>
  
  <p>I am pulling all the data down, determined the color by some When/Else case, and then counting how many there are using group. Is there a way I can simply increment a counter, or do the (grouping) without having this derived table? </p>
  
  <p>I read a lot of questions on stackoverflow, they empathized <strong>do the counting on server side</strong>, and I would like to implement my query as such. My current solution is really slow. It essentially pulling 1 year worth of record and counting them (Over 4-5 hours).</p>
  
  <p>Dummy output of claim count:</p>
  
  <pre><code>Type | Color | Count
  -------------------
  Car    red      16894
  Car    blue     2316
  Car    black    1560
  Car    white    89143 
  Bus    red      165
  Bus    blue     9611230
  Bus    black    25284
  Bus    white    16929971
  </code></pre>