BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
newboy1218
Quartz | Level 8

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              .

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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;

 

View solution in original post

1 REPLY 1
novinosrin
Tourmaline | Level 20

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;

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 490 views
  • 0 likes
  • 2 in conversation