## Find an outstanding amount after an event

Solved
Regular Contributor
Posts: 162

# Find an outstanding amount after an event

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

Accepted Solutions
Solution
‎04-25-2012 03:38 PM
Frequent Contributor
Posts: 103

## Re: Find an outstanding amount after an event

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.

All Replies
Solution
‎04-25-2012 03:38 PM
Frequent Contributor
Posts: 103

## Re: Find an outstanding amount after an event

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.

Regular Contributor
Posts: 162

## Re: Find an outstanding amount after an event

Hi SAS_Bigot,

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

Regards,

mspak

,

Super User
Posts: 10,782

## Re: Find an outstanding amount after an event

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

Regular Contributor
Posts: 162

## Re: Find an outstanding amount after an event

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

🔒 This topic is solved and locked.