BookmarkSubscribeRSS Feed
sasuser00
Calcite | Level 5

I'd like to know how to do 10 day roll up summary. Thanks.

datecount10day sum
1/1/20122
1/2/20124
1/3/20123
1/4/20126
1/5/20127
1/6/20129
1/7/201265
1/8/20124
1/9/20128
1/10/201256164
1/11/20124166
1/12/20125167
1/13/20124168
1/14/20122164
1/15/20122159
1/16/20123153
1/17/2012492
1/18/2012391
1/19/2012285
1/20/2012433
1/21/2012534
1/22/2012332
1/23/2012533
1/24/2012839
1/25/2012946
1/26/2012043
1/27/2012746
1/28/2012649
1/29/2012653
1/30/20124594
15 REPLIES 15
FriedEgg
SAS Employee

data foo;

input date mmddyy10. count;

cards;

1/1/2012  2

1/2/2012  4

1/3/2012  3

1/4/2012  6

1/5/2012  7

1/6/2012  9

1/7/2012  65

1/8/2012  4

1/9/2012  8

1/10/2012 56

1/11/2012 4

1/12/2012 5

1/13/2012 4

1/14/2012 2

1/15/2012 2

1/16/2012 3

1/17/2012 4

1/18/2012 3

1/19/2012 2

1/20/2012 4

1/21/2012 5

1/22/2012 3

1/23/2012 5

1/24/2012 8

1/25/2012 9

1/26/2012 0

1/27/2012 7

1/28/2012 6

1/29/2012 6

1/30/2012 45

;

run;

data bar;

if _n_=1 then

  do i=1 to 9;

   set foo;

   call missing(sum);

   output;

  end;

sum=0;

set foo (firstobs=10);

do i=1 to 10;

  p=(_n_-1)+i;

  set foo point=p;

  sum+count;

end;

output;

drop i;

run;

datecountsum
189932.
189944.
189953.
189966.
189977.
189989.
1899965.
190004.
190018.
1900256164
190034166
190045167
190054168
190062164
190072159
190083153
19009492
19010391
19011285
19012433
19013534
19014332
19015533
19016839
19017946
19018043
19019746
19020649
19021653
190224594
Haikuo
Onyx | Level 15

Here we are, again. without involving ETS, in addition to FriedEgg's solution, here is another approach (credit to Howard, Ksharp):

data have;

infile cards truncover;

input date :mmddyy10. count;

format date mmddyy10.;

cards;

1/1/2012 2

1/2/2012 4

1/3/2012 3

1/4/2012 6

1/5/2012 7

1/6/2012 9

1/7/2012 65

1/8/2012 4

1/9/2012 8

1/10/2012 56

1/11/2012 4

1/12/2012 5

1/13/2012 4

1/14/2012 2

1/15/2012 2

1/16/2012 3

1/17/2012 4

1/18/2012 3

1/19/2012 2

1/20/2012 4

1/21/2012 5

1/22/2012 3

1/23/2012 5

1/24/2012 8

1/25/2012 9

1/26/2012 0

1/27/2012 7

1/28/2012 6

1/29/2012 6

1/30/2012 45

;

data want;

array v(0:9) _temporary_;

set have;

v(mod(_n_,10))=count;

if _n_>=10 then day_sum=sum( of v(*));

run;

proc print;run;

Haikuo

JasonDiVirgilio
Quartz | Level 8

Beat me to it...and that code is definitely slick and concise.  If it is too slick:


  data want;
    set have;
    array sumz {10};
    retain sumz rowcnt 0;

    rowcnt +1;
    sumz{rowcnt}=count;
    if _n_ >=10 then do;
      tendaycount=sum(of sumz1-sumz10);
      if _n_ in (10,20,30)then rowcnt=0;
    end;
    keep date1 count tendaycount;
run;

Ksharp
Super User

I have a SQL solution instead of it.Not need to pre-sort dataset.  Smiley Happy

data foo;
input date mmddyy10. count;
format date mmddyy10.;
cards;
1/1/2012  2
1/2/2012  4
1/3/2012  3
1/4/2012  6
1/5/2012  7
1/6/2012  9
1/7/2012  65
1/8/2012  4
1/9/2012  8
1/10/2012 56
1/11/2012 4
1/12/2012 5
1/13/2012 4
1/14/2012 2
1/15/2012 2
1/16/2012 3
1/17/2012 4
1/18/2012 3
1/19/2012 2
1/20/2012 4
1/21/2012 5
1/22/2012 3
1/23/2012 5
1/24/2012 8
1/25/2012 9
1/26/2012 0
1/27/2012 7
1/28/2012 6
1/29/2012 6
1/30/2012 45
;
run;
proc sql;
create table want as
 select date,count,case when date lt min(date)+9 then . else (select sum(count) from foo as a where a.date between b.date-9 and b.date) end as sum
  from foo as b;
