BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasphd
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

 

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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.

--
Paige Miller
Astounding
PROC Star

SAS can do that:

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

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
Tourmaline | Level 20

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;

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 730 views
  • 2 likes
  • 5 in conversation