counting the frequency of the response type

[srikanthyadav44]

dear all 

i have to count the frequency of the response type for each company in the data set and to prepare a separate table with company name and frequency of response type. 

the sample of the data and required output is given below

The sample data

Company Name	response type
3I Infotech Ltd.	Independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3M India Ltd.	Independent
3M India Ltd.	Independent
3M India Ltd.	Independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent

required output table

Company Name	total Frequency	Non-independent	Independent
3I Infotech Ltd.	7	5	2
3M India Ltd.	10	7	3
3P Land Holdings Ltd.	7	4	3

Please suggest me SAS code perform the above discussed function

thanks in advance. 

[ed_sas_member]

Hi @srikanthyadav44 

Here is an approach to achieve this:

data have;
	infile datalines dlm="09"x;
	input Company_Name:$50.	response_type:$50.;
	datalines;
3I Infotech Ltd.	Independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3M India Ltd.	Independent
3M India Ltd.	Independent
3M India Ltd.	Independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
;
run;

/* Frequencies per Company_Name and response_type */
proc freq data=have noprint;
	table response_type / out=have_stat (drop=percent);
	by Company_Name;
run;

proc transpose data=have_stat out=have_stat_tr (drop=_:);
	var count;
	id response_type;
	by Company_Name;
run;

/* Frequencies per Company_Name */

proc freq data=have noprint;
	table Company_Name / out=have_stat_total (drop=percent rename=(count = total_Frequency));
run;

/* Final table */

data want;
	merge have_stat_total have_stat_tr;
	by Company_Name;
run;

Should you need only a report, you can use a proc tabulate for example:

proc tabulate data=have;
	class Company_Name response_type;
	table Company_Name, response_type all*n;
run;

All the best,

[Ksharp · Accepted solution]

data have;
	infile datalines dlm="09"x;
	input Company_Name:$50.	response_type:$50.;
	datalines;
3I Infotech Ltd.	Independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3I Infotech Ltd.	Non-independent
3M India Ltd.	Independent
3M India Ltd.	Independent
3M India Ltd.	Independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3M India Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Independent
3P Land Holdings Ltd.	Non-independent
3P Land Holdings Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Non-independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
52 Weeks Entertainment Ltd.	Independent
;
run;

proc sql;
select Company_Name,count(response_type) as total,
       sum(response_type='Non-independent') as Nonindependent,
       sum(response_type='Independent') as Independent
   from have
    group by Company_Name;
quit;

[srikanthyadav44]

thank you Mr.  Ksharp

the SAS code suggested by you is working excellently. 

can you please provide me the code which can save the output table into a separate excel file

thanks in advance.  

[Ksharp]

1) Create a table like :

proc sql;

create table want as

........

and using PROC EXPORT to export output into an EXCEL file.

proc export data=want outfile='c:\temp\want.xlsx' dbms=excel replace;

run;

2) Try ODS EXCEL like:

ods excel file='c:\temp\want.xlsx' ;

proc sql;

select ..........

quit;

ods excel close;

[srikanthyadav44]

Dear Mr. Ksharp

I tried the two alternatives as suggested by you, but they are not working. 

output table is displayed in SAS,but not exported as excel file. 

please suggest me a complete SAS code include the function to create the table. because i may be wrong in arranging the code properly. 

thanks in advance 

[srikanthyadav44]

dear Mr. Ksharp

i also want to extent this function to get the frequency count for multiple years of data. 

the new data set format is as follows. the data set consists of the observations from 2001 to 2018. Almost all the companies will be repeated in each year. 

Company Name	year	response_type
8K Miles Software Services Ltd.	2001	Non-independent
8K Miles Software Services Ltd.	2001	Non-independent
8K Miles Software Services Ltd.	2001	Non-independent
8K Miles Software Services Ltd.	2001	Non-independent
8K Miles Software Services Ltd.	2001	Non-independent
Aarvi Encon Ltd.	2001	Non-independent
Aarvi Encon Ltd.	2001	Non-independent
Akar Auto Inds. Ltd.	2001	Non-independent
Akar Auto Inds. Ltd.	2001	Non-independent
Akar Auto Inds. Ltd.	2001	Non-independent
Akar Auto Inds. Ltd.	2001	Non-independent
Akar Auto Inds. Ltd.	2001	Non-independent
Alang Industrial Gases Ltd.	2001	Non-independent
Allied Digital Services Ltd.	2001	Non-independent
Amarjothi Spinning Mills Ltd.	2001	Non-independent
Amarjothi Spinning Mills Ltd.	2001	Non-independent
Ansal Buildwell Ltd.	2001	Non-independent
Ansal Buildwell Ltd.	2001	Non-independent
Ansal Buildwell Ltd.	2001	Non-independent
Ansal Buildwell Ltd.	2001	Non-independent
Ansal Buildwell Ltd.	2001	Non-independent
Apar Industries Ltd.	2001	Non-independent
Apar Industries Ltd.	2001	Non-independent
Apar Industries Ltd.	2001	Non-independent
Apar Industries Ltd.	2001	Non-independent
Apar Industries Ltd.	2001	Non-independent
Apar Industries Ltd.	2001	Non-independent
Apar Industries Ltd.	2001	Non-independent
Apex Buildsys Ltd.	2001	Non-independent
Apollo Hospitals Enterprise Ltd.	2001	Non-independent
Apollo Hospitals Enterprise Ltd.	2001	Non-independent
Apollo Hospitals Enterprise Ltd.	2001	Independent
Apollo Hospitals Enterprise Ltd.	2001	Independent
Apollo Hospitals Enterprise Ltd.	2001	Independent
Apollo Hospitals Enterprise Ltd.	2001	Independent
Apollo Hospitals Enterprise Ltd.	2001	Non-independent
Apollo Hospitals Enterprise Ltd.	2001	Independent

the required format the of the table is 

Company Name	year	total Frequency	Non-independent	Independent
3I Infotech Ltd.	2001	7	5	2
3M India Ltd.	2001	10	7	3
3P Land Holdings Ltd.	2001	7	4	3
.	.	.	.	.
.	.	.	.	.
.	.	.	.	.
.		.	.	.
.	.	.	.	.

please let me know how the modify the SAS code to make it suitable for the new format of the dataset. 

and also, how to export the output table. 

thanks in advance. 

[Ksharp]

proc sql;
create table want as
select Company_Name,year,count(response_type) as total,
       sum(response_type='Non-independent') as Nonindependent,
       sum(response_type='Independent') as Independent
   from have
    group by Company_Name,year;
quit;
proc export data=want outfile="/courses/d8fb3215ba27fe300/want.xlsx" dbms=xlsx replace;
run;

[srikanthyadav44]

thanks a lot. it is working

[srikanthyadav44]

thanks. it is working