Proc SQL select count with correlated subquerie

[sascode]

Hello,

I have a dataset as below. I want to count as total,count cat where it is A and group by year.

Obs year cat12345678910

2018	A
2019	B
2021	C
2019	A
2021	A
2021	B
2018	C
2019	C
2021	A
2021	A

 I am using below code but it is not working.

proc sql;
select year,
count(*) as total,
(select count(cat) from try where cat = 'A') as n_a

from try
group by year;
quit;

Above code produced this:

 year total n_a

2018	2	5
2019	3	5
2021	5	5

where total is correctly grouped but n_a is not.

I wanted this code to make to work because there are other ways to solve this issue ,for instance i can use count with " case when".

Thank you.

[ChrisNZ]

select count(*) as total
,sum(cat = 'A') as n_a

[sascode · Accepted solution]

Thank you,

Unfortunately it does not work.

[sascode]

My apologise ,

Is working correctly.

Thank you.

[Kurt_Bremser]

You should mark the code that worked for you as the solution, not your answer to it.

[Ksharp]

proc sql;
select year,count(*) as total,sum(cat='A') as n_a
from have
group by year;
quit;