topic changing the data frequency in SAS Programming

changing the data frequency

loggy — Sun, 17 Jun 2012 12:49:30 GMT

Dear R users,

I have a panel data set (in MS excel) on prices across countries and time

country time price

1 "23/11/08" 2

1 "28/12/08" 3

1 "25/01/09" 4

1 "22/02/09" 5

1 "29/03/09" 6

1 "26/04/09" 32

1 "24/05/09" 23

1 "28/06/09" 32

2 "26/10/08" 45

2 "23/11/08" 46

2 "21/12/08" 90

2 "18/01/09" 54

2 "15/02/09" 65

2 "16/03/09" 77

2 "12/04/09" 7

2 "10/05/09" 6

As you can see,

1)the start and end date of the time series for countries 1 and 2 are

different. For example, for country 1 the time series begins on

"23/11/08" while for country 2 the time series begins on "26-10-2008”.

2)My data on prices are available every 28 days (or equivalently every 4

weeks). So, each observation is a 4-week average. But in some cases I

have jumps (35 days or 29 days instead of

28 days). Examples are:

"28/12/08" to "28/12/08" , from 22/02/09" to "29/03/09", etc

My goal is to have a unified sequence of dates across countries.

Otherwise I will not be able to do further data/econometric analysis,

Unless you have different suggestion, I want to take what I have and

calculate monthly average prices. To be more specific, I want to change the frequency of the time series (probably using the EXPAND procedure?)

Put differently, I want to interpolate the 4-week average prices to monthly average prices.

The problem is also the jumps where I have 5 weeks in some cases and I want to find the monthly average of it.

I am a new user of SAS which means that a suggestion, although welcome, will not be very helpful. I would be grateful if you could provide an exact code adjusted to the above example

If parts of my question are not very understandable, please feel free to ask me

thank you

Re: changing the data frequency

TomKari — Sun, 17 Jun 2012 17:20:21 GMT

If you have access to it, take a look at PROC EXPAND. It's designed for operations like this, but it's hard to tell if it meets your specific requirement.

Tom

Re: changing the data frequency

loggy — Mon, 18 Jun 2012 17:16:30 GMT

Hi TomKari,

thank you. I was thinking something like

http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_expand_sect020.htm

Any suggestions?

thanks

Re: changing the data frequency

Reeza — Mon, 18 Jun 2012 17:28:10 GMT

If you navigate to examples within proc expand you can see SAS provides an example of interpolating irregular data with sample code, which is almost identical to your request.

http://support.sas.com/documentation/cdl/en/etsug/63348/HTML/default/viewer.htm#etsug_expand_sect033.htm

Re: changing the data frequency

loggy — Mon, 18 Jun 2012 18:04:21 GMT

Thank you Reeza.

What worries me is that in my case each data point is a a 4-week average. so to form a week I need some days more. In that example that is providedby SAS we do not know if each data point is a value recorded at that SPECIFIC date or something else. Or it does not matter?

Thanks

Re: changing the data frequency

Reeza — Mon, 18 Jun 2012 18:27:19 GMT

Why not expand it back to daily data then first? Neither is correct, but from a practical perspective, apply the average to each day of your 4 week period and then roll up into monthly averages?

Re: changing the data frequency

loggy — Mon, 18 Jun 2012 18:48:07 GMT

Good idea! If I understand correctly, you mean to interpolate the 4 week average to daily and then interpolate the daily to monthly?

Thanks again!