changing the data frequency

New Contributor
Posts: 4

changing the data frequency

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

Posts: 1,317

Re: changing the data frequency

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.


New Contributor
Posts: 4

Re: changing the data frequency

Hi TomKari,

thank you. I was thinking something like

Any suggestions?


Super User
Posts: 23,776

Re: changing the data frequency

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.

New Contributor
Posts: 4

Re: changing the data frequency

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?


Super User
Posts: 23,776

Re: changing the data frequency

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?

New Contributor
Posts: 4

Re: changing the data frequency

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!

Ask a Question
Discussion stats
  • 6 replies
  • 3 in conversation