Sum up value by group if the group is missing

newboy1218 — Thu, 30 Jul 2020 18:24:23 GMT

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 .

Re: Sum up value by group if the group is missing

novinosrin — Thu, 30 Jul 2020 18:35:42 GMT

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;

topic Sum up value by group if the group is missing in SAS Programming

Sum up value by group if the group is missing

Re: Sum up value by group if the group is missing