Solved
Contributor
Posts: 23

# sumifs function

Hi,

I am looking for sas procedure or sas function that similar to excel sumifs function.

I would to sum _FREQ_ in terms of msa_code_all and Auditor_Key where previousyear< yearend <= yearend

So for   msa_code_all(10100) and Auditor_Key(27) sum of freq should be 8(5+2+1) because yearends of three rows are smaller thanpreviousyear.

Sumifs function is in G column =SUMIFS(D\$2\$21,\$A\$2:\$A\$21,"="&\$A3,\$B\$2:\$B\$21,"="&\$B3,\$C\$2:\$C\$21,"="&\$C3,\$E\$2:\$E\$21,"<="&\$E3,\$E\$2:\$E\$21,">"&\$F3).

So far, I came up with the below code but does not work. Can you help me?

proc summary data=example nway;

class Auditor_Key msa_code_all

var _FREQ_;

where previousyear < yearend <= yearend ;

outputout=testsum sum=;

run;

 msa_code_all SIC2 Auditor_Key _FREQ_ yearend previousyear Sum of Freq 10100 67 27 5 20100301 20090301 8 10100 67 27 2 20100201 20090201 0 10100 67 27 1 20100101 20090101 1 10100 67 27 1 20031201 20021201 1 10180 36 846 1 20040901 20030901 1 10180 36 846 1 20030901 20020901 1 10180 46 846 1 20011201 20001201 1 10180 60 846 2 20110601 20100601 2 10180 60 846 1 20100601 20090601 1 10180 60 846 2 20090601 20080601 2 10180 60 846 1 20080601 20070601 1 10180 60 846 1 20070601 20060601 1 10180 60 846 1 20060601 20050601 1 10180 51 1091 1 20041201 20031201 1 10180 51 1091 1 20031201 20021201 1 10180 51 1091 1 20021201 20011201 1 10180 67 1091 1 20041201 20031201 1 10180 67 1091 1 20031201 20021201 1 10180 67 1091 1 20021201 20011201 1 10420 7 2 2 20111201 20101201 2

Accepted Solutions
Solution
‎11-11-2014 10:52 PM
Super User
Posts: 8,120

## Re: sumifs function

One easy way is to merge the data with itself.  This doesn't seem to get the same values, but perhaps the boundary conditions on the date ranges is wrong?

data have ;

row+1;

input msa_code_all SIC2 Auditor_Key _FREQ_ yearend previousyear what;

informat yearend previousyear yymmdd8. ;

format yearend previousyear yymmdd10. ;

cards;

10100 67 27 5 20100301 20090301 8

10100 67 27 2 20100201 20090201 0

10100 67 27 1 20100101 20090101 1

10100 67 27 1 20031201 20021201 1

10180 36 846 1 20040901 20030901 1

10180 36 846 1 20030901 20020901 1

10180 46 846 1 20011201 20001201 1

10180 60 846 2 20110601 20100601 2

10180 60 846 1 20100601 20090601 1

10180 60 846 2 20090601 20080601 2

10180 60 846 1 20080601 20070601 1

10180 60 846 1 20070601 20060601 1

10180 60 846 1 20060601 20050601 1

10180 51 1091 1 20041201 20031201 1

10180 51 1091 1 20031201 20021201 1

10180 51 1091 1 20021201 20011201 1

10180 67 1091 1 20041201 20031201 1

10180 67 1091 1 20031201 20021201 1

10180 67 1091 1 20021201 20011201 1

10420 7 2 2 20111201 20101201 2

run;

proc sql noprint ;

create table want as

select distinct a.*,sum(b._freq_) as newvar

from have a

, have b

where a.msa_code_all = b.msa_code_all

and a.SIC2 and b.SIC2

and a.Auditor_Key and b.Auditor_Key

and b.yearend between a.yearend and a.previousyear

group by a.msa_code_all

, a.SIC2

, a.Auditor_Key

, a.yearend

, a.previousyear

order by a.row

;

quit;

proc print data=want ;

run;

All Replies
Super User
Posts: 23,771

## Re: sumifs function

I'm not following. Can you simplify such that you display what you have and what you want?

Contributor
Posts: 23

## Re: sumifs function

sorry Reeza!

I want to get the same values in Sum of Freq column using sas code becuase I got that values with excel sumifs function.

my code does not work. do you know how to get values in Sum of Freq column using sas code?

Super User
Posts: 23,771

## Re: sumifs function

I don't understand the logic of how that column is calculated.

Super User
Posts: 8,120

## Re: sumifs function

Seems to me the issue is what records to include.

The first group defined by MSA*SIC*AUDITOR has 4 records that total to 9 not 8.

