DATA Step, Macro, Functions and more

Conditional Sum

Accepted Solution Solved
Reply
Regular Contributor
Posts: 212
Accepted Solution

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;


Capture.PNG

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

Re: Conditional Sum

Posted in reply to buechler66

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;

View solution in original post


All Replies
Super User
Posts: 19,814

Re: Conditional Sum

Posted in reply to buechler66

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: 5,506

Re: Conditional Sum

Posted in reply to buechler66

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

Posted in reply to buechler66

Seems I'm hung up here.  The reference to sum(_Smiley Happy 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: 5,506

Re: Conditional Sum

Posted in reply to buechler66

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;

Regular Contributor
Posts: 212

Re: Conditional Sum

Posted in reply to Astounding

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: 5,506

Re: Conditional Sum

[ Edited ]
Posted in reply to buechler66

Do you want separate totals for each Rule_order?

Super User
Posts: 11,343

Re: Conditional Sum

Posted in reply to buechler66
SQL Sum (across observations) is not the same as the datastep sum function (sum of variables within single observation). Hence the error.
☑ This topic is solved.

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

Discussion stats
  • 9 replies
  • 901 views
  • 0 likes
  • 4 in conversation