topic Re: Find an outstanding amount after an event in SAS Procedures

Find an outstanding amount after an event

mspak — Wed, 25 Apr 2012 13:15:02 GMT

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

Re: Find an outstanding amount after an event

FloydNevseta — Wed, 25 Apr 2012 19:38:45 GMT

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.

Re: Find an outstanding amount after an event

mspak — Thu, 26 Apr 2012 03:29:19 GMT

Hi SAS_Bigot,

Thank you for your program. It is the first time I came across the coalesce function in SAS.

Regards,

mspak

Re: Find an outstanding amount after an event

Ksharp — Thu, 26 Apr 2012 04:14:18 GMT

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

Re: Find an outstanding amount after an event

mspak — Thu, 26 Apr 2012 05:12:31 GMT

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