BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Hinh
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
gergely_batho
SAS Employee

I updated the program.

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

View solution in original post

5 REPLIES 5
gergely_batho
SAS Employee

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ó

Hinh
Calcite | Level 5

Hi Gergly,

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

Best,

Hinh

Hinh
Calcite | Level 5

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

gergely_batho
SAS Employee

I updated the program.

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

Hinh
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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