Conditional Sum

Solved
Regular Contributor
Posts: 212

Conditional Sum

Hi.  I'm wondering if it's possible to sum all instances of the Total column with Score_Impacting = 'N' (see attached)?  Is there a built-in for this or how could I accomplish this?

I'm thinking maybe something like this???

if rule_nm = 'MATCHING + NO SCORE IMPACTING' then do;
total = sum(total (where score_impacting='N'));
end;

Accepted Solutions
Solution
‎04-13-2016 03:51 PM
Super User
Posts: 6,785

Re: Conditional Sum

proc means data=FinalData sum;

where rule_nm = 'MATCHING + NO SCORE IMPACTING' and score_impacting='N';

var total _: null;

output out=cal_sums (drop=_type_ _freq_) sum=;

run;

All Replies
Super User
Posts: 23,763

Re: Conditional Sum

No, it depends on how you want the final results and if you're in a reporting procedure or data step.

Proc means is one way, proc sql is another, and there are others. But we need more information.

As long as you don't need code, feel free to include pictures.

Regular Contributor
Posts: 212

Re: Conditional Sum

Sorry, I'm attempting this inside a data step, but I'm open to the most logical solution if it's not in the data step. Thanks.
Regular Contributor
Posts: 212

Re: Conditional Sum

I'm gonna attempt Proc SQL I think.
Super User
Posts: 6,785

Re: Conditional Sum

Going back to your original code, would it not be as simple as this:

if rule_nm = 'MATCHING + NO SCORE IMPACTING' and score_impacting='N' then grand_total + total;

Regular Contributor
Posts: 212

Re: Conditional Sum

Seems I'm hung up here.  The reference to sum(_ is my attempt to apply the sum() function to all my variables beginning with _. For instance I may have variables:

_04_11_2016

_3_26_2016

etc....

The problem is these variables are the product of a previous Proc Transpose and they will not be the same with each run of the program.  Is there a way to work around my issue?

``````108  proc sql;
109  CREATE TABLE cal_sums AS
110  select sum(total) as total, sum(_:), sum(null)
-
22
200
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, ), *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?,
AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^,
^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

111  from FinalData
112  where rule_nm = 'MATCHING + NO SCORE IMPACTING'
113    and score_impacting='N';
114  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):``````
Solution
‎04-13-2016 03:51 PM
Super User
Posts: 6,785

Re: Conditional Sum

proc means data=FinalData sum;

where rule_nm = 'MATCHING + NO SCORE IMPACTING' and score_impacting='N';

var total _: null;

output out=cal_sums (drop=_type_ _freq_) sum=;

run;

Regular Contributor
Posts: 212

Re: Conditional Sum

Wow...that looks promising, but it seems to have dropped the Rule_nm and Rule_order variables.  Is there a way to carry these thru to Cal_Sums dataset?

Super User
Posts: 6,785

Re: Conditional Sum

[ Edited ]

Do you want separate totals for each Rule_order?

Super User
Posts: 13,577