title: Getting stuck with this SQL summary function
body: <p>This exercise of generating summary values in SAS SQL has been driving me round the bend all day. The SAS part of it is basically just me mocking up some data and general SQL principles apply if you want to have a crack at answering.</p>
  
  <p>Consider the below:</p>
  
  <p>Input data...</p>
  
  <pre><code>data test;
  account_rk = 111111;
  customer_rk = 111111;
  ca_flag = 0;
  score1 = 100;
  score_with_ca = 0;
  score_without_ca = 100;
  output;
  account_rk = 111111;
  customer_rk = 222222;
  ca_flag = 1;
  score1 = 150;
  score_with_ca = 150;
  score_without_ca = 0;
  output;
  run;
  </code></pre>
  
  <p>Attempted summary function...</p>
  
  <pre><code>proc sql;
  create table test2 as 
  select
  account_rk,
  sum(ca_flag) as sum_ca_flag,
      min(case
      when score_with_ca ne 0 then score_with_ca
      else score1 end) as min_score_with_ca,
      min(case 
      when score_without_ca eq 0 then score_without_ca
      else score1 end) as min_score_without_ca
  from test
  group by account_rk
  having sum_ca_flag &gt; 0;
  quit;
  </code></pre>
  
  <p>What desired outout should look like...</p>
  
  <pre><code>data test3;
  account_rk = 111111;
  sum_ca_flag = 1;
  min_score_with_ca = 150;
  min_score_without_ca = 100;
  output;
  run;
  </code></pre>
  
  <p>What do I need to change in my SQL to get an output that looks like the output generated in the third step? The idea here in logical steps is to generate minimum values for <code>score1</code> depending on the value of <code>ca_flag</code> and group it by <code>account_rk</code>.</p>
  
  <p>Can anyone tell me what I am doing wrong?</p>
  
  <p>Thanks</p>