DATA Step, Macro, Functions and more

Computation of the industry mean after excluding the firm-year (Revisit)

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Computation of the industry mean after excluding the firm-year (Revisit)

Hi,

 

When I want to compute industry mean of other firms after excluding the firm-year, I find the following code (variable name: delta, the current dataset: c15, new dataset: ind1) from this community.

However, it is not run well. Further, I don't understand how this code computes the industry mean from the industry sum.

 

proc sql;

create table ind1 as

select *,(((select sum(delta) from c15 where cyear=a.cyear and sic=a.sic )-a.delta)/((select count(*) from c15 where cyear=a.cyear and sic=a.sic )-1)) as mean_delta

from c15 as a;

quit;

 

Thank you very much in advance for your advice


Accepted Solutions
Solution
‎01-05-2018 01:58 PM
Respected Advisor
Posts: 4,673

Re: Computation of the industry mean after excluding the firm-year (Revisit)

@joon1

Using the code @Reeza posted with your sample data and things appear to work. Does that answer your question?

data c15;
  input firmid cyear delta sic;
  cards;
10884 1993 0.0168 100
11993 1993 0.01202 1000
4205 1993 0.03925 1040
5560 1993 0.01296 1040
5686 1993 0.05542 1040
7881 1993 0.02544 1040
11985 1993 0.01574 1040
3153 1993 0.01304 1044
10174 1993 0.00609 1044
1678 1993 0.03234 1311
6819 1993 0.05358 1311
7017 1993 0.02803 1311
7276 1993 0.00745 1311
7620 1993 0.03125 1311
7912 1993 0.02168 1311
8068 1993 0.02743 1311
8468 1993 0.02525 1311
8549 1993 0.05271 1311
8627 1993 0.03238 1311
8655 1993 0.02876 1311
9406 1993 0.03188 1311
11038 1993 0.03298 1311
11558 1993 0.0175 1311
11923 1993 0.08634 1311
14878 1993 0.03137 1311
14934 1993 0.14321 1311
15291 1993 0.08339 1311
23047 1993 0.44653 1311
;
run;

/* option 1 */
proc sql;
  create table want1 as
    select *, 
      ( sum(delta) - a.delta ) /  ( n(delta) - 1 ) as mean_minusFirmYear
    from c15 as a
      group by cyear, sic
        order by firmid, cyear, delta, sic
  ;
quit;

/* option 2 */
proc sql;
  create table want2 as
    select *,
    (((select sum(delta) from c15 where cyear=a.cyear and sic=a.sic )-a.delta)
      /
    ((select count(*) from c15 where cyear=a.cyear and sic=a.sic )-1))
      as mean_minusFirmYear
        from c15 as a
          order by firmid, cyear, delta, sic
  ;
quit;

/* test if want1 and want2 contain the same data */
proc compare data=want1 compare=want2;
run;

View solution in original post


All Replies
Super User
Posts: 23,293

Re: Computation of the industry mean after excluding the firm-year (Revisit)

You calculate the average by SUM(X's) / N(X's). 

To calculate the Average, without Xi then you can do:

 

