BookmarkSubscribeRSS Feed
loggy
Calcite | Level 5

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

6 REPLIES 6
TomKari
Onyx | Level 15

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

loggy
Calcite | Level 5

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...

Any suggestions?

thanks

Reeza
Super User

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...

loggy
Calcite | Level 5
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

Reeza
Super User

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?

loggy
Calcite | Level 5

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 620 views
  • 0 likes
  • 3 in conversation