If first with multiple BY variables and counts/calculations

[mmaxiu]

Hi, everyone.

I'm using Compustat to attempt to compute an auditor industry specialist variable with the following method of calculation: Industry Specialization = Sum of square root of total assets of auditor clients (by gvkey) in a particular industry (sic code )divided by the sum of the square root of total assets of all clients of the auditor. Auditors with fewer than 10 clients per year are coded as =0.

I plan to pull the auditor, fiscal year, gvkey, sic code, and total assets for each firm on a firm-year basis. I was thinking it might be possible to use some form of nested if first. statement using a table with that data, but I'm stuck and don't really know where to start with the coding, as I'm a SAS beginner.

Any ideas?

Thanks!

[andreas_lds]

Other may be able to help you without further information, i am not. I need to the see an excerpt of the data as data step and the expected result using that data.

[mkeintz]

Here's a way to use proc freq to generate the results you want (for each auditor/sic generate the proportion of the auditor clients' total assets_squared arising from the sic):

I use the sashelp.class, modified, as an example.  I gave auditor 

data class;
  set sashelp.class (rename=(name=gvkey sex=sic));
  auditor=mod(_n_,3);
  assets_squared=age*age;
  if _n_= 10 then assets_squared=weight*weight;
  drop age height weight;
run;

proc sort;
  by auditor;
run;

proc freq ;
  by auditor;
  table sic / out=want (rename=(count=assets_squared));
  weight assets_squared;
run;

It uses proc freq, because that proc generates percentages of assets_squared for each sic within each auditor.  You can add a "noprint" option to the proc freq statement to get the WANT dataset without the printed table.  

You could sort the WANT dataset by

    auditor  descending percent

and then the first obs for each auditor will be the most "specialized" sic for that auditor.

[mmaxiu]

Thanks everyone for your help so far! Just wanted to add a few more details because I failed to mention about the fiscal year. Here is a look at the data table I have in SAS. For every combination of au (auditor) and fyear (fiscal year), I'm wanting to get a count of the unique gvkeys associated with that auditor and fiscal year. Then, if that gvkey count is less than 10 I want to have a variable called ISPEC that takes on a value of zero. If the value is 10 or greater, I need to compute the ISPEC variable based on the SIC2 code: For each combination of auditor, fiscal year, and SIC2 code, I need to compute the sum of the square root of at (total  assets) and then divide that by the sum of the square root of total assets of all gvkeys associated with a particular combination of auditor and fiscal year. 

[PaigeMiller]

Industry Specialization = Sum of square root of total assets of auditor clients (by gvkey) in a particular industry (sic code )divided by the sum of the square root of total assets of all clients of the auditor.

Compute the square roots of the variables on each row in a DATA step. Compute the sum of the square roots by gvkey and sic code by using PROC MEANS or PROC SUMMARY. (Do not try to compute sums by gvkey/sic code by yourself, SAS has already done this for you). Then, once done, do the division in another data step.

Working with sum of square roots seems somewhat strange way to calculate something. Are you sure this is written correctly? Should it should say "sum of squares"?