BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
buechler66
Barite | Level 11

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

9 REPLIES 9
Reeza
Super User

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.

buechler66
Barite | Level 11
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.
buechler66
Barite | Level 11
I'm gonna attempt Proc SQL I think.
Astounding
PROC Star

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;

 

buechler66
Barite | Level 11

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):
Astounding
PROC Star

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;

buechler66
Barite | Level 11

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?

Astounding
PROC Star

Do you want separate totals for each Rule_order?

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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