DATA Step, Macro, Functions and more

accumulative sum with condition

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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?

 

 DateHave Want
Tuesday, September 01, 201522
Wednesday, September 02, 201533
Thursday, September 03, 201544
Friday, September 04, 2015512
Saturday, September 05, 201520
Sunday, September 06, 201550

 

 

Thanks


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Respected Advisor
Posts: 4,922

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

View solution in original post


All Replies
Super User
Posts: 19,800

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
Respected Advisor
Posts: 4,922

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: 95

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;

Respected Advisor
Posts: 4,922

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: 95

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.

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

Discussion stats
  • 8 replies
  • 281 views
  • 1 like
  • 4 in conversation