If you want to only include 3 of the 4 records for the first group what logic is excluding the fourth record?

The last column in your report does not make any sense given the data.

Contributor
Posts: 23

## Re: sumifs function

Time matters.. 4th records's yearend(20031201) is not between first records' previousyear(20090301)and yearend(20100301).

First three records' yearends(20100301,20100201,20100101)

are between first records' previousyear(20090301)and yearend(20100301).

Solution
‎11-11-2014 10:52 PM
Super User
Posts: 8,120

## Re: sumifs function

One easy way is to merge the data with itself.  This doesn't seem to get the same values, but perhaps the boundary conditions on the date ranges is wrong?

data have ;

row+1;

input msa_code_all SIC2 Auditor_Key _FREQ_ yearend previousyear what;

informat yearend previousyear yymmdd8. ;

format yearend previousyear yymmdd10. ;

cards;

10100 67 27 5 20100301 20090301 8

10100 67 27 2 20100201 20090201 0

10100 67 27 1 20100101 20090101 1

10100 67 27 1 20031201 20021201 1

10180 36 846 1 20040901 20030901 1

10180 36 846 1 20030901 20020901 1

10180 46 846 1 20011201 20001201 1

10180 60 846 2 20110601 20100601 2

10180 60 846 1 20100601 20090601 1

10180 60 846 2 20090601 20080601 2

10180 60 846 1 20080601 20070601 1

10180 60 846 1 20070601 20060601 1

10180 60 846 1 20060601 20050601 1

10180 51 1091 1 20041201 20031201 1

10180 51 1091 1 20031201 20021201 1

10180 51 1091 1 20021201 20011201 1

10180 67 1091 1 20041201 20031201 1

10180 67 1091 1 20031201 20021201 1

10180 67 1091 1 20021201 20011201 1

10420 7 2 2 20111201 20101201 2

run;

proc sql noprint ;

create table want as

select distinct a.*,sum(b._freq_) as newvar

from have a

, have b

where a.msa_code_all = b.msa_code_all

and a.SIC2 and b.SIC2

and a.Auditor_Key and b.Auditor_Key

and b.yearend between a.yearend and a.previousyear

group by a.msa_code_all

, a.SIC2

, a.Auditor_Key

, a.yearend

, a.previousyear

order by a.row

;

quit;

proc print data=want ;

run;

Contributor
Posts: 23

## Re: sumifs function

Oh my good~ It works!!!

Tom! Thank you so much!!! I changed  the boundary conditions on the date ranges to this.

a.previousyear < b.yearend <= a.yearend

Learner
Posts: 1

## Re: sumifs function

Hi, first off thanks a lot for the post! This helped me out a lot as well! However, do you think there is a way to improve the performance? I am working with a data set of about 1.4gb and as you can imagine, it takes quite some time to run. I also need to run this with multiple conditions - the one condition increases the size a lot (I guess) and results in an error:
ERROR: Insufficient space in file WORK.'SASTMP-000000447'n.UTILITY.
ERROR: File WORK.'SASTMP-000000447'n.UTILITY is damaged. I/O processing did not complete.

If you could please suggest something, it would be great!
Posts: 1,345

## Re: sumifs function

If the records are physically grouped by  msa_code_all/sic2/auditor_key, then this is a forward rolling window, where the window size varies, depending on the yearend and previousyear values.

I ran this double DOW, which should be faster and take less disk space.  The ouput was identical to the SQL program  output (with the OP's revised boundaries, and the equality constraints I mentioned elsewhere).

If you expect more than 20 records in a given BY group, then increase the array sizes.

``````data want2;
array yr{20};
array prvyr{20};
array sumfrq  {20};
do rec=1 to 20;
sumfrq{rec}=0;
end;

do rec=1 by 1 until (last.auditor_key);
set have;
by msa_code_all sic2 auditor_key  notsorted;
yr{rec}=yearend;
prvyr{rec}= previousyear;
do J=rec to 1 by -1  while (prvyr{J}<yr{rec}<=yr{J});
sumfrq{J}=sumfrq{J}+_freq_;
end;
end;

do rec=1 by 1 until (last.auditor_key);
set have;
by msa_code_all sic2 auditor_key  notsorted;
newvar2=sumfrq{rec};
output;
end;
drop  rec yr: prvyr: sumfrq: J ;
run;``````

Posts: 1,345

## Re: sumifs function

[ Edited ]

I think the line "and a.sic2 and b.sic2"  should be  "and a.sic2=b.sic2".

Same with "and a.auditor_key and b.auditor_key".  Should be "and a.auditor_key=b.auditor_key", yes?

MK

☑ This topic is solved.

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

Discussion stats
• 10 replies
• 7022 views
• 1 like
• 5 in conversation