calculate the distinct value in column B over (partition by column A)

alepage — Thu, 19 May 2022 14:56:46 GMT

Hello,

In SAS EG, is there a way to use the statement count(distinct flob) over (partition by police) as N, like the example for test3 (see code below) and if so, how ?

data test;
   input police $ flob $ Other_info $;
   datalines;
1 A X1
1 B X1
1 C X1
1 D X1
1 E X1
2 A TR
2 C TR
;
RUN;

PROC SQL;
create table test1 as
select *,
		count(distinct flob) as N
from test
group by police;
QUIT;

PROC SQL;
create table test2 as
select *,
		count(distinct flob) over (partition by police) as N
from test;
QUIT;

Regards,

Re: calculate the distinct value in column B over (partition by column A)

Tom — Thu, 19 May 2022 15:38:22 GMT

No.

But you don't need it in SAS.

Just use your first example with the normal GROUP BY and SAS will remerge the count onto the appropriate values of A.

topic Re: calculate the distinct value in column B over (partition by column A) in SAS Enterprise Guide

calculate the distinct value in column B over (partition by column A)

Re: calculate the distinct value in column B over (partition by column A)