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!
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
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;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.