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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.