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?
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;
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:
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;
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?
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 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: