topic Re: sum of a series for panel data in SAS Programming

sum of a series for panel data

Hannah_Mode — Mon, 24 Sep 2018 19:38:56 GMT

Hi SAS experts,

I want to calculate the attached formula for a panel data. So for firm i, at each year, I want to calculate sum of the following formula. Rick Wicklin has a blog about summation using proc IML, but I need to account for the time too. Can you let me know how can I revise his codes to include time too?

proc iml;

start SumSeries(n);
i = 1: (n); /* index of terms */
return( sum(sales *( ln(sales))) ); /* sum of terms */
finish;

Re: sum of a series for panel data

Reeza — Mon, 24 Sep 2018 19:43:20 GMT

How is your data structured? Do you need to use IML?

Re: sum of a series for panel data

Hannah_Mode — Mon, 24 Sep 2018 19:50:46 GMT

Hi Reeza,

Thank you for replying to my post. The data is an unbalanced panel for 200 firms from 2009 to 2015. I have a variable called number of segments which is time invariant. Sales is also a time variant variable. I got the proc IML from Rick Wicklin's blog, I don't have to use proc iml, this is what I found.

Re: sum of a series for panel data

Reeza — Mon, 24 Sep 2018 19:56:39 GMT

Then I would recommend using Base SAS and skipping IML otherwise you're learning two languages really.

In general, you can use the SUM() function to sum within a row and you use PROC MEANS to sum within a column. If you want to do a cumulative sum, you likely will need a data step, but it really depends on your data structure. You'll need to provide sample data (preferably in a data step) if you want demo code. In general, the more details in your question/post the more likely you are to get a fast, accurate solution.

If you have SAS/ETS PROC EXPAND can be an option but for what you're doing I don't think it is appropriate.

@Hannah_Mode wrote:

Hi Reeza,

Thank you for replying to my post. The data is an unbalanced panel for 200 firms from 2009 to 2015. I have a variable called number of segments which is time invariant. Sales is also a time variant variable. I got the proc IML from Rick Wicklin's blog, I don't have to use proc iml, this is what I found.

Re: sum of a series for panel data

Hannah_Mode — Mon, 24 Sep 2018 20:06:01 GMT

I'd appreciate a sample code. Here is the data structure:

Company ID Year Sales Number of segments

1 2009 2000.000 3.00
2 2009 300.000 2.00
3 2009 400.000 1.00
4 2009 500.000 5.00
5 2009 1200.000 10.00
1 2010 2200.000 3.00
2 2010 400.000 2.00
3 2010 800.000 1.00
4 2010 500.000 5.00
5 2010 600.000 10.00
1 2011 1800.000 3.00
2 2011 1200.000 2.00
3 2011 500.000 1.00
4 2011 600.000 5.00
5 2011 700.000 10.00

Re: sum of a series for panel data

Reeza — Mon, 24 Sep 2018 20:08:48 GMT

Can you provide the expected output for those records to verify the code against it, to ensure the implementation is correct?

Re: sum of a series for panel data

Ksharp — Tue, 25 Sep 2018 13:36:35 GMT

What Sales ikt stands for ? How to count Sales ikt for k=1 to Number of segments ?

Re: sum of a series for panel data

Hannah_Mode — Tue, 25 Sep 2018 21:38:11 GMT

k is number of segments, i means firm i, and t shows the period, let's say we have Microsoft and k for microsoft is 4, there are sales data for micorsoft for each segment for 3 periods and this is how the series look like:

(Sales)(k=1)(t=1)*ln(1/sales), so the sum is the sum of sales*ln(1/sales) for each firm , for each period and for each segment.