Matching Two sets of companies by SIC code and tender offer year

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

Matching Two sets of companies by SIC code and tender offer year

Hi.

I am trying to match two sets of companies by a common SIC code and common tender offer year. Group 1: companies that have adopted a defense mechanism within 12 months of the tender offer. Group 2: companies that did not have a defense mechanism in place. Here is the example of my data. For companies that did not have a defense mechanism adopted, the adoptiondate, adoptionyear and tender_minus_adoption fields are blank. 

What would be the best way to find the best matches for the Adopting Companies Set from the Non-Adopters Set based on the common TargetPrimarySicCode and Tenderoffer_year. I hope it makes sense. Thank you for your help.

TenderOfferAnnouncedtenderoffer_yearTargetNameTargetPrimarySICCodeTargetIndustrySectorAcquirorPrimarySICCodeTargetCUSIPAdoptionDateAdoptionYearTender_minus_AdoptionDate_months
27/03/20002000Hartford Life(ITT Hartford)6311Insurance679925500M03/27/20012001-12
03/11/20002000Getty Petroleum Marketing Inc5172Wholesale Trade-Nondurable Goods131123129210/30/20012001-11.86849315
10/02/19981998Computer Sciences Corp7373Business Services73726444602/05/19991999-11.83561644
07/09/19871987Canrad Inc3648Electronic and Electrical Equipment36633090008/29/19881988-11.7369863
17/05/19901990DST Systems Inc7372Prepackaged Software401110114305/09/19911991-11.7369863
18/12/20002000Chase Industries Inc3351Metal and Metal Products67993852112/10/20012001-11.7369863

Accepted Solutions
Solution
‎03-22-2013 07:23 PM
Super Contributor
Posts: 644

Re: Matching Two sets of companies by SIC code and tender offer year

You have not considered the other questions I asked so I am going to assume that

  • if there are multiple matches you want all combinations (cartesian product)
  • if there are no matches you are not interested in these records and they will be dropped (inner join)

Also, there will be duplicate columns for tender_offer_date, target_name, target_cusip


Alert: untested code

Proc SQL ;

     Create table want as

          select  adp.tender_offer_year

               ,     adp.target_sic_code

               ,     adp.tender_offer_date  as adop_tender_offer_date

               ,     adp.target_name          as adop_target_name

               ,     adp.target_cusip          as adop_target_cusip

               ,     adp.adoption_date

               ,     oth.tender_offer_date   as othr_tender_offer_date

               ,     oth.target_name           as othr_target_name

               ,     oth.target_cusip           as othr_target_cusip

          From    have (where = (adoptionyear IS NOT NULL))   adp

               ,     have (where = (adoptionyear IS NULL))           oth

          Where  adp.tender_offer_year     =     oth.tender_offer_year

               and adp.target_sic_code       =     oth.target_sic_code

          Order

               by   1, 2, 3, oth.tender_offer_date

                    ;

Quit ;



Richard

View solution in original post


All Replies
Super Contributor
Posts: 644

Re: Matching Two sets of companies by SIC code and tender offer year

I think we need more information

What columns are you wanting to keep in your result set?

How do you want to treat many to many matches (cartesian products) or non-matches?

Are there any other match criteria unspecified (eg closest in date)?

Do you want exact matches or close matches?

Richard

Contributor
Posts: 20

Re: Matching Two sets of companies by SIC code and tender offer year

Posted in reply to RichardinOz

Hi.

Preferably I want to keep the following columns: tender_offer_date, tender_offer_year, target_name, target_sic_code, target_cusip, adoption_date.

I need the two groups to be matched by two criteria: Target_sic_code and tender_offer_year. Meaning, the matching companies have to have the same sic code and the same tender offer year, but one of the companies will be an adopting company and the other one not. I would also like to have exact matched: exact sic code and tender offer years.

Hope it helps.

Thank you for your time,

A.

Solution
‎03-22-2013 07:23 PM
Super Contributor
Posts: 644

Re: Matching Two sets of companies by SIC code and tender offer year

You have not considered the other questions I asked so I am going to assume that

  • if there are multiple matches you want all combinations (cartesian product)
  • if there are no matches you are not interested in these records and they will be dropped (inner join)

Also, there will be duplicate columns for tender_offer_date, target_name, target_cusip


Alert: untested code

Proc SQL ;

     Create table want as

          select  adp.tender_offer_year

               ,     adp.target_sic_code

               ,     adp.tender_offer_date  as adop_tender_offer_date

               ,     adp.target_name          as adop_target_name

               ,     adp.target_cusip          as adop_target_cusip

               ,     adp.adoption_date

               ,     oth.tender_offer_date   as othr_tender_offer_date

               ,     oth.target_name           as othr_target_name

               ,     oth.target_cusip           as othr_target_cusip

          From    have (where = (adoptionyear IS NOT NULL))   adp

               ,     have (where = (adoptionyear IS NULL))           oth

          Where  adp.tender_offer_year     =     oth.tender_offer_year

               and adp.target_sic_code       =     oth.target_sic_code

          Order

               by   1, 2, 3, oth.tender_offer_date

                    ;

Quit ;



Richard

Contributor
Posts: 20

Re: Matching Two sets of companies by SIC code and tender offer year

Posted in reply to RichardinOz

It worked! Thank you very much for your help!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 365 views
  • 0 likes
  • 2 in conversation