topic Re: Proc SQL select count with correlated subquerie in SAS Programming

Proc SQL select count with correlated subquerie

sascode — Thu, 18 Nov 2021 03:49:12 GMT

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

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

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 — Thu, 18 Nov 2021 07:12:08 GMT

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

Ksharp — Thu, 18 Nov 2021 12:50:20 GMT

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

sascode — Thu, 18 Nov 2021 12:50:21 GMT

Thank you,

Unfortunately it does not work.

sascode — Thu, 18 Nov 2021 12:55:55 GMT

My apologise ,

Is working correctly.

Thank you.

Kurt_Bremser — Thu, 18 Nov 2021 13:43:55 GMT

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