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 |
I updated the program.
Currently seo=1 for GVKEY 001011and FYEAR =1991. Is that OK?
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ó
Hi Gergly,
Thank you so much. Your code works perfectly. Yes, the error in my sample was my error.
Best,
Hinh
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
I updated the program.
Currently seo=1 for GVKEY 001011and FYEAR =1991. Is that OK?
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.