how to get industry median excluding focal firm

[yzs0056]

Hello,

I am a beginner in SAS, I hope I can get some help from the community.

I am trying to calculate the industry median R&D expense for each year excluding the focal firm, the industry is indicated by the 4-digit SIC code.

For example, assume the data is from 2000-2005 for 10 firms: A, B, C, D, E have same SIC code, and F, G, H, I, J have the same SIC code,  each firm has data covering those 6 years, how do I get the industry median RD expenses excluding each focal firm for each industry and year?

Thanks so much!

 yzs0056

[yzs0056]

Hello,

I am a beginner in SAS, I hope I can get some help from the community.

I am trying to calculate the industry median R&D expense for each year excluding the focal firm, the industry is indicated by the 4-digit SIC code.

For example, assume the data is from 2000-2005 for 10 firms: A, B, C, D, E have same SIC code, and F, G, H, I, J have the same SIC code,  each firm has data covering those 6 years, how do I get the industry median RD expenses excluding each focal firm for each industry and year?

Thanks so much!

 yzs0056

[ballardw]

---

@yzs0056 wrote:

Hello,

I am a beginner in SAS, I hope I can get some help from the community.

I am trying to calculate the industry median R&D expense for each year excluding the focal firm, the industry is indicated by the 4-digit SIC code.

For example, assume the data is from 2000-2005 for 10 firms: A, B, C, D, E have same SIC code, and F, G, H, I, J have the same SIC code,  each firm has data covering those 6 years, how do I get the industry median RD expenses excluding each focal firm for each industry and year?

Thanks so much!

 yzs0056

---

How do we know which is the "focal firm"? For each year? I have to assume that you have a variable that represents year.

Some example data that you can mention specific values/variable names is helpful. Also, do you need a data set (further computer use) or a report (people read the result) or possibly both?

Likely easiest would be to add a variable that indicates whether the specific record is from a "focal firm". The use a where clause in a procedure to exclude them. Supposing you have a variable named FocalFirm that has a value of 1 when the record is from a focal firm and 0 otherwise you might have something like:

proc means data=have median;

   where focalfirm=0;

   class SIC;

   var RandDExpense;

run;

Procs Means, Summary, Tabulate, Univariate and Report will generate medians.

[ballardw]

Hello,

I am a beginner in SAS, I hope I can get some help from the community.

I am trying to calculate the industry median R&D expense for each year excluding the focal firm, the industry is indicated by the 4-digit SIC code.

For example, assume the data is from 2000-2005 for 10 firms: A, B, C, D, E have same SIC code, and F, G, H, I, J have the same SIC code,  each firm has data covering those 6 years, how do I get the industry median RD expenses excluding each focal firm for each industry and year?

Thanks so much!

 yzs0056

[Reeza]

Can you do this for the base case, ie one firm? If so, you can then create a macro and use that. The usual methods won't work for a median type calculations so you do need to do it repetitively

Once you've got the base case working, you can turn it into a macro and call it once for each 'focal firm'.