Help using Base SAS procedures

cumulative question

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 79
Accepted Solution

cumulative question


Hello, I am trying to find the rolling cumulative average by month

Suppose I have a dataset

    vardateinfobegindate
ann12/29/2000212/1/2000
ann1/1/200131/1/2001
ann1/2/200151/1/2001
joe9/20/200169/1/2001
joe9/21/200179/1/2001
joe9/24/200189/1/2001

For each row I want to compute the rolling avg by each month on each available date grouped by var

So for example whatever dates are available within the month for each var i want to compute the average upto that date.

Thanks for your help on this!

Example

    vardateinfobegindateinfo avg
ann12/29/2000212/1/20002
ann1/1/200131/1/20013
ann1/2/200151/1/2001(3+5)/2
joe9/20/200169/1/20016
joe9/21/200179/1/2001(6+7)/2
joe9/24/200189/1/2001(6+7+8)/3

Thanks!!


Accepted Solutions
Solution
‎04-10-2012 02:35 PM
Super Contributor
Posts: 1,636

Re: cumulative question

data have;

informat date begindate mmddyy10.;

format date begindate mmddyy10.;

input var $ date info begindate ;

cards;

ann 12/29/2000 2 12/1/2000

ann 1/1/2001 3 1/1/2001

ann 1/2/2001 5 1/1/2001

joe 9/20/2001 6 9/1/2001

joe 9/21/2001 7 9/1/2001

joe 9/24/2001 8 9/1/2001

;

data want(drop=count total);

  set have;

  retain count total;

  by var begindate;

  if first.begindate then do;

     count=0;

     total=0; end;

      count+1;

      total+info;

      info_ave=total/count;

run;

proc print;run;

                    obs          date     begindate    var    info    info_ave

                     1     12/29/2000    12/01/2000    ann      2        2.0

                     2     01/01/2001    01/01/2001    ann      3        3.0

                     3     01/02/2001    01/01/2001    ann      5        4.0

                     4     09/20/2001    09/01/2001    joe      6        6.0

                     5     09/21/2001    09/01/2001    joe      7        6.5

                     6     09/24/2001    09/01/2001    joe      8        7.0

Linlin

View solution in original post


All Replies
Solution
‎04-10-2012 02:35 PM
Super Contributor
Posts: 1,636

Re: cumulative question

data have;

informat date begindate mmddyy10.;

format date begindate mmddyy10.;

input var $ date info begindate ;

cards;

ann 12/29/2000 2 12/1/2000

ann 1/1/2001 3 1/1/2001

ann 1/2/2001 5 1/1/2001

joe 9/20/2001 6 9/1/2001

joe 9/21/2001 7 9/1/2001

joe 9/24/2001 8 9/1/2001

;

data want(drop=count total);

  set have;

  retain count total;

  by var begindate;

  if first.begindate then do;

     count=0;

     total=0; end;

      count+1;

      total+info;

      info_ave=total/count;

run;

proc print;run;

                    obs          date     begindate    var    info    info_ave

                     1     12/29/2000    12/01/2000    ann      2        2.0

                     2     01/01/2001    01/01/2001    ann      3        3.0

                     3     01/02/2001    01/01/2001    ann      5        4.0

                     4     09/20/2001    09/01/2001    joe      6        6.0

                     5     09/21/2001    09/01/2001    joe      7        6.5

                     6     09/24/2001    09/01/2001    joe      8        7.0

Linlin

Super User
Posts: 9,676

Re: cumulative question

data have;
informat date begindate mmddyy10.;
format date begindate mmddyy10.;
input var $ date info begindate ;
cards;
ann 12/29/2000 2 12/1/2000
ann 1/1/2001 3 1/1/2001
ann 1/2/2001 5 1/1/2001
joe 9/20/2001 6 9/1/2001
joe 9/21/2001 7 9/1/2001
joe 9/24/2001 8 9/1/2001
;
run;
data want(drop=count total);
  set have;
  retain count total;
  by var date groupformat;
  format date yymmd.;
  if first.date then do;
     count=0;
     total=0; end;
      count+1;
      total+info;
      info_ave=total/count;
run;


Ksharp

Respected Advisor
Posts: 3,124

Re: cumulative question

Nice, first time see 'groupformat' in action! Thanks for sharing!

Super Contributor
Posts: 1,636

Re: cumulative question

what is groupformat?

Respected Advisor
Posts: 3,124

Re: cumulative question

LinLin, do a google on "sas groupformat", you will know it instantly.  for some reason, I can't paste links.

Super Contributor
Posts: 1,636

Re: cumulative question

Thank you Haikuo! I have found it.  - Linlin

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 231 views
  • 1 like
  • 4 in conversation