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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.