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.
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
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.
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
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
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.