PROC EXPAND daily to weekly aggregation with missing values

[iank131]

I have daily data with missing values. I want to aggregate to weekly observations by taking the straight average of available values (not counting missing) in the week before the end of the week. I also want to get a weighted average of the available values.

Here is the sample code that I am trying, but I can't get the result I want. I realize that the weighted average is not in my code, but I can't even get it to do the straight average. I can't understand the results I get. I even try to transform the missing values into zeros, but still I can't understand what I am getting. luesCould you please help. Thanks!

Ian.

data ds_daily;

input id $ date value weight;

format date weekdatx17.;

datalines;

A 3 2 1

A 4 3 1

A 5 4 1

A 6 5 1

A 7 6 1

A 8 . .

A 9 . .

A 10 2 1

A 11 3 1

A 12 4 1

A 13 5 2

A 14 6 2

A 15 . .

A 16 . .

B 3 1 1

B 4 1 1

B 5 2 1

B 6 2 1

B 7 4 1

B 8 . .

B 9 . .

B 10 1 1

B 11 1 1

B 12 2 1

B 13 2 2

B 14 4 2

B 15 . .

B 16 . .

; run;

proc expand data=ds_daily out=ds_weekly from=day to=week align=end;

  convert value = w_value / transformin=( setmiss 0);

  id date; by id; run;

/* Want

id date w_value

A 8 4

A 15 5.57

B 8 2

B 15 2.29

*/

[Ksharp]

Not sure if the following is what you need.

data ds_daily;

input id $ date value weight;

if weekday(date)=2 then week+1;

format date weekdatx17.;

datalines;

A 3 2 1

A 4 3 1

A 5 4 1

A 6 5 1

A 7 6 1

A 8 . .

A 9 . .

A 10 2 1

A 11 3 1

A 12 4 1

A 13 5 2

A 14 6 2

A 15 . .

A 16 . .

B 3 1 1

B 4 1 1

B 5 2 1

B 6 2 1

B 7 4 1

B 8 . .

B 9 . .

B 10 1 1

B 11 1 1

B 12 2 1

B 13 2 2

B 14 4 2

B 15 . .

B 16 . .

;

run;

proc summary data=ds_daily;

by week;

var value;

weight weight;

output out=w mean=avg_value;

run;

[iank131]

Thanks Xia for your answer. It doesn't quite give me what I was looking for, but I get the idea. I had to add a few things to make it get the correct answer. See below.

In the meantime I found out how to do it using PROC EXPAND. I add this in reply to my own question. I changed the data slightly to make it give the same output as the code below.

data ds_daily;

input id $ date value weight;

format date weekdatx17.;

datalines;

A 2 1 1

A 3 2 1

A 4 3 1

A 5 4 1

A 6 5 1

A 7 6 1

A 8 . .

A 9 . .

A 10 2 1

A 11 3 1

A 12 4 1

A 13 5 2

A 14 6 2

A 15 . .

A 16 . .

B 2 1 1

B 3 1 1

B 4 1 1

B 5 2 1

B 6 2 1

B 7 4 1

B 8 . .

B 9 . .

B 10 1 1

B 11 1 1

B 12 2 1

B 13 2 2

B 14 4 2

B 15 . .

B 16 . .

; run;

data xia_a; set ds_daily; by id;

  if first.id then week=0; if weekday(date)=1 then week+1; run;

proc summary data=xia_a;

  by id week; var value; weight weight;

  output out=xia_b mean=avg_value; run;

data xia_c; set xia_a; if weekday(date) ne 7 then delete; run;

data xia_d (keep=id date avg_value);

  merge xia_c xia_b; by id week; if date=. then delete; run;

[iank131 · Accepted solution]

I found an answer to my own question in SAS GLOBAL FORUM 2011, Paper 417-2011, Expanding PROC EXPAND. :smileylaugh: Here is the full code including the data and the PROC EXPAND code.

data ds_daily;

input id $ date value weight;

format date weekdatx17.;

datalines;

A 2 1 1

A 3 2 1

A 4 3 1

A 5 4 1

A 6 5 1

A 7 6 1

A 8 . .

A 9 . .

A 10 2 1

A 11 3 1

A 12 4 1

A 13 5 2

A 14 6 2

A 15 . .

A 16 . .

B 2 1 1

B 3 1 1

B 4 1 1

B 5 2 1

B 6 2 1

B 7 4 1

B 8 . .

B 9 . .

B 10 1 1

B 11 1 1

B 12 2 1

B 13 2 2

B 14 4 2

B 15 . .

B 16 . .

; run;

data value_a; set ds_daily; product = value*weight; run;

proc expand data=value_a out=value_b from=day to=week align=end;

  convert product = total_product / method=aggregate observed=total transformin=(setmiss 0);

  convert weight  = total_wght / method=aggregate observed=total transformin=(setmiss 0);

  id date; by id; run;

data value_c (drop=total_product total_wght); set value_b; w_value=total_product/total_wght; run;