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
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
Hi TomKari,
thank you. I was thinking something like
Any suggestions?
thanks
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.
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
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?
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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.