From daily to monthly

[Camilla]

Hello! Could you help me with the following?

I have time series data with 255 variables.

I have to convert all 255 companies' rate of return daily data  to monthly data from 2004 to 2016. I have done it with one variable using proc expand

proc expand data=work.risk

from=day to=month

out=avmonthly;

id Date;

Convert Rf/

observed =average;

run;

But how can I use this to matrix data with 255 variables. The data looks like this. There are only 3 companies shown. I have to use the program to 255 companies.

Date	SamsungElec	KEPCO	HyundaiMtr
2004-06-17	1.38	1.09	4.50
2004-06-18	-1.47	-1.08	-2.63
2004-06-19	-1.47	-1.08	-2.63
2004-06-20	-1.47	-1.08	-2.63
2004-06-21	2.41	2.45	0.49

Thank you!

[Reeza]

If you transpose your data so that its long instead of wide you can add a BY statement to your PROC EXPAND. 

You can do this via PROC TRANSPOSE. 

This will probably be useful later on as well. 

[Camilla]

Thank you!

[udo_sas]

Hello -

You may want to consider using PROC TIMESERIES instead.

For example:

proc timeseries data=work.risk out=work.want;

id date interval=month accumulate=mean;

var _numeric_;

run;

Thanks,
Udo

[Camilla]

Thank you!

[Camilla · Accepted solution]

Thank you for your responses. I tried the other way and it worked:

proc expand data=work.risk

from=day to=month

out=avmonthly;

id Date;

Convert SamsungElec--Hyundai Mtr/

observed =average;/

run;

[Reeza]

I didn't know you could list variables like that. Cool!