CODEWITHSUNDEEP
sqlsas
gdogg371
gdogg371

Reputation: 4122

Getting stuck with this SQL summary function

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.

Consider the below:

Input data...

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;

Attempted summary function...

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 > 0;
quit;

What desired outout should look like...

data test3;
account_rk = 111111;
sum_ca_flag = 1;
min_score_with_ca = 150;
min_score_without_ca = 100;
output;
run;

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 score1 depending on the value of ca_flag and group it by account_rk.

Can anyone tell me what I am doing wrong?

Thanks

Upvotes: 0

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I think you just want NULL or no else clause and then the right logic:

proc sql;
create table test2 as 
    select account_rk, sum(ca_flag) as sum_ca_flag,
           min(case when score_with_ca > 0 then score_with_ca
               end) as min_score_with_ca,
           min(case when score_without_ca > 0 then score_without_ca
               end) as min_score_without_ca
    from test
    group by account_rk
    having sum_ca_flag > 0;
quit;

Upvotes: 1

Related Questions