About cmoore

cmoore · ‎01-15-2019

@ChrisNZ yes total agree with your logic and those are the steps that I used to write my matching programme. The code above is just a snippet of the code that I used at the very end of the process where I needed to use fuzzy logic on poor data that was left after data cleansing and removing exact matches.

cmoore · ‎01-14-2019

Hi, The kind of coding you could you use to fuzzy match on name can be seen below. You just need to amend your variables to fit your data tables; /*Non-Exact Matches - Use the Algorithm to match variables - on Company Names*/ proc sql; create table NonExact_matches_Comp as select s1.company_name as Original_Company_Name, s1.cleaned_company_name as Cleaned_Company_name, s2.cleaned_company_name as connect_company_name, s2.cleaned_group_name as connect_group_name, s2.client_group_number, compged(s1.cleaned_company_name,s2.cleaned_company_name,999,'LN')as compged, spedis(s1.cleaned_company_name,s2.cleaned_company_name)as spedis from s1 inner join s2 on (compged(s1.cleaned_company_name,s2.cleaned_company_name,999,'LN') < 200 and spedis(s1.cleaned_company_name,s2.cleaned_company_name) < 50) group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17 having calculated compged = min(compged(s1.cleaned_company_name,s2.cleaned_company_name,999,'LN')) ; quit;

cmoore · ‎01-10-2019

