topic Re: desperately needed in SAS Programming

Count Distinct By groups with filter

Daniel1027 — Tue, 26 Apr 2016 16:31:52 GMT

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)

desperately needed

Daniel1027 — Tue, 26 Apr 2016 15:39:32 GMT

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)

Re: desperately needed

Daniel1027 — Tue, 26 Apr 2016 15:49:59 GMT

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.

Re: desperately needed

ballardw — Tue, 26 Apr 2016 15:50:29 GMT

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.

Re: desperately needed

Daniel1027 — Tue, 26 Apr 2016 15:53:43 GMT

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

Re: desperately needed

Daniel1027 — Tue, 26 Apr 2016 15:57:12 GMT

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

Re: desperately needed

stat_sas — Tue, 26 Apr 2016 16:03:43 GMT

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;