accumulative sum with condition

[last_one]

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

[Reeza]

How are you defining the rules? Is it just for this Week's data or all weeks? Are all Fridays only for the weekend?

[PGStats · Accepted solution]

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;

[Jim_G]

 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;

[PGStats]

@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
;

[Jim_G]

Thanks @PGStats I thought about a little error message if the days were not consecutive.

[last_one]

Thank you so much PS stats! It works perfect!

[last_one]

@Reeza, it is for all Fridays if the day I run SAS is on Monday.

[last_one]

@PGStats, thank you! I will try it when back to work on Monday.