syntax for new var as difference between two others

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 83
Accepted Solution

syntax for new var as difference between two others

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;

Accepted Solutions
Solution
‎06-01-2017 07:17 AM
Super User
Super User
Posts: 6,347

Re: syntax for new var as difference between two others

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;

 

View solution in original post


All Replies
Solution
‎06-01-2017 07:17 AM
Super User
Super User
Posts: 6,347

Re: syntax for new var as difference between two others

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;

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 78 views
  • 1 like
  • 2 in conversation