turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- SUMIF with multiple conditions

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-18-2016 01:18 PM

I am still having trouble with converting Excel SUMIFs to SAS code. Here is a different example from previous post. I want to end up with the value in "valueWant" below. The Excel formula that creates "ValueWant" is below: Basically I need to sum column F based on three different criteria. I've tried various versions of proc SQL, Hash objects, retain statements and I am still stuck on this.

=SUMIFS(F2:F$100,B2:B$100,B2,C2:C$100,C2+1,E2:E$100,D2)

for non Excel users this is what the formula means:

=SUMIFS(sum range, criteria1 Range, criteria1, criteria2 range, criteria3 range, criteria3)

If the criteria was from the same row instead of C2+1 for example, or the criteria was in the same column as the range instead of E2:E$100, D2 then it would be a simple sum of column F by columns B,C, E as grouped variables ; however, the complicated criteria is causing difficulty,

data want ;

infile datalines dsd missover;

input ID Col_B Col_C Col_D $ Col_E $ Col_F ValueWant Include ;

datalines;

10001,6,1,a.b.c.d,a.b.c.d.e,5,2,1

10001,6,2,a.b.c,a.b.c.d,1,2,1

10001,6,3,a.b,a.b.c,1,10,1

10001,6,4,a,a.b,5,0,1

10001,6,5,,,,0,0

10001,6,6,,a,5,0,1

10002,3,1,a.b,a.b.c,1,5,1

10002,3,2,a.,a.b,5,0,1

10002,3,3,,a,5,0,1

10003,6,1,a.b.c.d,a.b.c.d.e,5,1,1

10003,6,2,a.b.c,a.b.c.d,1,1,1

10003,6,3,a.b,a.b.c,1,5,1

10003,6,4,a,a.b,5,0,1

10003,6,5,,,,0,0

10003,6,6,,a,5,0,1

;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-18-2016 03:44 PM

How about your logic in English? Or logical terms. Not Excel.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-19-2016 08:12 AM

This is part of a longer formula which is a very complicated weighting structure so it's hard to create an analogy. I'll try to say what I need based on these codes.

I want to add the values in column F for all values of col_b and all values of col_c + 1 (indicating that the row progressed to the next level), and for all values of column d that match values in column e. Typically, the value in column d can only match column e in the col_c+1 row.

Not sure if this helps.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2016 03:57 PM

CP2 wrote:

This is part of a longer formula which is a very complicated weighting structure so it's hard to create an analogy. I'll try to say what I need based on these codes.

I want to add the values in column F for all values of col_b and all values of col_c + 1 (indicating that the row progressed to the next level), and for all values of column d that match values in column e. Typically, the value in column d can only match column e in the col_c+1 row.

Not sure if this helps.

No, you're still trying to be too technical.

I suggest starting with a single issue, stating the logic as clearly as possible and working through that. Then moving on to your next issue. As is, your question is broad and too involved for a simple forum answer. It may be worth starting over and explaining in more simple terms.

Including data as a data step and your desired variable is a good idea and thank you for providing that!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

09-20-2016 03:23 PM

Thank you for your help. I guess I keep hoping there are others more familiar with Excel than I am. The logic doesn't make sense to me so working through it was difficult. Anyway, it turns out there seems to be a reason why I can't articulate the logic - there really isn't a good explanation! So we examined the Excel formula i was supposed to convert and found the value in the range to be summed in the sumifs function is the same for each id so we don't need to do the complicated +1 and D=F criteria. It is just a simple grouped sum by columns B,C,D.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-18-2016 04:16 PM

As mutch as I anderstand the excel SUMIFS function, I would convert the formula

** =SUMIFS(F2:F$100,B2:B$100,B2,C2:C$100,C2+1,E2:E$100,D2)**

into:

if Col_B of row _N_ = Col_B of row 2 and

Col_C of row _N_ = (Col_C of row 2) +1 and

Col_E of row _N_ = Col_D of row 2

then ValueWant + Col_F;

If the above pseudo code is right then do:

data temp;

keep Col_B2 Col_C2 Col_D2;

set have (point=2);

Col_B2 = Col_B;

Col_C2 = Col_C + 1;

Col_D2 = Col_E;

output;

run;

proc sql;

create table want as select

