syntax for new var as difference between two others

[brulard]

hi,

I sometime have to sum & count variables, and then show the difference between these two variables. Can this be done in one step instead of two?

thanks,

data have;
input name $ cli cli_letter;
datalines;
anne 1 1
barb 1 0
bob 1 1
bob 1 0
bob 1 1
mike 1 1
mike 1 1
run;

proc sql; create table have2 as
select  name, count(cli) as cli,sum(cli_letter) as letter_sent
from have
group by name;quit;

data want;set have2;
/*create new var as difference between first two */
letter_not_sent=cli-letter_sent;run;

[Tom · Accepted solution]

You can either just replicate the aggregate function.

proc sql;
create table have2 as
  select
    name
  , count(cli) as cli
  , sum(cli_letter) as letter_sent
  , count(cli) - sum(cli_letter) as letter_not_sent
  from have
  group by name
;
quit;

Or use the CALCULATED keyword when referencing a variable that is derived in the current statement.

proc sql;
create table have2 as
  select
    name
  , count(cli) as cli
  , sum(cli_letter) as letter_sent
  , calculated cli  - calculated letter_sent as letter_not_sent
  from have
  group by name
;
quit;