topic how to get industry median excluding focal firm in SAS Data Management

how to get industry median excluding focal firm

yzs0056 — Tue, 27 Nov 2018 22:02:00 GMT

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

Re: how to get industry median excluding focal firm

ballardw — Tue, 27 Nov 2018 23:12:27 GMT

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

Re: how to get industry median excluding focal firm

Reeza — Tue, 27 Nov 2018 23:39:09 GMT

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