## accumulative sum with condition

Solved
Occasional Contributor
Posts: 6

# accumulative sum with condition

I am working on this daily report and try to find a way to get what I want. So here is the example:

If Today(the date I run SAS) is Monday, Sep 7, 2015 , then I need the 'Want' column. The challenge part is the Friday, September 04, 2015's want number, I need it to be the sum of Have column from Friday Sep 04 to Sunday Sep 06th.

Is it possible to get it?

 Date Have Want Tuesday, September 01, 2015 2 2 Wednesday, September 02, 2015 3 3 Thursday, September 03, 2015 4 4 Friday, September 04, 2015 5 12 Saturday, September 05, 2015 2 0 Sunday, September 06, 2015 5 0

Thanks

Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Posts: 5,529

## Re: accumulative sum with condition

Here is one way to do this:

``````data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
;

proc sql;
create table Fridays as
select intnx("WEEK.6", date, 0) as date, sum(have) as want
from have
where weekday(date) in (1,6,7)
group by calculated date;
quit;

data want;
merge have Fridays(in=friday);
by date;
if not friday then want = have;
if weekday(date) in (1, 7) then want = 0;
run;

proc print data=want; run;``````
PG

All Replies
Super User
Posts: 23,724

## Re: accumulative sum with condition

How are you defining the rules? Is it just for this Week's data or all weeks? Are all Fridays only for the weekend?
Solution
‎09-25-2015 06:23 AM
Posts: 5,529

## Re: accumulative sum with condition

Here is one way to do this:

``````data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
;

proc sql;
create table Fridays as
select intnx("WEEK.6", date, 0) as date, sum(have) as want
from have
where weekday(date) in (1,6,7)
group by calculated date;
quit;

data want;
merge have Fridays(in=friday);
by date;
if not friday then want = have;
if weekday(date) in (1, 7) then want = 0;
run;

proc print data=want; run;``````
PG
Frequent Contributor
Posts: 102

## Re: accumulative sum with condition

here is another way;

data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
;
proc sort; by descending date;

data want; set have;
day=weekday(date); drop day sum;
want=have;
if weekday(date)=1 then do; sum+have; want=0; end;       *if   sunday;
if weekday(date)=7 then do; sum+have; want=0; end;       *if   saturday;
if weekday(date)=6 then do; sum+have; want=sum; sum=0; end;  *if   friday;
proc sort; by date;
proc print; run;

Posts: 5,529

## Re: accumulative sum with condition

@Jim_G, Nice and simple solution, as long as there are no holes in the time series. Try it, for example, with

``````data have;
input date :yymmdd. have;
format date weekdate37.;
datalines;
2015-09-01 2
2015-09-02 3
2015-09-03 4
2015-09-04 5
2015-09-05 2
2015-09-06 5
2015-09-12 2
2015-09-13 5
;``````
PG
Frequent Contributor
Posts: 102

## Re: accumulative sum with condition

Thanks @PGStats I thought about a little error message if the days were not consecutive.
Occasional Contributor
Posts: 6

## Re: accumulative sum with condition

Thank you so much PS stats! It works perfect!

Occasional Contributor
Posts: 6

## Re: accumulative sum with condition

@Reeza, it is for all Fridays if the day I run SAS is on Monday.
Occasional Contributor
Posts: 6

## Re: accumulative sum with condition

@PGStats, thank you! I will try it when back to work on Monday.
🔒 This topic is solved and locked.