topic COUNT variable in SAS Procedures

COUNT variable

namrata — Wed, 04 Jul 2012 04:12:09 GMT

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.

Re: COUNT variable

Ksharp — Wed, 04 Jul 2012 04:40:08 GMT

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

Re: COUNT variable

Haikuo — Wed, 04 Jul 2012 12:56:24 GMT

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

Re: COUNT variable

namrata — Wed, 04 Jul 2012 23:06:33 GMT

Thanks a lot!The suggestions worked.