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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
vickyCh
Obsidian | Level 7

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;  

  

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Obsidian | Level 7

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
Super User

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?


 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 916 views
  • 0 likes
  • 3 in conversation