Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

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

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

GVKEYFYEARPROCEEDS

seo

001011198delete
0010111984delete
0010111985delete
0010111986delete
0010111987delete
0010111988delete
0010111989delete
0010111990delete
0010111991delete
001011199211.31
00101119931
001011199437.51
00101219791
00101219801
00101219811
00101219821
00101219831
00101219841
00101219851
00101219861
00101219871
00101219881
00101219891
00101319790
00101319800
00101319810

Accepted Solutions
Solution
‎03-19-2015 11:05 AM
SAS Employee
Posts: 340

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

I updated the program.

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

View solution in original post


All Replies
SAS Employee
Posts: 340

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

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;

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;

end;

run;

Message was edited by: Gergely Bathó

Occasional Contributor
Posts: 9

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

Hi Gergly,

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

Best,

Hinh

Occasional Contributor
Posts: 9

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

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

Solution
‎03-19-2015 11:05 AM
SAS Employee
Posts: 340

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

I updated the program.

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

Occasional Contributor
Posts: 9

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

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;

end;

do until(last.GVKEY);

  set have;

  by GVKEY;

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

  output;

  end;

end;

run;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 248 views
  • 3 likes
  • 2 in conversation