topic Re: how to compute the frequency of a variable? in SAS Programming

how to compute the frequency of a variable?

srikanthyadav44 — Fri, 29 Jul 2022 11:36:47 GMT

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

Re: how to compute the frequency of a variable?

PaigeMiller — Fri, 29 Jul 2022 11:40:00 GMT

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;

Re: how to compute the frequency of a variable?

srikanthyadav44 — Fri, 29 Jul 2022 11:40:05 GMT

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

Re: how to compute the frequency of a variable?

PaigeMiller — Fri, 29 Jul 2022 11:40:46 GMT

I provided untested code

Re: how to compute the frequency of a variable?

maguiremq — Fri, 29 Jul 2022 11:58:30 GMT

@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.

Re: how to compute the frequency of a variable?

srikanthyadav44 — Fri, 29 Jul 2022 11:59:18 GMT

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

Re: how to compute the frequency of a variable?

srikanthyadav44 — Fri, 29 Jul 2022 11:59:58 GMT

i am attaching the output

Re: how to compute the frequency of a variable?

PaigeMiller — Fri, 29 Jul 2022 12:02:20 GMT

@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.

Re: how to compute the frequency of a variable?

Ksharp — Fri, 29 Jul 2022 13:18:08 GMT

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

Re: how to compute the frequency of a variable?

srikanthyadav44 — Fri, 29 Jul 2022 17:55:38 GMT

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

variable frequency by year-wise?

srikanthyadav44 — Sat, 30 Jul 2022 09:05:13 GMT

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

Re: variable frequency by year-wise?

Kurt_Bremser — Sat, 30 Jul 2022 09:53:14 GMT

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.

Re: variable frequency by year-wise?

PaigeMiller — Sat, 30 Jul 2022 10:42:22 GMT

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.

Re: variable frequency by year-wise?

Kurt_Bremser — Sat, 30 Jul 2022 12:35:48 GMT

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