Programming issue for counting

[joon1]

Hi Madam/Sir,

I would like to count the number of observations when ind_dir=1 in the same company (cikcode) and in the same year (annualreportdate). The following code was tried and printed. However, as shown in the excel sheet containing data, the whole observation per company-year was counted regardless whether ind_dir=1 or ind_dir=0. How can I fix the program to count the number of observations only when ind_dir=1 per company-year pair?

163 proc sql;
163! create table count1 as select*, count(ind_dir=1) as dirindsum from os5 group by
163! cikcode, annualreportdate; run;
NOTE: The query requires remerging summary statistics back with the original data.
NOTE: Table WORK.COUNT1 created, with 2474560 rows and 40 columns.

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
164
NOTE: PROCEDURE SQL used (Total process time):
real time 8:43.94
cpu time 1:09.04

165 proc print data=count1(obs=1000); var cikcode annualreportdate ind_dir dirindsum; run;

NOTE: There were 1000 observations read from the data set WORK.COUNT1.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.32 seconds
cpu time 0.31 seconds

Thank you very much for your help in advance

SIncerely,

Joon1

[hlnquynh · Accepted solution]

Hope this help!

proc sql;
  create table count1 as

    select distinct cikcode, annualreportdate, count(*) as dirindsum

    from os5 where ind_dir=1

    group by cikcode, annualreportdate

  ;

quit;