topic How to identify the first time something happens and after that in a time series dataset in SAS Procedures

How to identify the first time something happens and after that in a time series dataset

Hinh — Wed, 18 Mar 2015 20:56:35 GMT

Dear all,

I have a time series unbalanced dataset. I want to keep all data after the firm is identified as Seasoned Equity Offering (SEO) issuer and the one year before it. Then delete all years before the firm is an SEO issuer. Also, I want to mark all non-SEO issuers in the same data set.

All I can do now is to identify the year in which the firm issues SEO. I am stuck on how to do the rest. A sample of the data and the desired column (SEO) is pasted below. In the file, when PROCEEDS has a number, that means the firm is an SEO. Anything after a positive PROCEEDS should be marked as SEO for the firm. GVKEY is firm id. One year before PROCEEDS is positive should also be marked as SEO. Two or more years before PROCEEDS is positive should be deleted for that particular firm. For firms who never have positive PROCEEDS, they should be marked as non-SEO or SEO=0.

Thanks for any amount of help.

Regards,

Hinh

GVKEY	FYEAR	PROCEEDS	seo
001011	198		delete
001011	1984		delete
001011	1985		delete
001011	1986		delete
001011	1987		delete
001011	1988		delete
001011	1989		delete
001011	1990		delete
001011	1991		delete
001011	1992	11.3	1
001011	1993		1
001011	1994	37.5	1
001012	1979		1
001012	1980		1
001012	1981		1
001012	1982		1
001012	1983		1
001012	1984		1
001012	1985		1
001012	1986		1
001012	1987		1
001012	1988		1
001012	1989		1
001013	1979		0
001013	1980		0
001013	1981		0

Re: How to identify the first time something happens and after that in a time series dataset

gergely_batho — Thu, 19 Mar 2015 14:06:02 GMT

Hi,

Please check your example, since I think it is not consistent with the requirements. (GVKEY=001012 has no PROCEEDS>0, but it has seo=1)

data have;

infile datalines missover;

input GVKEY FYEAR PROCEEDS;

datalines;

001011 1983

001011 1984

001011 1985

001011 1986

001011 1987

001011 1988

001011 1989

001011 1990

001011 1991

001011 1992 11.3

001011 1993

001011 1994 37.5

001012 1979

001012 1980

001012 1981

001012 1982

001012 1983

001012 1984

001012 1985

001012 1986

001012 1987

001012 1988

001012 1989

001013 1979

001013 1980

001013 1981

;

run;

data want;

seoYear=.;seo=0;

do until(last.GVKEY);

set have;

by GVKEY;

if 0<PROCEEDS and seoYear=. then do;

seoYear=FYEAR;

seo=1;

end;

seoYear=seoYear-1;

do until(last.GVKEY);

set have;

by GVKEY;

if (seo=1 and seoYear<=FYEAR) or (seo=0) then do;

output;

end;

run;

Message was edited by: Gergely Bathó

Re: How to identify the first time something happens and after that in a time series dataset

Hinh — Thu, 19 Mar 2015 14:19:31 GMT

Hi Gergly,

Thank you so much. Your code works perfectly. Yes, the error in my sample was my error.

Best,

Hinh

Re: How to identify the first time something happens and after that in a time series dataset

Hinh — Thu, 19 Mar 2015 14:31:48 GMT

Gergely,

Oops, I was too quick to reply. But there is still an error in the code. Would you mind looking again? In the original dataset, GVKEY 001011 has FYEAR =1991. I want to keep it, but your code deletes that observation.

What I need is to keep one year before SEO=1 and onward for the same GVKEY.

Thanks again for your help.

Best,

Hinh

Re: How to identify the first time something happens and after that in a time series dataset

Hinh — Thu, 19 Mar 2015 15:04:56 GMT

Gergely,

I have fixed the code like this and everything seems ok now. After the first IF statement, seoYear=FYEAR-1; instead of seoYear=FYEAR;

Still, I thank you very much for your prompt help.

Regards,

Hinh

The full modified code is as follows:

data want;

seoYear=.;seo=0;

do until(last.GVKEY);

set have;

by GVKEY;

if 0<PROCEEDS and seoYear=. then do;

seoYear=FYEAR-1;

seo=1;

end;

do until(last.GVKEY);

set have;

by GVKEY;

if (seo=1 and seoYear<=FYEAR) or (seo=0) then do;

output;

end;

run;

Re: How to identify the first time something happens and after that in a time series dataset

gergely_batho — Thu, 19 Mar 2015 15:05:27 GMT

I updated the program.

Currently seo=1 for GVKEY 001011and FYEAR =1991. Is that OK?