topic compare two names to find a match in SAS Programming https://communities.sas.com/t5/SAS-Programming/compare-two-names-to-find-a-match/m-p/696525#M212769 <P>hello,&nbsp;</P> <P>I had a file that contain two names column. I want to find the perfect match. I want then to compare the column Fund_name with the column Fund_name_CRSP to find the most similar name for each secid. comme match by secid.&nbsp;</P> <P>&nbsp;</P> <TABLE width="888"> <TBODY> <TR> <TD width="98">secid</TD> <TD width="64">Fund_name</TD> <TD width="93">Inception_date</TD> <TD width="64">CRSP_FUNDNO</TD> <TD width="121">FIRST_OFFER_DT</TD> <TD colspan="2" width="128">FUND_NAME_CRSP</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>31345</TD> <TD>2007-03-08</TD> <TD colspan="7">Vanguard International Equity Index Funds: Vanguard FTSE All-World ex US Index Fund; Investor Shares</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>49091</TD> <TD>2007-03-08</TD> <TD colspan="7">Fidelity Rutland Square Trust II: Strategic Advisers Fidelity International Fund</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>49091</TD> <TD>2007-03-08</TD> <TD colspan="7">Fidelity Rutland Square Trust II: Strategic Advisers International II Fund</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>49093</TD> <TD>2007-03-08</TD> <TD colspan="7">Fidelity Rutland Square Trust II: Strategic Advisers US Opportunity II Fund</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16966</TD> <TD>2005-11-25</TD> <TD colspan="7">Jennison Sector Funds, Inc: Jennison Health Sciences Fund; Class X Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16966</TD> <TD>2005-11-25</TD> <TD colspan="7">JennisonDryden Sector Funds: Jennison Health Sciences Fund; Class X Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16966</TD> <TD>2005-11-25</TD> <TD colspan="7">Prudential Sector Funds, Inc: Prudential Jennison Health Sciences Fund; Class X Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16967</TD> <TD>2005-11-25</TD> <TD colspan="7">Jennison Sector Funds, Inc: Jennison Health Sciences Fund; Class M Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16967</TD> <TD>2005-11-25</TD> <TD colspan="7">JennisonDryden Sector Funds: Jennison Health Sciences Fund; Class M Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16967</TD> <TD>2005-11-25</TD> <TD colspan="7">Prudential Sector Funds, Inc: Prudential Jennison Health Sciences Fund; Class M Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16968</TD> <TD>2005-11-25</TD> <TD colspan="7">Jennison Sector Funds, Inc: Jennison Health Sciences Fund; Class L Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16968</TD> <TD>2005-11-25</TD> <TD colspan="7">JennisonDryden Sector Funds: Jennison Health Sciences Fund; Class L Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16968</TD> <TD>2005-11-25</TD> <TD colspan="7">Prudential Sector Funds, Inc: Prudential Jennison Health Sciences Fund; Class L Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36620</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class A Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36621</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class C Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36622</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class K Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36622</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class R Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36623</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Institutional Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36938</TD> <TD>2007-12-05</TD> <TD colspan="7">ING Funds Trust: ING Institutional Prime Money Market Fund; Class IS shares</TD> </TR> </TBODY> </TABLE> Wed, 04 Nov 2020 14:47:48 GMT sasphd 2020-11-04T14:47:48Z compare two names to find a match https://communities.sas.com/t5/SAS-Programming/compare-two-names-to-find-a-match/m-p/696525#M212769 <P>hello,&nbsp;</P> <P>I had a file that contain two names column. I want to find the perfect match. I want then to compare the column Fund_name with the column Fund_name_CRSP to find the most similar name for each secid. comme match by secid.&nbsp;</P> <P>&nbsp;</P> <TABLE width="888"> <TBODY> <TR> <TD width="98">secid</TD> <TD width="64">Fund_name</TD> <TD width="93">Inception_date</TD> <TD width="64">CRSP_FUNDNO</TD> <TD width="121">FIRST_OFFER_DT</TD> <TD colspan="2" width="128">FUND_NAME_CRSP</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> <TD width="64">&nbsp;</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>31345</TD> <TD>2007-03-08</TD> <TD colspan="7">Vanguard International Equity Index Funds: Vanguard FTSE All-World ex US Index Fund; Investor Shares</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>49091</TD> <TD>2007-03-08</TD> <TD colspan="7">Fidelity Rutland Square Trust II: Strategic Advisers Fidelity International Fund</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>49091</TD> <TD>2007-03-08</TD> <TD colspan="7">Fidelity Rutland Square Trust II: Strategic Advisers International II Fund</TD> </TR> <TR> <TD>F000000DL0</TD> <TD>Macquarie IPO China Concentrated Core A1</TD> <TD>2007-03-08</TD> <TD>49093</TD> <TD>2007-03-08</TD> <TD colspan="7">Fidelity Rutland Square Trust II: Strategic Advisers US Opportunity II Fund</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16966</TD> <TD>2005-11-25</TD> <TD colspan="7">Jennison Sector Funds, Inc: Jennison Health Sciences Fund; Class X Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16966</TD> <TD>2005-11-25</TD> <TD colspan="7">JennisonDryden Sector Funds: Jennison Health Sciences Fund; Class X Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16966</TD> <TD>2005-11-25</TD> <TD colspan="7">Prudential Sector Funds, Inc: Prudential Jennison Health Sciences Fund; Class X Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16967</TD> <TD>2005-11-25</TD> <TD colspan="7">Jennison Sector Funds, Inc: Jennison Health Sciences Fund; Class M Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16967</TD> <TD>2005-11-25</TD> <TD colspan="7">JennisonDryden Sector Funds: Jennison Health Sciences Fund; Class M Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16967</TD> <TD>2005-11-25</TD> <TD colspan="7">Prudential Sector Funds, Inc: Prudential Jennison Health Sciences Fund; Class M Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16968</TD> <TD>2005-11-25</TD> <TD colspan="7">Jennison Sector Funds, Inc: Jennison Health Sciences Fund; Class L Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16968</TD> <TD>2005-11-25</TD> <TD colspan="7">JennisonDryden Sector Funds: Jennison Health Sciences Fund; Class L Shares</TD> </TR> <TR> <TD>F000000JLW</TD> <TD>WCM Hippocrates A USD</TD> <TD>2005-11-25</TD> <TD>16968</TD> <TD>2005-11-25</TD> <TD colspan="7">Prudential Sector Funds, Inc: Prudential Jennison Health Sciences Fund; Class L Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36620</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class A Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36621</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class C Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36622</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class K Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36622</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Class R Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36623</TD> <TD>2007-12-05</TD> <TD colspan="7">Federated Equity Funds: Federated Kaufmann Large Cap Fund; Institutional Shares</TD> </TR> <TR> <TD>F000000KTA</TD> <TD>Macquarie Asia New Stars A1</TD> <TD>2007-12-05</TD> <TD>36938</TD> <TD>2007-12-05</TD> <TD colspan="7">ING Funds Trust: ING Institutional Prime Money Market Fund; Class IS shares</TD> </TR> </TBODY> </TABLE> Wed, 04 Nov 2020 14:47:48 GMT https://communities.sas.com/t5/SAS-Programming/compare-two-names-to-find-a-match/m-p/696525#M212769 sasphd 2020-11-04T14:47:48Z Re: compare two names to find a match https://communities.sas.com/t5/SAS-Programming/compare-two-names-to-find-a-match/m-p/696530#M212771 Can you offer some guidance by ranking the best to worst matches for the four rows of F000000DL0? Wed, 04 Nov 2020 14:54:46 GMT https://communities.sas.com/t5/SAS-Programming/compare-two-names-to-find-a-match/m-p/696530#M212771 RichardDeVen 2020-11-04T14:54:46Z Re: compare two names to find a match https://communities.sas.com/t5/SAS-Programming/compare-two-names-to-find-a-match/m-p/696537#M212773 <P>I have checked and there is no match in the sample I give. if there is no match I want that the output table that indicate no match will like this</P> <TABLE style="border-collapse: collapse; width: 372pt;" border="0" width="498" cellspacing="0" cellpadding="0"> <TBODY> <TR style="height: 12.6pt;"> <TD width="83" height="17" style="height: 12.6pt; width: 62pt;">secid</TD> <TD width="83" style="width: 62pt;">Fund_name</TD> <TD width="83" style="width: 62pt;">Inception_date</TD> <TD width="83" style="width: 62pt;">CRSP_FUNDNO</TD> <TD width="83" style="width: 62pt;">FIRST_OFFER_DT</TD> <TD width="83" style="width: 62pt;">FUND_NAME_CRSP</TD> </TR> <TR style="height: 12.6pt;"> <TD height="17" style="height: 12.6pt;">F000000DL0</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>.</TD> </TR> <TR style="height: 12.6pt;"> <TD height="17" style="height: 12.6pt;">F000000JLW</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>.</TD> </TR> </TBODY> </TABLE> <P>&nbsp;</P> <P>&nbsp;</P> <P>however this is an example where we find a match</P> <TABLE width="1091"> <TBODY> <TR> <TD width="83">secid</TD> <TD width="261">Fund_name</TD> <TD width="83">Inception_date</TD> <TD width="83">CRSP_FUNDNO</TD> <TD width="83">FIRST_OFFER_DT</TD> <TD colspan="2" width="166">FUND_NAME_CRSP</TD> <TD width="83">&nbsp;</TD> <TD width="83">&nbsp;</TD> <TD width="83">&nbsp;</TD> <TD width="83">&nbsp;</TD> </TR> <TR> <TD>F000002KYY</TD> <TD>JHancock Disciplined Value R4</TD> <TD>2009-05-22</TD> <TD>46807</TD> <TD>2009-05-22</TD> <TD colspan="6">John Hancock Funds III: John Hancock Disciplined Value Fund; Class R3 Shares</TD> </TR> <TR> <TD><STRONG>F000002KYY</STRONG></TD> <TD><STRONG>JHancock Disciplined Value R4</STRONG></TD> <TD><STRONG>2009-05-22</STRONG></TD> <TD><STRONG>46808</STRONG></TD> <TD><STRONG>2009-05-22</STRONG></TD> <TD colspan="6"><STRONG>John Hancock Funds III: John Hancock Disciplined Value Fund; Class R4 Shares</STRONG></TD> </TR> <TR> <TD>F000002KYY</TD> <TD>JHancock Disciplined Value R4</TD> <TD>2009-05-22</TD> <TD>46809</TD> <TD>2009-05-22</TD> <TD colspan="6">John Hancock Funds III: John Hancock Disciplined Value Fund; Class R5 Shares</TD> </TR> <TR> <TD>F000002KYY</TD> <TD>JHancock Disciplined Value R4</TD> <TD>2009-05-22</TD> <TD>46810</TD> <TD>2009-05-22</TD> <TD colspan="5">John Hancock Funds III: International Core Fund; Class R3 Shares</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>F000002KYY</TD> <TD>JHancock Disciplined Value R4</TD> <TD>2009-05-22</TD> <TD>46811</TD> <TD>2009-05-22</TD> <TD colspan="5">John Hancock Funds III: International Core Fund; Class R4 Shares</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>F000002KYY</TD> <TD>JHancock Disciplined Value R4</TD> <TD>2009-05-22</TD> <TD>46812</TD> <TD>2009-05-22</TD> <TD colspan="5">John Hancock Funds III: International Core Fund; Class R5 Shares</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>F000002KYY</TD> <TD>JHancock Disciplined Value R4</TD> <TD>2009-05-22</TD> <TD>46813</TD> <TD>2009-05-22</TD> <TD colspan="6">John Hancock Investment Trust: John Hancock Fundamental Large Cap Core Fund; Class R1 Shares</TD> </TR> <TR> <TD>F000002KYY</TD> <TD>JHancock Disciplined Value R4</TD> <TD>2009-05-22</TD> <TD>46813</TD> <TD>2009-05-22</TD> <TD colspan="6">John Hancock Investment Trust: John Hancock Large Cap Equity Fund; Class R1 Shares</TD> </TR> </TBODY> </TABLE> Wed, 04 Nov 2020 15:15:38 GMT https://communities.sas.com/t5/SAS-Programming/compare-two-names-to-find-a-match/m-p/696537#M212773 sasphd 2020-11-04T15:15:38Z