How to change "irregular total" to "daily average"?

[JeffNCSU]

Raw data look like:

Date  a

1     .

2    .

3    4    (note total number from day 1 to 3)

4  .

5 .

6  .

7  9  (note total number from day 4 to 7)

8 .

9 .

10 8

How to change these irregular total to daily average?

It looks the daily average gradually increase, so I didn't want to calculate as:

day 1-3: 4/3

day 4-7: 9/5

day 8-10: 8/3

thanks a lot!

[Haikuo]

I am sure there would be better solutions, while now, try this:

data have;

input Date  a;

cards;

1     .

2    .

3    4

4  .

5 .

6  .

7  9 

8 .

9 .

10 8

;

data want;

set have;

retain _cat;

length _cat $10.;

_mean+a;

i+1;

if i=1 then _cat=cats(date);

if not missing(a) then do;

  _mean=_mean/i;

  _cat=catx('-',_cat,date);

  output;

  call missing (_mean,i);

end;

keep _:;

run;

proc print;run;

Haikuo

[Patrick]

I'm sure someone will provide a more straightforward solution - but below code should work:

Data have;
infile datalines truncover dlm=' ';
input date a;
format date date9.;
datalines;
1 .
2 .
3 4    (note total number from day 1 to 3)
4 .
5 .
6 .
7 9    (note total number from day 4 to 7)
8 .
9 .
10 8
;
run;

data inter;
  set have ;
  retain date2 a2;
  if date>date2 then
    set have( keep=date a rename=(date=date2 a=a2) where=(a2 ne .) );
run;

proc sql;
  create table want as
  select *, a2/count(*) as avg
  from inter
  group by date2
  order by date
  ;
quit;

[Haikuo]

If to mimic Patrick's results using DOW, here is an approach:

data want;

do _n_=1 by 1 until (not missing(a));

set have;

_total+a;

end;

do until (not missing(a));

set have;

avg=_total/_n_;

output;

end;

call missing(_total);

run;

Haikuo

[Astounding]

A slight simplification.  Begin with:

data want;

set have (where=(a > .));

There are possible continuations that simplify as well.  For example, consider:

first_day = lag(date);

if _n_=1 then set have (keep=date rename=(date=first_day));

Details omitted at this point, unless this is really of interest to somebody.