topic Re: proc expand convert in SAS Procedures

proc expand convert

vickyCh — Fri, 12 Jan 2018 08:45:05 GMT

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?

Re: proc expand convert

Kurt_Bremser — Fri, 12 Jan 2018 09:31:13 GMT

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-data-AKA-generate/ta-p/258712

Re: proc expand convert

Reeza — Fri, 12 Jan 2018 16:03:43 GMT

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?

Re: proc expand convert

vickyCh — Sat, 13 Jan 2018 04:16:22 GMT

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;