BookmarkSubscribeRSS Feed
JeffNCSU
Calcite | Level 5

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!

4 REPLIES 4
Haikuo
Onyx | Level 15

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
Opal | Level 21

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
Onyx | Level 15

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
PROC Star

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.

SAS Innovate 2025: Call for Content

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!

Submit your idea!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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