Picking observations per firm with cetain variable is available

[kimx0961]

The following dataset shows that the firm with gvkey 1011 has four observations that 'emps5' variable is available and the firm with gvkey 1012 has 0 observation that 'emps5' variable is available. Is there any sas program that picks only firm-observations per each firm that  at least three observations of 'emps5' variables are avaiable using 'sql' statement?

Thank you for your help

gvkey 001011	fyear 1988	. emps5
001011	1989	.
001011	1990	.
001011	1991	0.01495
001011	1992	0.01378
001011	1993	0.01339
001011	1994	0.01415
001012	1988	.
001012	1989	.

[stat_sas]

What is the required output? If you want to list all variables for those gvkey having at least 3 values for emps5 then following code may provide you the desired output.

proc sql;

select * from have

group by gvkey

having count(emps5)>=3;

quit;

[Reeza]

Does it matter if the years are consecutive, or is any three years valid?

[kimx0961]

Thanks The following code works well.

proc sql; create table want  select * from have group by gvkey

having count(emps5)>=3; quit;

Is there any condition should be incldued in the code that the three years are consecutive?

Thanks again

[kimx0961]

Hi,

The following code enables me to capture observations with the number of variable 'emps5' is greater than 3.

proc sql; create table want  select * from have group by gvkey

having count(emps5)>=3; quit;

Is there any condition should be incldued in the code that the three years are consecutive?

Thank you for your help

[Reeza]

To clarify, the 3 years should be consecutive?

The method provided does not support finding 3 consecutive years and needs modification if that is the case. 

[kimx0961]

Hi Reeza,

Yes. the 3 years should be consecutive.

Thank you for your help

[kimx0961]

Hi Reeza,

Do you have a suggested code for picking up consecutive observations of three years?

Thank you very much