topic Re: Picking observations per firm with cetain variable is available in SAS Programming

Picking observations per firm with cetain variable is available

kimx0961 — Fri, 08 Jan 2016 01:46:36 GMT

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	.

Re: Picking observations per firm with cetain variable is available

stat_sas — Fri, 08 Jan 2016 01:58:16 GMT

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;

Re: Picking observations per firm with cetain variable is available

Reeza — Fri, 08 Jan 2016 03:42:40 GMT

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

Re: Picking observations per firm with cetain variable is available

kimx0961 — Fri, 08 Jan 2016 11:55:36 GMT

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

Re: Picking observations per firm with cetain variable is available

kimx0961 — Mon, 11 Jan 2016 19:07:53 GMT

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

Re: Picking observations per firm with cetain variable is available

Reeza — Mon, 11 Jan 2016 19:16:39 GMT

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.

Re: Picking observations per firm with cetain variable is available

kimx0961 — Mon, 11 Jan 2016 19:48:14 GMT

Hi Reeza,

Yes. the 3 years should be consecutive.

Thank you for your help

Re: Picking observations per firm with cetain variable is available

kimx0961 — Tue, 12 Jan 2016 02:00:02 GMT

Hi Reeza,

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

Thank you very much