Sum(X's) - Xi / N - 1

 

 

select *,

 

(((select sum(delta) from c15 where cyear=a.cyear and sic=a.sic )-a.delta) <- Sum(X's) - Xi

   /

((select count(*) from c15 where cyear=a.cyear and sic=a.sic )-1)) <- N - 1

 

as mean_delta

from c15 as a;

 

 

 

This could probably be simplified to, but I didn't test it. 

 

select *, 
      ( sum(delta) - a.delta ) /  ( n(delta) - 1 ) as mean_minusFirmYear
from mean_delta
group by year, sic;
quit;

If this is beyond your current programming skills it may be worth breaking it out into several data steps or SQL steps if that would help. 

Respected Advisor
Posts: 4,673

Re: Computation of the industry mean after excluding the firm-year (Revisit)

@joon1

If you want us to post tested code then please always provide representative sample data in the form of a tested SAS data step generating such data.

Contributor
Posts: 20

Re: Computation of the industry mean after excluding the firm-year (Revisit)

Hi,

 

The following is the sample data.

data have;
    input firmid cyear     delta sic;

cards;

 

1088419930.0168100
1199319930.012021000
420519930.039251040
556019930.012961040
568619930.055421040
788119930.025441040
1198519930.015741040
315319930.013041044
1017419930.006091044
167819930.032341311
681919930.053581311
701719930.028031311
727619930.007451311
762019930.031251311
791219930.021681311
806819930.027431311
846819930.025251311
854919930.052711311
862719930.032381311
865519930.028761311
940619930.031881311
1103819930.032981311
1155819930.01751311
1192319930.086341311
1487819930.031371311
1493419930.143211311
1529119930.083391311
2304719930.446531311

 

I tried your suggested code, but it is hard to complete the programming. Also breaking down multiple data steps is not easy.

 

proc sql;

create table want as

select *,( sum(delta) - a.delta ) / ( n(delta) - 1 ) as indmean,

group by cyear sic,

from have as a;

quit;

 

Thank you very much in advance for your help

 

 

Solution
‎01-05-2018 01:58 PM
Respected Advisor
Posts: 4,673

Re: Computation of the industry mean after excluding the firm-year (Revisit)

@joon1

Using the code @Reeza posted with your sample data and things appear to work. Does that answer your question?

data c15;
  input firmid cyear delta sic;
  cards;
10884 1993 0.0168 100
11993 1993 0.01202 1000
4205 1993 0.03925 1040
5560 1993 0.01296 1040
5686 1993 0.05542 1040
7881 1993 0.02544 1040
11985 1993 0.01574 1040
3153 1993 0.01304 1044
10174 1993 0.00609 1044
1678 1993 0.03234 1311
6819 1993 0.05358 1311
7017 1993 0.02803 1311
7276 1993 0.00745 1311
7620 1993 0.03125 1311
7912 1993 0.02168 1311
8068 1993 0.02743 1311
8468 1993 0.02525 1311
8549 1993 0.05271 1311
8627 1993 0.03238 1311
8655 1993 0.02876 1311
9406 1993 0.03188 1311
11038 1993 0.03298 1311
11558 1993 0.0175 1311
11923 1993 0.08634 1311
14878 1993 0.03137 1311
14934 1993 0.14321 1311
15291 1993 0.08339 1311
23047 1993 0.44653 1311
;
run;

/* option 1 */
proc sql;
  create table want1 as
    select *, 
      ( sum(delta) - a.delta ) /  ( n(delta) - 1 ) as mean_minusFirmYear
    from c15 as a
      group by cyear, sic
        order by firmid, cyear, delta, sic
  ;
quit;

/* option 2 */
proc sql;
  create table want2 as
    select *,
    (((select sum(delta) from c15 where cyear=a.cyear and sic=a.sic )-a.delta)
      /
    ((select count(*) from c15 where cyear=a.cyear and sic=a.sic )-1))
      as mean_minusFirmYear
        from c15 as a
          order by firmid, cyear, delta, sic
  ;
quit;

/* test if want1 and want2 contain the same data */
proc compare data=want1 compare=want2;
run;
Super User
Posts: 23,293

Re: Computation of the industry mean after excluding the firm-year (Revisit)

I’m not sure what you mean by complete the program. That is all you need for the calculation. 

 

If you’re not sure of the steps, do it manually first and keep track of how you’re calculating it. 

 

1. Calculate SUM and N for each SIC, YEAR => Proc Means

2. Merge it in with main data, by SIC and YEAR => PROC SQL, Data step

3. Do calculation identified in first post, subtract observation value and divide by N- 1 => Proc sql or data step, can be done in same step as #2

 

SQL does it in one step but the basic approach is quite straightforward. 


joon1 wrote:

Hi,

 

The following is the sample data.

data have;
    input firmid cyear     delta sic;

cards;

 

10884 1993 0.0168 100
11993 1993 0.01202 1000
4205 1993 0.03925 1040
5560 1993 0.01296 1040
5686 1993 0.05542 1040
7881 1993 0.02544 1040
11985 1993 0.01574 1040
3153 1993 0.01304 1044
10174 1993 0.00609 1044
1678 1993 0.03234 1311
6819 1993 0.05358 1311
7017 1993 0.02803 1311
7276 1993 0.00745 1311
7620 1993 0.03125 1311
7912 1993 0.02168 1311
8068 1993 0.02743 1311
8468 1993 0.02525 1311
8549 1993 0.05271 1311
8627 1993 0.03238 1311
8655 1993 0.02876 1311
9406 1993 0.03188 1311
11038 1993 0.03298 1311
11558 1993 0.0175 1311
11923 1993 0.08634 1311
14878 1993 0.03137 1311
14934 1993 0.14321 1311
15291 1993 0.08339 1311
23047 1993 0.44653 1311

 

I tried your suggested code, but it is hard to complete the programming. Also breaking down multiple data steps is not easy.

 

proc sql;

create table want as

select *,( sum(delta) - a.delta ) / ( n(delta) - 1 ) as indmean,

group by cyear sic,

from have as a;

quit;

 

Thank you very much in advance for your help

 

 


 

Contributor
Posts: 20

Re: Computation of the industry mean after excluding the firm-year (Revisit)

Thank you so much

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 196 views
  • 0 likes
  • 3 in conversation