topic Re: Conditional Sum in SAS Programming

Conditional Sum

buechler66 — Wed, 13 Apr 2016 17:27:30 GMT

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;

Re: Conditional Sum

Reeza — Wed, 13 Apr 2016 17:51:24 GMT

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.

Re: Conditional Sum

buechler66 — Wed, 13 Apr 2016 17:54:37 GMT

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.

Re: Conditional Sum

buechler66 — Wed, 13 Apr 2016 18:00:37 GMT

I'm gonna attempt Proc SQL I think.

Re: Conditional Sum

Astounding — Wed, 13 Apr 2016 18:09:44 GMT

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;

Re: Conditional Sum

buechler66 — Wed, 13 Apr 2016 18:12:19 GMT

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):

Re: Conditional Sum

Astounding — Wed, 13 Apr 2016 18:24:16 GMT

OK, how about this way then:

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;

Re: Conditional Sum

buechler66 — Wed, 13 Apr 2016 19:06:06 GMT

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?

Re: Conditional Sum

Astounding — Wed, 13 Apr 2016 19:15:50 GMT

Do you want separate totals for each Rule_order?

Re: Conditional Sum

ballardw — Wed, 13 Apr 2016 19:58:36 GMT

SQL Sum (across observations) is not the same as the datastep sum function (sum of variables within single observation). Hence the error.