Are all the names the same spellings across the tables? Here is some rough code that cleanses the name on both tables then joins on it. If the spellings werent the same you would need to use fuzzy matching techniques. data s1; set s1; format Cleaned_exec_fullname $255.; /*Name*/ Cleaned_exec_fullname = scan(exec_fullname, 1,'()'); Cleaned_exec_fullname = upcase(Cleaned_exec_fullname); Cleaned_exec_fullname = compress(Cleaned_exec_fullname,",,.,/,*,',",""); Cleaned_exec_fullname = compress(Cleaned_exec_fullname,""""); Cleaned_exec_fullname = strip(Cleaned_exec_fullname); Cleaned_exec_fullname = compbl(Cleaned_exec_fullname); run; data s2; set s2; format Cleaned_directorname $255.; /* Name*/ Cleaned_directorname = scan(directorname, 1,'()'); Cleaned_directorname = upcase(Cleaned_directorname); Cleaned_directorname = compress(Cleaned_directorname,",,.,/,*,',",""); Cleaned_directorname = compress(Cleaned_directorname,""""); Cleaned_directorname = strip(Cleaned_directorname); Cleaned_directorname = compbl(Cleaned_directorname); run; proc sql noprint; **Selecting exact matches; create table Exact_matches_Comp as select s1.exec_fullname as Original_Exec_Name, s1.Cleaned_exec_fullname as Cleaned_Exec_Name, s2.directorname as Original_Director_Name, s2.Cleaned_directorname as Cleaned_Director_Name, s2.degree from s1 inner join s2 on s1.Cleaned_exec_fullname = s2.Cleaned_directorname where s1.Cleaned_exec_fullname is not null; quit; proc sort data=Exact_matches_Comp nodupkey; by Cleaned_Director_Name degree; run;

cmoore · ‎01-10-2019

Yes I'll help you know problem. Please could you provide me with 10 examples of data that you are trying to match. Thanks Chris

cmoore · ‎08-02-2018

Hi, I have received an output of data from a survey that was recently sent out. Please see a data example below; I basically want to reorder the Data by rank order in columns depending what number is in each cell. Any ideas of the best way to do this? Thanks Chris

cmoore · ‎05-16-2018

Thanks a lot for your advice

cmoore · ‎05-16-2018

Hi, Does anybody know a quick way to reverse strings. I basically have some strings like the below; Germany; Munich and I want to change them to Munich; Germany Would you know a good function to use to do this? Thanks Chris

cmoore · ‎03-23-2018

Thanks for your input. I have attempted to write a Pass-through query but it doesn't seem to connect. When I run the following it connects fine and I can see all the tables; libname Connect odbc complete="driver=SQL Server; database=connect_productionreports ; server=fcl-sql-v075" schema=dbo; run; When I just want to target the table with more than 32 characters I have tried the following; proc sql; connect to odbc (noprompt ="dsn=fcl-sql-v075;DRIVER=SQLServer;uid=c***e;pwd=Lo****;"); create table Prod02test as select * from connection to odbc (SELECT * FROM connect_productionreports.dbo.Contact_Company_ExtSL_FormalCRMProgrammesandTargetingLists ) ; disconnect from odbc; quit; This doesn't connect. Is there something stupid that I am doing as I've never needed to use these queries before. many thanks Chris

cmoore · ‎03-22-2018

Hi, I'm connecting to a SQL database via ODBC and run the following code to do so; /*Link via ODBC to the MS Connect Server*/ libname Connect odbc complete="driver=SQL Server; database=connect_productionreports ; server=fcl-sql-v075" schema=dbo; run; This piece of code allows me to view all tables in this area. It's come to my attention that some tables have been stored with a name of more 32 characters that I can't see. There is a table called "Contact_Company_ExtSL_ClientGroupGlobalRP " that has 3 columns that I can't see in the database, for example. Please could you tell me how I could amend the code above to pull in all tables regardless of the table length name? Many thanks Chris

cmoore · ‎02-28-2018

Thanks for the information. I have a list of headline titles containing company names in and company lookup table. Please see below; Have 1 Have 2 Headline_Title Client_Name FF_Sector 3I in takeover talks 3i Group Plc Global Financial Investors AP Moller Maersk to invest in AI technology Maersk Energy, Resources and Infrastructure to form partnership with EE BT Group BT Group PLC Power, Utilities & Renewables in fines due to miss selling EDF EDF Power, Utilities & Renewables Abbott labs to sell off Africa arm Abbot Laboratories Consumer and Healthcare Group Bank RBS post profit RBS Banks I want to extract out the list of company names from the text and match them to a company name lookup table that I have with cleansed company names. I want the table to look like this; Want Headline_Title Client_Name FF_Sector 3I in takeover talks 3i Group Plc Global Financial Investors AP Moller Maersk to invest in AI technology Maersk Energy, Resources and Infrastructure to form partnership with EE BT Group BT Group PLC Power, Utilities & Renewables in fines due to miss selling EDF EDF Power, Utilities & Renewables Abbott labs to sell off Africa arm Abbot Laboratories Consumer and Healthcare Group Bank RBS post profit RBS Banks I have thousands of records to match to and this is just an example. Would you happen to know what the best way is to do this? Thanks Chris

cmoore · ‎02-23-2018

It doesn't have to be perfect to be honest. 70% plus maybe. Just need a good way to fuzzy match the example data set then take it from there.

cmoore · ‎02-23-2018

Hi, I have two data sources of information that I want to merge together. The common matchkey across both sources is the Client Name. However, there are different variations of spellings for the same Client. Please see below a small example of the two datasets I have and what I would like it to be transformed to; Have1 Have2 Client_Name Sector Client_Name Sub_Sector 3i Group Plc Global Financial Investors 3i Private Equity 3m Company Industrials 3M Company (NYSE:MMM) Manufacturing industries Ag Barr Plc Consumer and Healthcare Group A.P. Møller - Maersk A/S (CPSE:MAERSK B) Transport Maersk Energy, Resources and Infrastructure AB Volvo (publ) (OM:VOLV B) Automotive Aa Plc Industrials Abertis Infrastructure funds Electrolux Consumer and Healthcare Group BT Telecoms Volvo Industrials 3M Holding Company Manufacturing industries Abb Ltd Industrials AB Electrolux (publ) (OM:ELUX B) Consumer Abbott Laboratories Consumer and Healthcare Group RBS Banks Abbvie Inc Consumer and Healthcare Group ABB Manufacturing industries Aberdeen Asset Management Plc Financial Institutions Group AAM PLC (LSE:ADN) Asset managers Aberforth Smaller Companies Financial Institutions Group AbbVie Incorp Health Abertis Infraestructuras Sa Global Financial Investors Abbott Laboratories (NYSE:ABT) Health Abn Amro Group Nv Financial Institutions Group Abertis Infraestructuras, S.A. (BME:ABE) Infrastructure funds Britsh Telecommunications Technology, Media and Telecoms AG BAR Consumer Royal Bank of Scotland PLC Financial Institutions Group AA Business Services The Coca Cola Company Consumer and Healthcare Group Coca cola Consumer And the final output should look like; Want Client Name Sector Sub_Sector 3i Group Plc Global Financial Investors Private Equity 3m Company Industrials Manufacturing industries Ag Barr Plc Consumer and Healthcare Group Consumer Maersk Energy, Resources and Infrastructure Transport Aa Plc Industrials Business Services Electrolux Consumer and Healthcare Group Consumer Volvo Industrials "Unknown" Abb Ltd Industrials Manufacturing industries Abbott Laboratories Consumer and Healthcare Group Health Abbvie Inc Consumer and Healthcare Group Health Aberdeen Asset Management Plc Financial Institutions Group Asset managers Aberforth Smaller Companies Financial Institutions Group Infrastructure funds Abertis Infraestructuras Sa Global Financial Investors Infrastructure funds Abn Amro Group Nv Financial Institutions Group "Unknown" Britsh Telecommunications Technology, Media and Telecoms Telecoms Royal Bank of Scotland PLC Financial Institutions Group Banks The Coca Cola Company Consumer and Healthcare Group Consumer Is there a reliable way for me to fuzzy match across the different client names? Any help would be greatly appreciated. Many thanks Chris

cmoore · ‎01-29-2018

Hi, I would be wanting the data to create a dataset and build up the history of all news alerts that come through. I will then use another internal dataset I have to merge together the data. I'm using SAS 9.4. Thanks Chris

cmoore · ‎01-26-2018

Hi, I have created automatic daily email alerts that come into my outlook email inbox. The alerts contain daily news article links that state the date and headline. Please see below an example; This is your e-mail from mergermarket generated by your alert: European M&A alert Computer services 25/01/2018 Econocom wants to make more acquisitions (translated) Bolt on/Opportunistic Consumer: Other 25/01/2018 Novartis could pay up to USD 10bn for individual acquisitions; no deadline for US generics unit decision (translated) Bolt on/Opportunistic, Cross Border, Takeover situations, Other, Other 24/01/2018 Companies to follow in Italy: new investments, growth plans, potential for M&A Proprietary Intelligence Companies for sale Consumer: Retail 25/01/2018 Emté owner Sligro expects to sell supermarket chain this year (translated) Auction/Privatization 24/01/2018 Pets at Home shareholder KKR placing 12.3% stake via AAB Companies for sale, Private equity related 24/01/2018 Yonghui Superstores wants no controlling stake in Carrefour China; company to resume stock trading on 25 January Cross Border, Takeover situations Is there away to link SAS to Microsoft Outlook 2010 to pull in emails from a subfolder in an inbox and specify the data contained in the body of the email? Ideally I would have the following columns per news link; Date, News_Headline, Practice Group and Sector. Any help on this would be greatly appreciated. Thanks Chris

cmoore · ‎12-14-2017

Thanks for your reply. So if I wanted to go back and retrieve " "CRH PLC TO BID" and also "BID FOR PPC LTD" how would I tweak the code? It's a function that I have never used before. I'll need to do some reading up around the subject. Many thanks Chris

Online Status	Offline
Date Last Visited	‎01-17-2019 03:26 AM

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Ranking values in Columns in Sequence

Re: Reverse Strings

Reverse Strings

Re: Connect via ODBC

Connect via ODBC

Re: Matching Company Names

Re: Cleaning a dataset to move column data to the left and ignore blan...

Re: Remove character is a string

Re: SAS Macros

Re: SAS Macros

Re: SAS Macros

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Re: Matching Company Names

Ranking values in Columns in Sequence

Re: Reverse Strings

Reverse Strings

Re: Connect via ODBC

Connect via ODBC

Re: Matching Company Names

Re: Matching Company Names

Matching Company Names

Re: Import Data from Outlook folder

Import Data from Outlook folder

Re: prxmatch syntax