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

Occasional Contributor
Posts: 13

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

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!

Posts: 3,167

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

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

Posts: 4,741

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

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;

Posts: 3,167

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

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

Super User
Posts: 6,785

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

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.

Discussion stats
• 4 replies
• 276 views
• 0 likes
• 4 in conversation