quit;



Ksharp

Haikuo
Onyx | Level 15

Liked! SQL is indeed a powerful tool!

Haikuo

PGStats
Opal | Level 21

Of course, as a rule, SQL is the only way to do this properly (duck :smileysilly:) as in the bullet proof :

proc sql;

create table want as

select A.date, A.count, ifn(count(distinct B.date)=10, sum(B.count), .) as ten_day_sum

from foo as A left join foo as B

on B.date between intnx("DAY", A.date, -9, "Beginning") and A.date

group by A.date, A.count;

select * from want;

quit;

PG

PG
FriedEgg
SAS Employee

using macro and lag function:

%macro lag_sum(var,n);

%let lag_sum=lag(&var);

%do i=2 %to &n;

  %let lag_sum=&lag_sum,lag&i(&var);

%end;

sum_&var&n=sum(&lag_sum);

if _n_ < &n then call missing(sum_&var&n);

%mend;

data bar;

set foo;

%lag_sum(count,10);

run;

art297
Opal | Level 21

I like Haikuo's FIFO approach but, if you are concerned about 10 day sums and don't really have all dates, then you might need to expand the code a little.  E.g.:

%let roll=10;

data want;

  array v{0:%eval(&roll.-1)} _temporary_;

  array d{0:%eval(&roll.-1)} _temporary_;

  set have;

  v{mod(_n_,&roll.)}=count;

  d{mod(_n_,&roll.)}=date;

  do i=0 to 9;

    if not missing(d{i}) then do;

      if date-d{i} gt %eval(&roll.-1) then do;

        call missing(v{i});

        call missing(d{i});

      end;

    end;

  end;

  if _n_>=&roll. then day_sum=sum( of v{*});

run;

PGStats
Opal | Level 21

I agree with your point Arthur. Solutions that depend on sorted values can be guarantied to work but if you rely on complete data, that's more difficult to enforce. So better be safe than sorry... Moreover, the overhead cost can be minimal (I removed one data line to show the effect) :

data have;
infile cards truncover;
input date :mmddyy10. count;
format date mmddyy10.;
cards;
1/1/2012 2
1/2/2012 4
1/3/2012 3
1/4/2012 6
1/5/2012 7
1/6/2012 9
1/7/2012 65
1/8/2012 4
1/9/2012 8
1/10/2012 56
1/11/2012 4
1/12/2012 5
1/13/2012 4
1/14/2012 2
1/16/2012 3
1/17/2012 4
1/18/2012 3
1/19/2012 2
1/20/2012 4
1/21/2012 5
1/22/2012 3
1/23/2012 5
1/24/2012 8
1/25/2012 9
1/26/2012 0
1/27/2012 7
1/28/2012 6
1/29/2012 6
1/30/2012 45
;

%let wnd=10;
data want;
array d{0:0} _temporary_;
array s{0:&wnd.} _temporary_;
set have;
d{0} = 1 + coalesce(d{0}, date-1);
do while (d{0} < date);
s{mod(d{0}, &wnd)} = .; /* creates a hole when sum is incomplete (preferred)*/
*s{mod(d{0}, &wnd)} = 0; /* accepts incomplete sums */
d{0} + 1;
end;
s{mod(date, &wnd)} = count;
if nmiss(of s{*}) = 1 then day_sum = sum(of s{*});
else day_sum = .;
run;

proc print data=want; run;

PG

PG
FloydNevseta
Pyrite | Level 9

It doesn't get any easier than this:

data want (drop=_:);

set have;

retain _cum 0;

_cum + ( count - coalesce( lag10( count ),0 ));

if _n_ <= 10 then cum_sum = .;

else cum_sum = _cum;

run;

Each iteration of the data step adds to the cumulative sum the new value for count and subtracts the 10th lag of count.


Linlin
Lapis Lazuli | Level 10

Nice!

if _n_ <= 10 then cum_sum = .;

should be

if _n_ < 10 then cum_sum = .;

FloydNevseta
Pyrite | Level 9

Good catch. Thanks.

MikeZdeb
Rhodochrosite | Level 12

hi ... here's a tweak of SAS_Bigot's nice solution ...

data want (drop=_cum);

set have;

_cum +  sum(count, lag10(-count));

cum_sum = ifn(_n_ lt 10, . , _cum);

run;

Haikuo
Onyx | Level 15

Awesome! So far we have Array, Lag(), SQL, Macro, DOW, and let's see how far this great thread can lead us to!

Haikuo

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 15 replies
  • 1524 views
  • 2 likes
  • 10 in conversation