I am looking to make new variables with more specific information based on what is currently contained in a column named 'AdvisorsToTarget" (see data example below). The column as it is contains up to six pieces of information, such as 'Legal Advisor', 'Financial Advisor', 'Accountant', 'Information Agent', 'Due Diligence Provider', 'Transfer Agent/Registrar' and 'Placement Agent'.
What I want is six new columns each with this specific information, as opposed to all the information being contained together in one variable/column. Each separate piece of information is separated by a semi-colon, and the detail of the information is in brackets. Note that some rows contain more than one Financial Advisor, for example (see row 11 for example).
I have attached an example piece of data below - I hope this makes sense and I appreciate any suggestions on how this may be done, if it is indeed feasible.
Thanks
Paul
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input int Date byte Target str1 Value byte(Buyer Sellers) str184 AdvisorsToTarget 21539 1 "-" 1 1 "GCA Altium Limited (Financial Advisor); CMS Germany (Legal Advisor)" 21539 2 "-" 2 2 "Macfarlanes LLP (Legal Advisor); Spencer House Partners LLP (Financial Advisor)" 21538 3 "-" 3 3 "Skadden, Arps, Slate, Meagher & Flom LLP (Legal Advisor); Travers Smith LLP (Legal Advisor); KPMG Corporate Finance LLP (Financial Advisor)" 21538 4 "-" 4 4 "Rothschild & Co SCA (ENXTPA:ROTH) (Placement Agent)" 21537 5 "-" 5 5 "Addleshaw Goddard LLP (Legal Advisor); DC Advisory (Financial Advisor); Jamieson Corporate Finance LLP (Financial Advisor)" 21536 6 "-" 6 6 "Orrick, Herrington & Sutcliffe LLP (Legal Advisor); Goldman Sachs & Co. LLC (Placement Agent)" 21536 7 "-" 7 7 "Gateley (Holdings) Plc (AIM:GTLY) (Legal Advisor); Clearwater International (Financial Advisor)" 21536 8 "-" 8 8 "Houlihan Lokey, Inc. (NYSE:HLI) (Financial Advisor)" 21530 9 "-" 9 9 "Clearwater International (Placement Agent)" 21530 10 "-" 10 10 "Simmons & Company International Limited (Financial Advisor)" 21528 11 "-" 11 11 "Kirkland & Ellis LLP (Legal Advisor); Innisfree M&A Inc. (Information Agent); Morgan Stanley & Co. LLC (Financial Advisor); UBS Securities LLC (Financial Advisor)" 21528 12 "-" 12 12 "TLT LLP (Legal Advisor); WH Ireland Limited (Financial Advisor); WH Ireland Limited (Placement Agent)" 21525 13 "-" 13 13 "HMT LLP (Financial Advisor)" 21525 14 "-" 14 14 "Park Place Corporate Finance Limited (Financial Advisor)" 21522 15 "-" 15 15 "Burges Salmon (Legal Advisor); Deloitte PCS Limited (Accountant); Blake Morgan LLP (Legal Advisor); Ernst & Young Services (uk) Ltd (Financial Advisor)" 21522 16 "-" 16 16 "Burness Paull & Williamsons (Legal Advisor); BDO International Limited (Financial Advisor)" 21510 17 "-" 17 17 "Addleshaw Goddard LLP (Legal Advisor); Liberty Corporate Finance Associates LLP (Financial Advisor)" 21508 18 "-" 18 18 "Moelis & Company (NYSE:MC) (Financial Advisor)" 21508 19 "-" 19 19 "RG Financial Limited (Financial Advisor)" 21507 20 "-" 20 20 "Turner Parkinson LLP (Legal Advisor); Clearwater CFA (Financial Advisor)" 21500 21 "-" 21 21 "Ernst & Young UK, Investment Banking Arm (Financial Advisor)" 21496 22 "-" 22 22 "Freeths LLP (Legal Advisor)" 21496 23 "-" 23 23 "Charles Russell Speechlys LLP (Legal Advisor)" 21495 24 "-" 24 24 "Liontree Advisors UK LLP (Financial Advisor)" 21494 25 "-" 25 25 "BCMS Corporate Limited (Financial Advisor)" 21490 26 "-" 26 26 "BDO LLP (Accountant)" 21485 27 "-" 27 27 "PALAZZO Investment Bankers (Placement Agent)" 21483 28 "-" 28 28 "Ward Hadaway LLP (Legal Advisor); HURST Corporate Finance (Financial Advisor)" 21479 29 "-" 29 29 "BDO LLP, Financial Advisory Arm (Placement Agent)" 21474 30 "-" 30 30 "Shoosmiths LLP (Legal Advisor); Houlihan Lokey (Europe) Limited (Financial Advisor)" 21473 31 "-" 31 31 "Houlihan Lokey (Europe) Limited (Financial Advisor)" 21472 32 "-" 32 32 "Lazard & Co., Limited (Financial Advisor); Wyvern Partners Limited (Financial Advisor); Morrison & Foerster (UK) LLP (Legal Advisor)" 21472 33 "-" 33 33 "GCA Altium Limited (Financial Advisor); Addleshaw Goddard LLP (Legal Advisor); PricewaterhouseCoopers LLP (Due Diligence Provider); CIL Management Consultants (Due Diligence Provider)" 21469 34 "-" 34 34 "DLA Piper UK LLP (Legal Advisor)" 21469 35 "-" 35 35 "Jamieson Corporate Finance LLP (Financial Advisor); Osborne Clarke LLP (Legal Advisor)" 21466 36 "-" 36 36 "Allen & Overy LLP (Legal Advisor); DLA Piper UK LLP (Legal Advisor)" 21466 37 "-" 37 37 "JMW Solicitors Ltd. (Legal Advisor); Spinnaker Corporate Finance (Financial Advisor)" 21466 38 "-" 38 38 "Walker Morris LLP (Legal Advisor); DSW Bridge Houghton Forensic (Financial Advisor)" 21465 39 "-" 39 39 "Birketts LLP (Legal Advisor)" 21458 40 "-" 40 40 "Silverpeak LLP (Financial Advisor)" 21458 41 "-" 41 41 "Spayne Lindsay & Co. LLP (Financial Advisor)" 21447 42 "-" 42 42 "Gateley (Holdings) Plc (AIM:GTLY) (Legal Advisor); Alantra USA (Placement Agent)" 21447 43 "-" 43 43 "KPMG Corporate Finance LLP (Financial Advisor); DLA Piper UK LLP (Legal Advisor)" 21446 44 "-" 44 44 "Osborne Clarke LLP (Legal Advisor)" 21445 45 "-" 45 45 "EMW Law LLP (Legal Advisor); Nexus Corporate Finance LLP (Financial Advisor)" 21445 46 "-" 46 46 "Pinsent Masons (Legal Advisor); Grant Thornton Corporate Finance (Financial Advisor)" 21438 47 "-" 47 47 "Addleshaw Goddard LLP (Legal Advisor); Shore Capital and Corporate Limited (Financial Advisor)" 21433 48 "-" 48 48 "BDO LLP (Accountant); Houlihan Lokey Capital, Inc. (Financial Advisor)" 21431 49 "-" 49 49 "N M Rothschild & Sons Limited (Financial Advisor); DLA Piper UK LLP (Legal Advisor)" 21430 50 "-" 50 50 "Macfarlanes LLP (Legal Advisor); Jamieson Corporate Finance LLP (Financial Advisor)" 21428 51 "-" 51 51 "KPMG LLP (United Kingdom), Investment Banking Arm (Financial Advisor)" 21427 52 "-" 52 52 "BDO Corporate Finance LLP (Financial Advisor)" 21424 53 "-" 53 53 "Higgs & Sons (Legal Advisor); PricewaterhouseCoopers LLP (Accountant); Spencer Gardner Dickins (Accountant)" 21420 54 "-" 54 54 "BDO LLP (Accountant); The Jordan, Edmiston Group, Inc. (Financial Advisor); CMS Cameron McKenna Nabarro Olswang LLP (Legal Advisor)" 21419 55 "-" 55 55 "Gowling WLG (UK) LLP (Legal Advisor); Winchester Technology Advisors Limited (Placement Agent)" 21418 56 "-" 56 56 "PwC Corporate Finance LLP (Financial Advisor)" 21412 57 "-" 57 57 "Pinsent Masons (Legal Advisor); Numis Securities Limited (Financial Advisor); Nplus1 Singer Advisory LLP (Financial Advisor)" 21410 58 "-" 58 58 "Kuit Steinart Levy LLP (Legal Advisor)" 21409 59 "-" 59 59 "Slaughter and May (Legal Advisor); Deutsche Bank AG, London Branch (Financial Advisor); Equiniti Limited (Transfer Agent/Registrar)" 21408 60 "-" 60 60 "CMS Cameron McKenna (Legal Advisor)" 21406 61 "-" 61 61 "Simpson Thacher & Bartlett LLP (Legal Advisor)" 21403 62 "-" 62 62 "BDO LLP (Accountant); N M Rothschild & Sons Limited (Financial Advisor); Travers Smith LLP (Legal Advisor); CMS Germany (Legal Advisor); Panmure Gordon (UK) Limited (Financial Advisor)" 21398 63 "-" 63 63 "Jenson Solutions Limited (Accountant)" 21398 64 "-" 64 64 "Cavendish Corporate Finance LLP (Financial Advisor); Armstrong Watson Ltd (Accountant); PricewaterhouseCoopers LLP (Accountant)" 21398 65 "-" 65 65 "Allen & Overy LLP (Legal Advisor); Reynolds Porter Chamberlain (Legal Advisor); KPMG LLP (United Kingdom) (Accountant); Houlihan Lokey (Europe) Limited (Financial Advisor)" 21397 66 "-" 66 66 "Browne Jacobson LLP (Legal Advisor); BDO Corporate Finance LLP (Financial Advisor)" 21396 67 "-" 67 67 "PricewaterhouseCoopers International Limited (Accountant); Park Place Corporate Finance Limited (Financial Advisor); Squire Patton Boggs (UK) LLP (Legal Advisor)" 21396 68 "-" 68 68 "Osborne Clarke LLP (Legal Advisor)" 21395 69 "-" 69 69 "TLT LLP (Legal Advisor)" 21391 70 "-" 70 70 "N M Rothschild & Sons Limited (Financial Advisor)" 21389 71 "-" 71 71 "Ernst & Young Global Limited, Corporate Finance (Financial Advisor)" 21389 72 "-" 72 72 "CMS Cameron McKenna Nabarro Olswang LLP (Legal Advisor)" 21388 73 "-" 73 73 "Arrowpoint Advisory LLP (Financial Advisor); James Cowper LLP (Accountant); Pitmans LLP (Legal Advisor)" end format %td Date
0 Response to Make new variables based on the information in brackets in current column
Post a Comment