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

[mkeintz]

Some questions:

1. Do you want total-period medians over all 6 years, or do you want yearly "complimentary" medians?  By "complimentary" I mean excluding the company in hand.
2. Could you show, in a data step, a sample of what your incoming data looks like?
3. Are your data sorted by SIC (probably not, but it's worth knowing).

The crude way of doing this is to output each company's data N(sic)-1 times, where N(sic) is the number of industries in a given SIC.  And output it in such a way that there are N(sic) group id's (variable GROUPID), such that company i is not in groupid=I.  Then tell SAS to run a proc to generate medians for each sic/year/groupid.  Finally match those median to the company data.

How big is your dataset, i.e. how many total company/years do you have?

Edited addition:  And how many SIC's do you have?  Then we would know the average size of each SIC, and the number of times a median must be calculated.