Help using Base SAS procedures

COUNT variable

Reply
Contributor
Posts: 52

COUNT variable

Hi all

I need to create a count variable that contains the total number of firm years for each firm in a panel data.

year   permno  firm

1          a          x

2          a          x

3          a          x

4          a          x

1          b          y         

2          b          y    

3          b          y

Now I need to create a count variable that gives me:

year   permno  firm      count

1          a          x          4

2          a          x          4

3          a          x          4

4          a          x          4

1          b          y          3

2          b          y          3

3          b          y          3

Thus, I can delete all firms that have less than say 3 years of observations.

The code till now is:

data want;

set have;

by permno;

if first.permno then count =1;

else count+1;

run;

this gives me the total on the last count row .I do not know how to create directly a variable that gives me the total on each row for each firm.

Super User
Posts: 9,676

Re: COUNT variable

Easy.

data have;
input year   permno $ firm $;
datalines;
1          a          x
2          a          x
3          a          x
4          a          x
1          b          y         
2          b          y    
3          b          y
;
run;
proc sql;
create table want as
 select *,count(*) as count
  from have
   group by firm;
quit;

Ksharp

Respected Advisor
Posts: 3,124

Re: COUNT variable

Adding one statement to Ksharp's code will give you one stone, two birds.

proc sql;

create table want as

select *,count(*) as count

  from have

   group by firm  having calculated count gt 3

;

quit;

Regards,

Haikuo

Contributor
Posts: 52

Re: COUNT variable

Thanks a lot!The suggestions worked.

Ask a Question
Discussion stats
  • 3 replies
  • 4554 views
  • 0 likes
  • 3 in conversation