Hello,
I have two different Stata datasets and would like to merge them. One contains a collection of securities prices with securities symbol, opening price, high price, low price, closing price, traded qty, and a datestamp. Another one contains derivative prices with name of the derivative, instrument type (option/future), contract expiry date, contract price, derivative's open, close, high, and low prices, contracts traded, and a datestamp. The derivatives database contains instruments that may or may not have a corresponding underlying contract (for eg., options and futures on government bonds, T-Bill etc.); similarly some securities in the securities database may not be trading derivatives on their security.
I want to merge these databases in the following manner:
1. Use securities database as the primary dataset
2. For a given security and a given datestamp, create new columns for all corresponding derivatives (with all relevant info, like contract price, expiry date, traded prices), symbol and datestamp being the matching keys
3. For a given date, retain derivatives that do not have a corresponding security and concomittantly retain securities that do not have derivatives.
A simple three row table may illustrate the outcome I want, in a better fashion (though I suspect it will reach you misaligned and unreadable at your end):
SECU OPEN HIGH LOW CLOSE QTY DATESTAMP DERIV_SYMB INSTR EXP_DT CTRCT_PRCE OPEN HIGH LOW CLOSE CONTRACTS_QTY
GE 28 29 25 27 10000 01/01/2004 CALL OPT 28/02/2004 35 3.5 3.75 3.15 3.34 100000
GE 28 29 25 27 10000 01/01/2004 PUT OPT 31/01/2004 25 1.2 1.75 1.15 1.22 3000
NODER 45 48 42 43 8000 01/01/2004
90TBILL FUT 30/03/2004 . 96 97 96.55 96.77 100000
If it helps, I provide the securities variable format followed by the derivatives variable format:
symbol str10 %10s
prevclose float %9.0g
open float %9.0g
high float %9.0g
low float %9.0g
close float %9.0g
qty long %12.0g
datestamp float %d
-----------------------
symbol str10 %10s
expiry_date float %d
strike_pr float %9.0g
option_typ str2 %9s
open float %9.0g
high float %9.0g
low float %9.0g
close float %9.0g
contracts long %12.0g
datestamp float %d
I greatly appreciate and thank those that take interest in reading the mail and attempting to answer. I have reached my limits in looking for an answer today, though haven't given up. (No, this is not my homework :) )
Regards,
Nat.
*
* For searches and help try:
* http://www.stata.com/support/faqs/res/findit.html
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
