Help using Base SAS procedures

proc expand convert

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

proc expand convert

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?


Accepted Solutions
Solution
‎01-12-2018 11:16 PM
Contributor
Posts: 22

Re: proc expand convert

[ Edited ]
Posted in reply to KurtBremser

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


All Replies
Super User
Posts: 10,258

Re: proc expand convert

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...

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎01-12-2018 11:16 PM
Contributor
Posts: 22

Re: proc expand convert

[ Edited ]
Posted in reply to KurtBremser

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;  

  

Super User
Posts: 23,733

Re: proc expand convert

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?


 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 182 views
  • 0 likes
  • 3 in conversation