Summarise and calculate the items specifically in the dataset using proc sql

meichan1128 — Tue, 01 Dec 2020 16:51:21 GMT

My dataset

data mydata;
   input Category $ Item $;
   datalines;
	A 1
	A 1 
	A 2
	B 3
	B 1
;

My attempt

proc sql;
	create table mytable as 
		select *, count(Category) as Total_No_in_Category, count(Category)-count(item, "3") as No_of_not_3_in_the_same_category from mydata
		group by Category;
run;

Result

Category	No_of_not_3_in_the_same_ category	Total_No_in_Category
A	3	3
A	3	3
A	3	3
B	2	2
B	2	1

My expected result

Category	No_of_not_1_in_the_same_ category	Total_No_in_Category
A	2	3
B	1	2

I wonder how to achieve the expected result using only proc SQL. Thank you so much.

Re: Summarise and calculate the items specifically in the dataset using proc sql

novinosrin — Tue, 01 Dec 2020 16:56:32 GMT

data mydata;
   input Category $ Item $;
   datalines;
	A 1
	A 1 
	A 2
	B 3
	B 1
;


 proc sql;
	create table want as 
		select category, sum(item ne '1') as No_of_not_1_in_the_same_category, count(Category) as Total_No_in_Category

		from mydata
		group by Category;
quit;

topic Summarise and calculate the items specifically in the dataset using proc sql in SAS Programming

Summarise and calculate the items specifically in the dataset using proc sql

Re: Summarise and calculate the items specifically in the dataset using proc sql