count observation of variable by id

[sasphd]

Hello 

I want to calculate the number of observations of TIC by CRSP_FUNDNO

HAVE

CRSP_FUNDNO      TIC

1                               BIC

1                                 *

1                                 *

1                               BIC

1                                LII

1                               THG

2                                 BIC

2                                   BIC

2                                    .

2                                     .

WANT

CRSP_FUNDNO      TIC        N

1                                BIC        2

1                                 LII          1

1                                 THG        1

2                                BIC         2

[PaigeMiller]

Hello, @sasphd 

You have been in the forum long enough to know we want data as SAS data step code (instructions). From now on, please provide the data in the requested format.

You can use PROC FREQ to get the answer you are looking for.

[Astounding]

SAS can do that:

proc freq data=have;
   tables CRSP_FUNDNO * TIC / list;
   where TIC ne '*';
run;

[ballardw · Accepted solution]

Do you want a report, people read, or a data set?

Several procedures do reports for this:

Proc freq data=have;
   tables crsp_fundno* tic /list nopercent nocum;
run;

proc tabulate data=have;
   class crsp_fundno tic;
   table crsp_fundno * tic ,
            n
   ;
run;

proc report data=have;
    columns crsp_fundno tic n;
    define crsp_fundno /group;
    define tic /group;
run;

Proc summary will make a data set like that.

proc summary data=have nway;
   class csrp_fundno tic;
   output out=want (drop=_type_);
run;

The automatic variable _freq_ will have the count.

[novinosrin]

Hi @sasphd  why not a simple SQL after all?

data have;
 input CRSP_FUNDNO      TIC $;
 cards;
1                               BIC

1                                 .

1                                 .

1                               BIC

1                                LII

1                               THG

2                                 BIC

2                                   BIC

2                                    .

2                                     .
;

proc sql;
  select CRSP_FUNDNO , TIC , count(*) as c
  from have
  where not missing(tic)
  group by CRSP_FUNDNO  ,    TIC ;
quit;