proc expand convert

[vickyCh]

We want to convert daily data to weekly data with Proc Expand - Convert statement.

The coding is as follows:

proc expand data= vickywant out= Agg_want_week
from= day to= week;
by symbol;
id date;
convert rate=weekly_rate / observed=AVERAGE;
convert price=weekly_price / observed=end;
run;

part of the data is as follows:

symbol	date	rate		price
abc	2007/1/3	0.0024		41.28
abc	2007/1/4	0.0015		41.34
abc	2007/1/5	-0.0017		41.27
abc	2007/1/8	-0.0006		40.77
abc	2007/1/9	0.0003		40.77
abc	2007/1/10	-0.0012		40.72
abc	2007/1/11	0.0007		40.75
abc	2007/1/12	-0.0025		40.65
abc	2007/1/16	0.0030		40.77
abc	2007/1/17	0.0005		40.79
abc	2007/1/18	-0.0005		40.77
abc	2007/1/19	-0.0005		40.75
abc	2007/1/22	0.0003		40.75
abc	2007/1/23	0.0004		40.75
abc	2007/1/24	0.0007		40.78
abc	2007/1/25	-0.0012		40.73
abc	2007/1/26	-0.0002		40.72
abc	2007/1/29	0.0024		40.72
abc	2007/1/30	0.0010		40.76
abc	2007/1/31	0.0010		40.80
abc	2007/2/1	0.0049		41.00
abc	2007/2/2	0.0037		41.15
abc	2007/2/5	-0.0012		41.10
abc	2007/2/6	0.0002		41.11
abc	2007/2/7	0.0007		41.14
abc	2007/2/8	0.0002		41.15
abc	2007/2/9	-0.0012		41.10
abc	2007/2/12	0.0024		41.20
abc	2007/2/13	0.0012		41.25
abc	2007/2/14	0.0012		41.30
abc	2007/2/15	-0.0015		41.24
abc	2007/2/16	0.0019		41.32
abc	2007/2/20	0.0002		41.33
abc	2007/2/21	-0.0024		41.23
abc	2007/2/22	-0.0005		41.21
abc	2007/2/23	-0.0051		41.00
abc	2007/2/26	0.0032		41.13
abc	2007/2/27	-0.0078		40.81
abc	2007/2/28	0.0047		41.00
abc	2007/3/1	0.0067		41.00
abc	2007/3/2	0.0039		41.00

What we really want is as follows:

1) The average rate of each week (might be 3, 4, or 5 days per week).

2) The last price of each week (might be 3, 4, or 5 days per week).

However we couldn't get what we want with the above coding. Is there something wrong?

[Kurt_Bremser]

Since I don't have ETS at hand, I solve such tasks with data step logic:

data have;
input symbol $ date :yymmdd10. rate price;
format date yymmdds10.;
cards;
abc 2007/1/3 0.0024 41.28
abc 2007/1/4 0.0015 41.34
abc 2007/1/5 -0.0017 41.27
abc 2007/1/8 -0.0006 40.77
abc 2007/1/9 0.0003 40.77
abc 2007/1/10 -0.0012 40.72
abc 2007/1/11 0.0007 40.75
abc 2007/1/12 -0.0025 40.65
abc 2007/1/16 0.0030 40.77
abc 2007/1/17 0.0005 40.79
abc 2007/1/18 -0.0005 40.77
abc 2007/1/19 -0.0005 40.75
abc 2007/1/22 0.0003 40.75
abc 2007/1/23 0.0004 40.75
abc 2007/1/24 0.0007 40.78
abc 2007/1/25 -0.0012 40.73
abc 2007/1/26 -0.0002 40.72
abc 2007/1/29 0.0024 40.72
abc 2007/1/30 0.0010 40.76
;
run;

data intermediate;
set have;
myyear = year(date);
myweek = week(date);
run;

data want (
  keep=symbol myyear myweek avg_rate price
);
set intermediate;
by symbol myyear myweek;
retain sum_rate count;
if first.myweek
then do;
  sum_rate = 0;
  count = 1;
end;
else do;
  sum_rate + rate;
  count + 1;
end;
if last.myweek
then do;
  avg_rate = sum_rate / count;
  output;
end;
run;

Please take note of the proper way to supply example data in a data step. This allows all others to recreate your data with a simple copy/paste and submit, keeping all meta-information like variable types, lengths and formats. A macro to do this automatically can be found here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[vickyCh · Accepted solution]

Thank you very much for your effort, KurtBremser.

That was exactly what we wanted except a minor modification as I highlighted below. 

The first sum_rate should be rate instead of zero.

if first.myweek
then do;
  sum_rate = rate;
  count = 1;
end;
else do;
  sum_rate + rate;
  count + 1;
end;  

[Reeza]

However we couldn't get what we want with the above coding. Is there something wrong?

Why/how didn't you get what you wanted? Was there an error, did the numbers not turn out as expected?

---

@vickyCh wrote:

We want to convert daily data to weekly data with Proc Expand - Convert statement.

The coding is as follows:

proc expand data= vickywant out= Agg_want_week
from= day to= week;
by symbol;
id date;
convert rate=weekly_rate / observed=AVERAGE;
convert price=weekly_price / observed=end;
run;

part of the data is as follows:

symbol	date	rate		price
abc	2007/1/3	0.0024		41.28
abc	2007/1/4	0.0015		41.34
abc	2007/1/5	-0.0017		41.27
abc	2007/1/8	-0.0006		40.77
abc	2007/1/9	0.0003		40.77
abc	2007/1/10	-0.0012		40.72
abc	2007/1/11	0.0007		40.75
abc	2007/1/12	-0.0025		40.65
abc	2007/1/16	0.0030		40.77
abc	2007/1/17	0.0005		40.79
abc	2007/1/18	-0.0005		40.77
abc	2007/1/19	-0.0005		40.75
abc	2007/1/22	0.0003		40.75
abc	2007/1/23	0.0004		40.75
abc	2007/1/24	0.0007		40.78
abc	2007/1/25	-0.0012		40.73
abc	2007/1/26	-0.0002		40.72
abc	2007/1/29	0.0024		40.72
abc	2007/1/30	0.0010		40.76
abc	2007/1/31	0.0010		40.80
abc	2007/2/1	0.0049		41.00
abc	2007/2/2	0.0037		41.15
abc	2007/2/5	-0.0012		41.10
abc	2007/2/6	0.0002		41.11
abc	2007/2/7	0.0007		41.14
abc	2007/2/8	0.0002		41.15
abc	2007/2/9	-0.0012		41.10
abc	2007/2/12	0.0024		41.20
abc	2007/2/13	0.0012		41.25
abc	2007/2/14	0.0012		41.30
abc	2007/2/15	-0.0015		41.24
abc	2007/2/16	0.0019		41.32
abc	2007/2/20	0.0002		41.33
abc	2007/2/21	-0.0024		41.23
abc	2007/2/22	-0.0005		41.21
abc	2007/2/23	-0.0051		41.00
abc	2007/2/26	0.0032		41.13
abc	2007/2/27	-0.0078		40.81
abc	2007/2/28	0.0047		41.00
abc	2007/3/1	0.0067		41.00
abc	2007/3/2	0.0039		41.00

What we really want is as follows:

1) The average rate of each week (might be 3, 4, or 5 days per week).

2) The last price of each week (might be 3, 4, or 5 days per week).

However we couldn't get what we want with the above coding. Is there something wrong?

---