SAS Programming to compute average CSR?

[srikanthyadav44]

dear all, 

I have the data on spending on CSR from 2015 to 2019 for companies in different industries. 

I have to compute the average CSR spending by each industry for every year from 2015 to 2019. 

in calculating the average CSR spending of the industry for the given year, the CSR spending of the given company should be excluded. 

For example, in 2015, for industry code 10, there are five companies, In calculating the average CSR spending for "Balrampur Chini Mills Ltd. " I should calculate the average for the remaining 4 companies excluding this particular company and for calculating the average CSR spending for "Avanti Feeds Ltd." I should exclude this particular company and the average should be computed for the remaining four companies. 

my data is in the following format 

Company_Name	year	code	CSR
Balrampur Chini Mills Ltd.	2015	10	9.2
Avanti Feeds Ltd.	2015	10	7.2
Sayaji Industries Ltd.	2015	10	1.6
Jubilant Foodworks Ltd.	2015	10	6.9
Apex Frozen Foods Ltd.	2015	10	2.2
Ruchi Soya Inds. Ltd.	2015	10	4.7
Jayant Agro-Organics Ltd.	2016	10	1.7
Godrej Agrovet Ltd.	2016	10	21.5
Piccadily Agro Inds. Ltd.	2016	10	5.3
Kothari Sugars & Chemicals Ltd.	2016	10	1.5
Kanco Tea & Inds. Ltd.	2016	10	0.8
Manpasand Beverages Ltd.	2016	10	4.3
Foods & Inns Ltd.	2016	10	0.5
Banswara Syntex Ltd.	2015	13	1.6
Flexituff Ventures Intl. Ltd.	2015	13	7.2
Raymond Ltd.	2015	13	5.3
Indo Count Inds. Ltd.	2015	13	1.1
Suryalata Spinning Mills Ltd.	2015	13	2.2

 In the above data, CSR stands for CSR spending and Code denotes industry classification code.

I need the output in the following format. 

Company_Name	year	code	CSR	average
Balrampur Chini Mills Ltd.	2015	10	9.2	4.52
Avanti Feeds Ltd.	2015	10	7.2	4.92
Sayaji Industries Ltd.	2015	10	1.6	6.04
Jubilant Foodworks Ltd.	2015	10	6.9	4.98
Apex Frozen Foods Ltd.	2015	10	2.2	5.92
Ruchi Soya Inds. Ltd.	2015	10	4.7	5.42
Jayant Agro-Organics Ltd.	2016	10	1.7	5.09
Godrej Agrovet Ltd.	2016	10	21.5
Piccadily Agro Inds. Ltd.	2016	10	5.3
Kothari Sugars & Chemicals Ltd.	2016	10	1.5
Kanco Tea & Inds. Ltd.	2016	10	0.8
Manpasand Beverages Ltd.	2016	10	4.3
Foods & Inns Ltd.	2016	10	0.5
Banswara Syntex Ltd.	2015	13	1.6
Flexituff Ventures Intl. Ltd.	2015	13	7.2
Raymond Ltd.	2015	13	5.3
Indo Count Inds. Ltd.	2015	13	1.1
Suryalata Spinning Mills Ltd.	2015	13	2.2

please suggest me an appropriate SAS CODE

i am attaching the .CSV file also. 

[Kurt_Bremser]

Why do you have different values for year 2015 and industry code 10, and why do you have missing values from 2016 onwards?

If you provide example data as a csv file, you must also provide the data step code to read it, so we can replicate your dataset as is.

---

@srikanthyadav44 wrote:

dear all, 

I have the data on spending on CSR from 2015 to 2019 for companies in different industries. 

I have to compute the average CSR spending by each industry for every year from 2015 to 2019. 

in calculating the average CSR spending of the industry for the given year, the CSR spending of the given company should be excluded. 

