topic count observation of variable by id in SAS Procedures

count observation of variable by id

sasphd — Tue, 14 Dec 2021 15:51:48 GMT

Hello

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

HAVE

CRSP_FUNDNO TIC

1 BIC

1 *

1 BIC

1 LII

1 THG

2 BIC

2 .

WANT

CRSP_FUNDNO TIC N

1 BIC 2

1 LII 1

1 THG 1

2 BIC 2

Re: count observation of variable by id

PaigeMiller — Tue, 14 Dec 2021 15:55:32 GMT

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.

Re: count observation of variable by id

Astounding — Tue, 14 Dec 2021 15:55:45 GMT

SAS can do that:

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

Re: count observation of variable by id

ballardw — Tue, 14 Dec 2021 16:00:44 GMT

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.

Re: count observation of variable by id

novinosrin — Tue, 14 Dec 2021 16:34:06 GMT

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;