Dear all,
I have a SAS file with the following columns:
ticker = database company identification code
years = initial public offering (IPO) year
ID_HK = identification code for HK database
IPO_Date = IPO Date
prospectus_announcement_date = IPO prospectus announcement date
Offer amount = IPO offer amount (million units)
Offer price = IPO Offer price
1997 - 2011 = are the number of shares outstanding (million units) for each balance sheet dates
I wish to calculate a variable,
FLOAT
= number of shares publicly offered/ number of shares outstanding after IPO
= offer amount / shares outstanding after IPO (first Balance sheet after IPO)
If I define the "first Balance sheet after IPO" = IPO Year or one year after IPO year depending on the number of shares outstanding shares data availability. If the data on IPO Year is available, then the shares outstanding for the IPO year should be used, otherwise the shares outstanding for the one year after IPO.
How should I produce an output (in columns) as follows:
ticker = database company identification code
years = initial public offering (IPO) year
ID_HK = identification code for HK database
IPO_Date = IPO Date
prospectus_announcement_date = IPO prospectus announcement date
Offer amount = IPO offer amount (million units)
Offer price = IPO Offer price
Float = offer amount / shares outstanding after IPO (first Balance sheet after IPO)
Thank you for any suggestion.
Regards,
mspak
Set up an array where the dimension is based on the columns for the shares outstanding for 1997 - 2011. Then use the years variable as the index to pick the correct value.
data shr_out;
set dat.shr_out;
array shr_out(1997:2011) _1997--_2011;
float = offer_amount / coalesce( shr_out( years ), shr_out( years+1 ));
run;
The float calculation returns some missing values because your sample does not always have the shares outstanding for the IPO year or the year after.
Set up an array where the dimension is based on the columns for the shares outstanding for 1997 - 2011. Then use the years variable as the index to pick the correct value.
data shr_out;
set dat.shr_out;
array shr_out(1997:2011) _1997--_2011;
float = offer_amount / coalesce( shr_out( years ), shr_out( years+1 ));
run;
The float calculation returns some missing values because your sample does not always have the shares outstanding for the IPO year or the year after.
Hi SAS_Bigot,
Thank you for your program. It is the first time I came across the coalesce function in SAS.
Regards,
mspak
,
As above said, the number of shares outstanding might be missing at one year after IPO year .
So I pick up the first non-missing value after IPO year.
BTW. HK is HongKong ? You come from HK ?
libname x v9 'c:\temp\'; data shr_out(drop=i); set x.shr_out; array shr_out{1997:2011} _1997--_2011; do i=lbound(shr_out) to hbound(shr_out); if not missing(shr_out{i}) and i ge years then do; float = offer_amount /shr_out{i}; leave; end; end; run;
Ksharp
Hi Ksharp,
Thank you for your program which help to obtain the first non-missing data and thank you so much for your frequent responses. I found that the missing values are missing for all years (from database). Therefore, I have to fix all the values manually by reading their annual reports.
I am from Malaysia but my counter-party for this project is from Hong Kong. We hand-collected some data by employing research assistants both from Malaysia and HK. As such, HK party created their identification code for easy data consolidation.
Regards,
mspak
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.