For example, in 2015, for industry code 10, there are five companies, In calculating the average CSR spending for "Balrampur Chini Mills Ltd. " I should calculate the average for the remaining 4 companies excluding this particular company and for calculating the average CSR spending for "Avanti Feeds Ltd." I should exclude this particular company and the average should be computed for the remaining four companies. 

my data is in the following format 

Company_Name	year	code	CSR
Balrampur Chini Mills Ltd.	2015	10	9.2
Avanti Feeds Ltd.	2015	10	7.2
Sayaji Industries Ltd.	2015	10	1.6
Jubilant Foodworks Ltd.	2015	10	6.9
Apex Frozen Foods Ltd.	2015	10	2.2
Ruchi Soya Inds. Ltd.	2015	10	4.7
Jayant Agro-Organics Ltd.	2016	10	1.7
Godrej Agrovet Ltd.	2016	10	21.5
Piccadily Agro Inds. Ltd.	2016	10	5.3
Kothari Sugars & Chemicals Ltd.	2016	10	1.5
Kanco Tea & Inds. Ltd.	2016	10	0.8
Manpasand Beverages Ltd.	2016	10	4.3
Foods & Inns Ltd.	2016	10	0.5
Banswara Syntex Ltd.	2015	13	1.6
Flexituff Ventures Intl. Ltd.	2015	13	7.2
Raymond Ltd.	2015	13	5.3
Indo Count Inds. Ltd.	2015	13	1.1
Suryalata Spinning Mills Ltd.	2015	13	2.2

 In the above data, CSR stands for CSR spending and Code denotes industry classification code.

I need the output in the following format. 

Company_Name	year	code	CSR	average
Balrampur Chini Mills Ltd.	2015	10	9.2	4.52
Avanti Feeds Ltd.	2015	10	7.2	4.92
Sayaji Industries Ltd.	2015	10	1.6	6.04
Jubilant Foodworks Ltd.	2015	10	6.9	4.98
Apex Frozen Foods Ltd.	2015	10	2.2	5.92
Ruchi Soya Inds. Ltd.	2015	10	4.7	5.42
Jayant Agro-Organics Ltd.	2016	10	1.7	5.09
Godrej Agrovet Ltd.	2016	10	21.5
Piccadily Agro Inds. Ltd.	2016	10	5.3
Kothari Sugars & Chemicals Ltd.	2016	10	1.5
Kanco Tea & Inds. Ltd.	2016	10	0.8
Manpasand Beverages Ltd.	2016	10	4.3
Foods & Inns Ltd.	2016	10	0.5
Banswara Syntex Ltd.	2015	13	1.6
Flexituff Ventures Intl. Ltd.	2015	13	7.2
Raymond Ltd.	2015	13	5.3
Indo Count Inds. Ltd.	2015	13	1.1
Suryalata Spinning Mills Ltd.	2015	13	2.2

please suggest me an appropriate SAS CODE

i am attaching the .CSV file also. 

---

[srikanthyadav44]

Dear Mr. Kurt_Bremser

greetings of the day 

thanks a lot for your prompt reply

i have calculated the values in the sample output manually as follows

=avg(7.2+1.6+6.9+2.2+4.7)=4.52

=AVG(9.2,1.6,6.9,2.2,4.7)=4.92

=AVERAGE(9.2,7.2,6.9,2.2,4.7)=6.04

As i mentioned in calculating the average for a particular company, value of that company should be excluded. that's why, i got different values. 

the sample output,  i have given only a model output for a few cells. so, there are no values from 2016 onwards 

please suggest SAS code with this condition 

[Tom · Accepted solution]

Seems simple enough in PROC SQL.

data have;
  infile cards dsd dlm='|';
  input Company_Name :$30. year code CSR;