t.*, sum(h.Col_F) as ValueWant

from temp as t

left join have as h

ON Col_B = Col_B2 and

Col_C = Col_C2 and

Col_D = Col_d2

order by ID, Col_B, Col_C, Col_D;

quit;

NOTE: The excel formula does not use the ID column.

The result is summing relevant rows from the whole input.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

09-19-2016 07:39 AM

thank you. the formula I showed was the formula for row 2 but that same formula is in every row under the valuewant column but the number is changed in the criteria depending on the row. For example for row 3 under that column the formula is:

=SUMIFS(F2:F$100,B2:B$100,B3,C2:C$100,C3+1,E2:E$100,D3)

=SUMIFS(F2:F$100,B2:B$100,B3,C2:C$100,C3+1,E2:E$100,D3)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2016 08:00 AM

also, I tried the following code but keep getting missing values.

proc sql ;

create table want as

select a.*, b.valuewant2

From want as a

left join

(select col_b, col_c, col_e, sum(col_f) as valuewant2

from want

group by col_b, col_c, col_e ) as b

on a.col_b = b.col_b

and a.col_c = b.col_c+1

and a.col_d = b.col_e ;

quit ;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2016 11:16 AM

To help you I need to clarrify few points:

1) By "Col_C + 1" within SAS the meaning is: Value of Col_C plus 1

(and not: Value of Col_c in row _N_ +1, which is the next row)

2) Columns B C D are keys to match data, so 1st step is to calculate and define the keys.

Using the excel terms: I define the keys according to the criteria in the excel formula.

The 2nd step is to sum Col_F per every combination of "keys";

3) You gave the example for row = 2; I understand you want to do it for all rows.

Does data have duplicates - i.e. more than one row with same "keys".

Obviously YES, otherwise ther will be no reason to sum Col_F;

It means we need to select DISTINCT combinations of "keys";

If you agree with my clarifications, then better do the work in two different steps:

1) Calculate distinct combinations of keys. Check the list is it OK. If true continue to the 2nd step;

Thus I have done creating dataset **temp**. In order to calculate **all** combinations of "keys"

drop the option (point=2) and finally sort by Col_B Col_C Col_D **NODUPKEY**;

2) Summarise Col_F per group defined by the "keys";

I hope I made myself clear. Try it. Upload your code and input lines and output you got - just per one "keys" example;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

09-19-2016 02:02 PM

Thank you for your response.

Here are my responses:

1) data is in sort order of col_c so col_C+1 is the next consecutive number.

2) B,C,E are the keyed fields - but this is part of the confusion. When the values are summed by these keyes then they need to be matched to the rows where C=C+1 (in summed database) and D= E (in summed database)

3)The rows are unique by id and col_c.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2016 03:46 PM

I need still to clariffy some points:

Is Col_B same value for all lines of an ID ?

Is Col_B the number of lines given per ID ?

What is Col_C + 1 for the last line in ID group ?

e.g. for ID=10002 on __last__ line Col_C=3. What is Col_C +1 in this case ?

Looking at IDs 10001 and 10003 they have same key values (e.g. Col_B=6, Col_c=1 and Col_E=5);

Do you want to some Col_F of those lines, despite different IDs ?

Why is ID not a member in the keys ? Is it deliberately ?

I am trying to convert the excel formula into sas technicaly but it is very difficult without understanding the logig and the materia .

I have the feeling that we argue to understand your data more than arguing SAS. I'm not sure this forum is intended for it.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-19-2016 05:39 PM

Some more clarifications:

Attached is a print of work.have created by your datalines.

I understand that column ValuWant is what you expect.

Line 1 and line 10 on attached PDF have the same keys:

Col_B=6 , Col_C=1, Col_D=a.b.c.d (=Col_E)

why on line 1 (id=10001) is ValuWant = 2 while on line 10 (id=10003) ValuWant = 1 ?

ID is not mentioned in your excel formula ?!

and - what is the origin/meaning of the INCLUDE column ?

does it participate in the algorithm to select lines to summarize ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Shmuel

09-20-2016 03:17 PM

thank you so much for trying to help! It turns out that because the column being summed is typically the same value we do not need to use +1 as in the excel formula. So, the code is much simpler - phew! It is a simple grouped sum with column B,C,D as the group variables.