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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.