Count Distinct By groups with filter

[Daniel1027]

Hello everyone,

I want to count security ID (secid) per year that has permno.  I urgently needed to count them. please help me guys.

Best wishes.

my sample dataset shown as:

secid   effect_date      cusip      ticker  issuer  permno 

6405     1/1/1996     86590610  sugr  calcomp    11727

6405      7/24/1996 12870110   clcp   calcomp     11727

6406      1/1/1996   16939610   cinc   china.inc     11729

6410       1/1/1996    55267210  ?       mcgec.inc    (none)

6409       2/11/2000   72017210  peide  piedmont    75561

6420        3/7/1997    89620810  ?         trimac         (none)

[Daniel1027]

Hello everyone,

I want to count security ID (secid) per year that has permno.  I urgently needed to count them. please help me guys.

Best wishes.

my sample dataset shown as:

secid   effect_date      cusip      ticker  issuer  permno 

6405     1/1/1996     86590610  sugr  calcomp    11727

6405      7/24/1996 12870110   clcp   calcomp     11727

6406      1/1/1996   16939610   cinc   china.inc     11729

6410       1/1/1996    55267210  ?       mcgec.inc    (none)

6409       2/11/2000   72017210  peide  piedmont    75561

6420        3/7/1997    89620810  ?         trimac         (none)

[Daniel1027]

I am doing my undergraduate interdisciplinary project individually. I thought, i need to work with sas software, because it will be my advantage in the future. basically, this is for my project.

[ballardw]

Does your PermNo variable actually contain text "(none)" or is it a SAS MISSING value? If the later

Proc freq data = have;

   where not missing(permno);

   tables secid;

run;

will give the count of each secid and the cumulative at the end of the table the overal count.

If the value of PermNo actually contains text of (none)

then

Proc freq data = have;

   where permno ne '(none)';

   tables secid;

run;

will do the same thing.

[Daniel1027]

sorry for inconvenience. I wrote down "none" in some case, there is just "."(dot).

[Daniel1027]

I do not mean about frequency, i mean, counting total number of secid per year that has permno.

[stat_sas · Accepted solution]

proc sql;
select distinct year(effect_date) as year, count(distinct secid) as secid from have
where permno ne .
group by year(effect_date);
quit;