topic Re: accumulative sum with condition in SAS Programming

accumulative sum with condition

last_one — Fri, 11 Sep 2015 21:26:56 GMT

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

Re: accumulative sum with condition

Reeza — Fri, 11 Sep 2015 21:52:09 GMT

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

Re: accumulative sum with condition

PGStats — Fri, 11 Sep 2015 22:24:44 GMT

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;

Re: accumulative sum with condition

last_one — Fri, 11 Sep 2015 22:34:27 GMT

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

Re: accumulative sum with condition

last_one — Fri, 11 Sep 2015 22:42:41 GMT

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

Re: accumulative sum with condition

Jim_G — Sat, 12 Sep 2015 22:00:11 GMT

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;

Re: accumulative sum with condition

PGStats — Sun, 13 Sep 2015 01:36:44 GMT

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

Re: accumulative sum with condition

Jim_G — Sun, 13 Sep 2015 12:38:21 GMT

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

Re: accumulative sum with condition

last_one — Mon, 14 Sep 2015 21:58:52 GMT

Thank you so much PS stats! It works perfect!