DATA Step, Macro, Functions and more

Count Distinct By groups with filter

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

Count Distinct By groups with filter

[ Edited ]

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)

 

 

 

 


Accepted Solutions
Solution
‎04-26-2016 12:53 PM
Trusted Advisor
Posts: 1,204

Re: desperately needed

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;

View solution in original post


All Replies
Contributor
Posts: 21

desperately needed

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)

Contributor
Posts: 21

Re: desperately needed

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.
Super User
Posts: 10,460

Re: desperately needed

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.

Contributor
Posts: 21

Re: desperately needed

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

Re: desperately needed

I do not mean about frequency, i mean, counting total number of secid per year that has permno.
Solution
‎04-26-2016 12:53 PM
Trusted Advisor
Posts: 1,204

Re: desperately needed

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 338 views
  • 3 likes
  • 3 in conversation