topic Re: How to change "irregular total" to "daily average"? in SAS Programming

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

JeffNCSU — Tue, 14 Aug 2012 22:07:59 GMT

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!

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

Haikuo — Tue, 14 Aug 2012 22:49:16 GMT

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

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

Patrick — Tue, 14 Aug 2012 22:51:39 GMT

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;

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

Haikuo — Tue, 14 Aug 2012 23:07:11 GMT

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

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

Astounding — Wed, 15 Aug 2012 12:45:02 GMT

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.