Sum up value by group if the group is missing

[newboy1218]

Hi, I have the following dataset. I would I like to sum up the 'amount' variable on var2 = 'a' for each var1 value. Currently I have the following SAS code, but in my result, var1 = '3' will have result equals to 0. Rather, I would like to have a result of . (empty) if var1 does not have var2 = 'a'. How could I do that? Thanks.

DATA:

var1   var2   amount

   1       a      100

   1       a      200

   1       b      100

   2       a        0

   2       b      200

   3       b      300

Current SAS Code:

select var1, (case when var2 = 'a' then amount else 0 end) as var2_a_amount

from input_data

group by var1;

RESULT I GOT CURRENTLY:

var1 var2_a_amount

  1            300

  2              0

  3              0

WHAT RESULT I WANT:

var1 var2_a_amount

  1            300

  2              0

  3              .

[novinosrin · Accepted solution]

Similar question answered by @Reeza  in another thread 2 years ago

sum(case when var2 = 'a' then amount else . end)

data input_data;
input var1   var2  $ amount;
cards;
   1       a      100

   1       a      200

   1       b      100

   2       a        0

   2       b      200

   3       b      300
   ;

proc sql;
select var1, sum(case when var2 = 'a' then amount else . end) as var2_a_amount

from input_data

group by var1;
quit;