sum of a series for panel data

[Hannah_Mode]

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;

[Reeza]

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

[Hannah_Mode]

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. 

[Reeza]

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. 

---

[Hannah_Mode]

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

[Reeza]

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

[Ksharp]

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

[Hannah_Mode]

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.