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

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

1 ACCEPTED SOLUTION

Accepted Solutions
FloydNevseta
Pyrite | Level 9

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.

View solution in original post

4 REPLIES 4
FloydNevseta
Pyrite | Level 9

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.

mspak
Quartz | Level 8

Hi SAS_Bigot,

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

Regards,

mspak

,

Ksharp
Super User

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

mspak
Quartz | Level 8

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 

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
  • 4 replies
  • 813 views
  • 4 likes
  • 3 in conversation