BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
iank131
Quartz | Level 8

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

*/

1 ACCEPTED SOLUTION

Accepted Solutions
iank131
Quartz | Level 8

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;

View solution in original post

3 REPLIES 3
Ksharp
Super User

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
Quartz | Level 8

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
Quartz | Level 8

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;

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 2226 views
  • 5 likes
  • 2 in conversation