Help using Base SAS procedures

SAS Roll_up Summary

Reply
N/A
Posts: 1

SAS Roll_up Summary

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
Trusted Advisor
Posts: 1,300

Re: SAS Roll_up Summary

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
Respected Advisor
Posts: 3,124

Re: SAS Roll_up Summary

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

Contributor
Posts: 44

Re: SAS Roll_up Summary

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;

Super User
Posts: 9,691

Re: SAS Roll_up Summary

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

Respected Advisor
Posts: 3,124

Re: SAS Roll_up Summary

Liked! SQL is indeed a powerful tool!

Haikuo

Respected Advisor
Posts: 4,662

Re: SAS Roll_up Summary

Of course, as a rule, SQL is the only way to do this properly (duck :smileysillySmiley Happy 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
Trusted Advisor
Posts: 1,300

Re: SAS Roll_up Summary

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;

PROC Star
Posts: 7,366

Re: SAS Roll_up Summary

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;

Respected Advisor
Posts: 4,662

Re: SAS Roll_up Summary

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
Frequent Contributor
Posts: 101

Re: SAS Roll_up Summary

It doesn't get any easier than this:

data want (drop=_Smiley Happy;

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.


Super Contributor
Posts: 1,636

Re: SAS Roll_up Summary

Nice!

if _n_ <= 10 then cum_sum = .;

should be

if _n_ < 10 then cum_sum = .;

Frequent Contributor
Posts: 101

Re: SAS Roll_up Summary

Good catch. Thanks.

Valued Guide
Posts: 765

Re: SAS Roll_up Summary

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;

Respected Advisor
Posts: 3,124

Re: SAS Roll_up Summary

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

Haikuo

Ask a Question
Discussion stats
  • 15 replies
  • 503 views
  • 2 likes
  • 10 in conversation