cards;
Balrampur Chini Mills Ltd.|2015|10|9.2
Avanti Feeds Ltd.|2015|10|7.2
Sayaji Industries Ltd.|2015|10|1.6
Jubilant Foodworks Ltd.|2015|10|6.9
Apex Frozen Foods Ltd.|2015|10|2.2
Ruchi Soya Inds. Ltd.|2015|10|4.7
Jayant Agro-Organics Ltd.|2016|10|1.7
Godrej Agrovet Ltd.|2016|10|21.5
Piccadily Agro Inds. Ltd.|2016|10|5.3
Kothari Sugars & Chemicals Ltd.|2016|10|1.5
Kanco Tea & Inds. Ltd.|2016|10|0.8
Manpasand Beverages Ltd.|2016|10|4.3
Foods & Inns Ltd.|2016|10|0.5
Banswara Syntex Ltd.|2015|13|1.6
Flexituff Ventures Intl. Ltd.|2015|13|7.2
Raymond Ltd.|2015|13|5.3
Indo Count Inds. Ltd.|2015|13|1.1
Suryalata Spinning Mills Ltd.|2015|13|2.2
;

proc sql;
create table want as
select *
     , mean(csr) as mean_csr
     , n(csr) as n_companies
     , (sum(csr)-csr)/(n(csr)-1) as mean_other_csr
     , n(csr) - 1 as n_other_companies
from have
group by year, code
;

You might have to adjust if any of the records have a missing value for CSR.

                                                                                             mean_
                                                                                             other_     n_other_
Obs    Company_Name                      year    code     CSR    mean_csr    n_companies      csr      companies

  1    Ruchi Soya Inds. Ltd.             2015     10      4.7     5.30000         6         5.42000        5
  2    Apex Frozen Foods Ltd.            2015     10      2.2     5.30000         6         5.92000        5
  3    Balrampur Chini Mills Ltd.        2015     10      9.2     5.30000         6         4.52000        5
  4    Sayaji Industries Ltd.            2015     10      1.6     5.30000         6         6.04000        5
  5    Avanti Feeds Ltd.                 2015     10      7.2     5.30000         6         4.92000        5
  6    Jubilant Foodworks Ltd.           2015     10      6.9     5.30000         6         4.98000        5
  7    Banswara Syntex Ltd.              2015     13      1.6     3.48000         5         3.95000        4
  8    Flexituff Ventures Intl. Ltd.     2015     13      7.2     3.48000         5         2.55000        4
  9    Suryalata Spinning Mills Ltd.     2015     13      2.2     3.48000         5         3.80000        4
 10    Indo Count Inds. Ltd.             2015     13      1.1     3.48000         5         4.07500        4
 11    Raymond Ltd.                      2015     13      5.3     3.48000         5         3.02500        4
 12    Jayant Agro-Organics Ltd.         2016     10      1.7     5.08571         7         5.65000        6
 13    Foods & Inns Ltd.                 2016     10      0.5     5.08571         7         5.85000        6
 14    Manpasand Beverages Ltd.          2016     10      4.3     5.08571         7         5.21667        6
 15    Kanco Tea & Inds. Ltd.            2016     10      0.8     5.08571         7         5.80000        6
 16    Kothari Sugars & Chemicals Ltd    2016     10      1.5     5.08571         7         5.68333        6
 17    Piccadily Agro Inds. Ltd.         2016     10      5.3     5.08571         7         5.05000        6
 18    Godrej Agrovet Ltd.               2016     10     21.5     5.08571         7         2.35000        6

[Reeza]

For an average per industry you need the rough size of the industry and # of companies doesn't seem like a good measure.
You can easily calculate total CSR but the denominator is what I'd have trouble defining.

Assumes CSR is Corporate Social Responsibility

If CSR is as a percentage that may be ok to just average them.

[srikanthyadav44]

Dear Ms. Reeza

thanks for your response. 

in my dataset, the number of companies is not the same across all the industries. they vary from industry to industry and year to year also. 

and CSR represents the amount of CSR spent mentioned in millions. 

[Reeza]

If you're doing this for class, just use the average. If you're doing this for something else, you need to consider the industry size.