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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.