BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hdg
Obsidian | Level 7 hdg
Obsidian | Level 7


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!!

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

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

6 REPLIES 6
Linlin
Lapis Lazuli | Level 10

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

Ksharp
Super User
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

Haikuo
Onyx | Level 15

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

Linlin
Lapis Lazuli | Level 10

what is groupformat?

Haikuo
Onyx | Level 15

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

Linlin
Lapis Lazuli | Level 10

Thank you Haikuo! I have found it.  - Linlin

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
  • 6 replies
  • 858 views
  • 1 like
  • 4 in conversation