how to compute the frequency of a variable?

[srikanthyadav44]

dear all 

my data is in the following format 

company_name	year	NIC
20 Microns Ltd.	2011	8
Ashapura Minechem Ltd.	2011	8
A D F Foods Ltd.	2011	10
Adani Wilmar Ltd.	2011	10
Agro Tech Foods Ltd.	2011	10
Ajanta Soya Ltd.	2011	10
Ambar Protein Inds. Ltd.	2011	10
Andrew Yule & Co. Ltd.	2011	10
Arcuttipore Tea Co. Ltd.	2011	10
Associated Alcohols & Breweries Ltd.	2011	11
Devyani International Ltd.	2011	11
Eco Friendly Food Processing Park Ltd.	2011	11
Elegant Floriculture & Agrotech (India) Ltd.	2011	11
Flex Foods Ltd.	2011	11
Freshtrop Fruits Ltd.	2011	12
Sree Jayalakshmi Autospin Ltd.	2011	12
A I Champdany Inds. Ltd.	2011	13
A K Spintex Ltd.	2011	13
A P M Industries Ltd.	2011	13
Aarvee Denims & Exports Ltd.	2011	13
Aditya Spinners Ltd.	2011	13
Alka India Ltd.	2011	13
Alok Industries Ltd.	2011	13
Alps Industries Ltd.	2011	13

i want the frequency of the NIC in next column as shown below 

company_name	year	NIC	NIC_Frequncy
20 Microns Ltd.	2011	8	2
Ashapura Minechem Ltd.	2011	8	2
A D F Foods Ltd.	2011	10	7
Adani Wilmar Ltd.	2011	10	7
Agro Tech Foods Ltd.	2011	10	7
Ajanta Soya Ltd.	2011	10	7
Ambar Protein Inds. Ltd.	2011	10	7
Andrew Yule & Co. Ltd.	2011	10	7
Arcuttipore Tea Co. Ltd.	2011	10	7
Associated Alcohols & Breweries Ltd.	2011	11	5
Devyani International Ltd.	2011	11	5
Eco Friendly Food Processing Park Ltd.	2011	11	5
Elegant Floriculture & Agrotech (India) Ltd.	2011	11	5
Flex Foods Ltd.	2011	11	5
Freshtrop Fruits Ltd.	2011	12	2
Sree Jayalakshmi Autospin Ltd.	2011	12	2
A I Champdany Inds. Ltd.	2011	13	8
A K Spintex Ltd.	2011	13	8
A P M Industries Ltd.	2011	13	8
Aarvee Denims & Exports Ltd.	2011	13	8
Aditya Spinners Ltd.	2011	13	8
Alka India Ltd.	2011	13	8
Alok Industries Ltd.	2011	13	8
Alps Industries Ltd.	2011	13	8

please suggest me a SAS to generate the frequency of the NIC as shown above 

thanking you in advance 

[PaigeMiller]

Use PROC FREQ, then merge the results with the original

UNTESTED CODE

proc freq data=have;
     tables nic/noprint out=nic_frequency;
run;
data want;
    merge have nic_frequency(keep=nic count);
    by nic;
run;

[srikanthyadav44]

thanks for your prompt reply
but, i need frequency as a separate variable as i shown my query.
please suggest me a code

[PaigeMiller]

I provided untested code

[srikanthyadav44]

thanks a lot for your prompt reply
i tried with this code
but i am not getting the output as i want

[PaigeMiller]

---

@srikanthyadav44 wrote:
thanks a lot for your prompt reply
i tried with this code
but i am not getting the output as i want

---

For your future benefit ... when you are not getting the output you want ... please SHOW US a screen capture of the output you are getting and explain what it is you want. If you just stop after you say "i am not getting the output as I want", we cannot help you, we don't know what that means, we don't know what you are seeing or what you want.

To provide a screen capture, please click on the "Insert Photos" icon to include your screen capture in your reply. Do not attach files.

[srikanthyadav44]

 i am attaching the output

[maguiremq · Accepted solution]

@PaigeMiller's code does provide that variable in the output unless I'm missing something.

data have;
infile datalines dlm = ',' dsd;
input company :$50. year nic;
datalines;
20 Microns Ltd.,2011,8
Ashapura Minechem Ltd.,2011,8
A D F Foods Ltd.,2011,10
Adani Wilmar Ltd.,2011,10
Agro Tech Foods Ltd.,2011,10
Ajanta Soya Ltd.,2011,10
Ambar Protein Inds. Ltd.,2011,10
Andrew Yule & Co. Ltd.,2011,10
Arcuttipore Tea Co. Ltd.,2011,10
Associated Alcohols & Breweries Ltd.,2011,11
Devyani International Ltd.,2011,11
Eco Friendly Food Processing Park Ltd.,2011,11
Elegant Floriculture & Agrotech (India) Ltd.,2011,11
Flex Foods Ltd.,2011,11
Freshtrop Fruits Ltd.,2011,12
Sree Jayalakshmi Autospin Ltd.,2011,12
A I Champdany Inds. Ltd.,2011,13
A K Spintex Ltd.,2011,13
A P M Industries Ltd.,2011,13
Aarvee Denims & Exports Ltd.,2011,13
Aditya Spinners Ltd.,2011,13
Alka India Ltd.,2011,13
Alok Industries Ltd.,2011,13
Alps Industries Ltd.,2011,13
;
run;

