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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 824 views
  • 0 likes
  • 4 in conversation