proc freq data=have;
     tables nic/noprint out=nic_frequency;
run;
data want;
    merge have nic_frequency(keep=nic count);
    by nic;
run;

It's just named `COUNT` which is an automatic variable from `PROC FREQ`. You can rename it on the `MERGE` statement.

Please mark Paige's response as the correct one.

[srikanthyadav44]

the code is working well.
but, i want to generate the frequency for each year, which I could not show in the sample.
i am sorry for the inconvenience.
please let me know how to rewrite the query to generate the nic frequency for every year separately.
my original data is from the year 2011 to 2021.

thanking you in advance

[srikanthyadav44]

dear all

i have my data in the following frequency 

company_name	year	NIC
20 Microns Ltd.	2011	8
Ashapura Minechem Ltd.	2011	8
A D F Foods Ltd.	2011	10
Adani Wilmar Ltd.	2011	10
Agro Tech Foods Ltd.	2011	10
Ajanta Soya Ltd.	2011	10
Ambar Protein Inds. Ltd.	2011	10
A I Champdany Inds. Ltd.	2011	13
A K Spintex Ltd.	2011	13
A P M Industries Ltd.	2011	13
Aarvee Denims & Exports Ltd.	2011	13
Aditya Spinners Ltd.	2011	13
A D F Foods Ltd.	2012	10
Adani Wilmar Ltd.	2012	10
Agro Tech Foods Ltd.	2012	10
Ajanta Soya Ltd.	2012	10
Andrew Yule & Co. Ltd.	2012	10
Arcuttipore Tea Co. Ltd.	2012	10
Associated Alcohols & Breweries Ltd.	2012	13
Devyani International Ltd.	2012	13
Eco Friendly Food Processing Park Ltd.	2012	13
Acknit Industries Ltd.	2012	14
Apis India Ltd.	2012	14

I  have to generate the frequency of the NIC code for each year from 2011 to 2021 in the dataset.

I need the output in the following  format 

company_name	year	NIC	NIC_Frequency
20 Microns Ltd.	2011	8	2
Ashapura Minechem Ltd.	2011	8	2
A D F Foods Ltd.	2011	10	5
Adani Wilmar Ltd.	2011	10	5
Agro Tech Foods Ltd.	2011	10	5
Ajanta Soya Ltd.	2011	10	5
Ambar Protein Inds. Ltd.	2011	10	5
A I Champdany Inds. Ltd.	2011	13	5
A K Spintex Ltd.	2011	13	5
A P M Industries Ltd.	2011	13	5
Aarvee Denims & Exports Ltd.	2011	13	5
Aditya Spinners Ltd.	2011	13	5
A D F Foods Ltd.	2012	10	6
Adani Wilmar Ltd.	2012	10	6
Agro Tech Foods Ltd.	2012	10	6
Ajanta Soya Ltd.	2012	10	6
Andrew Yule & Co. Ltd.	2012	10	6
Arcuttipore Tea Co. Ltd.	2012	10	6
Associated Alcohols & Breweries Ltd.	2012	13	3
Devyani International Ltd.	2012	13	3
Eco Friendly Food Processing Park Ltd.	2012	13	3
Acknit Industries Ltd.	2012	14	2
Apis India Ltd.	2012	14	2

please suggest me a SAS code. in the earlier query,my illustrated output was not clear.

i have multiple years of data as mentioned in the table. and I need NIC frequency  for each year separately.

thanking you in advance  

[Kurt_Bremser]

Use the auto-remerge of PROC SQL:

proc sql;
create table want as
  select
    company_name,
    year,
    nic,
    count(*) as nic_frequency
  from have
  group by year, nic
;
quit;

Untested, for lack of usable example data; for tested code, provide example data in a working data step with datalines.

[PaigeMiller]

In your other thread, you were shown a correct answer (you must have marked it correct) and also how to change your data into SAS code with DATALINES. Please don't keep asking the same question after it has been answered correctly. Please don't keep posting this data as you have been doing; please post it as SAS code with DATALINES.

[Kurt_Bremser]

I merged this back.

DO NOT DOUBLE_POST!!!

Provide usable example data as shown; if code provided as a suggestion does not work as intended, post the log and describe in detail where the result does not meet your expectations

[Ksharp]

proc sql;
create table want as
select *,count(*) as nic_freqency 
 from have
  group by nic;
quit;