topic Re: Letter case when matching data in SAS Programming https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534638#M146731 <P>it doesnt change the case in the output , but it does convert both to uppercase when it is joining them together</P> Mon, 11 Feb 2019 22:12:56 GMT 34reqrwe 2019-02-11T22:12:56Z Letter case when matching data https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534634#M146727 <P>I have two data sets as following below.</P><P>&nbsp;</P><P>Data set 1 is a list of company names.</P><P>&nbsp;</P><P>Name</P><P><FONT color="#FF0000">Apple</FONT></P><P><FONT color="#0000FF">Google</FONT></P><P><FONT color="#99CC00">Amazon</FONT></P><P>&nbsp;</P><P>Data set 2 has the following variables.</P><P>&nbsp;</P><P>ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Year1&nbsp; &nbsp; &nbsp; &nbsp;USname&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;nameO&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Country&nbsp; &nbsp; Year2</P><P>01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1992&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Apple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<FONT color="#FF0000">Apple</FONT><SPAN>&nbsp;</SPAN>| Ford&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1993&nbsp; &nbsp; &nbsp;</P><P>02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1993&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Alphabet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Toyato |<FONT color="#99CC00"><SPAN>&nbsp;</SPAN>Amazon</FONT>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1995</P><P>03&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1994&nbsp; &nbsp; &nbsp; &nbsp;Amazon.com&nbsp; &nbsp; &nbsp; &nbsp;<FONT color="#99CC00">Amazon&nbsp;</FONT><SPAN>&nbsp;</SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1996</P><P>04&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1995&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Alphabet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<SPAN>&nbsp;</SPAN><FONT color="#0000FF">Google</FONT>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1997</P><P>05&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1996&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IBM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;IBM&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1998</P><P>&nbsp;</P><P>Name in data set 1 is the same with nameO in data set 2 (Google) or Name in data set1 is partly same with nameO in data set 2 (Apple).</P><P>&nbsp;</P><P>What I want is to match these two data sets as long as NameO is partly same with Name.</P><P>&nbsp;</P><P>Expected</P><P>ID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Year1&nbsp; &nbsp; &nbsp; &nbsp;USname&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;nameO&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Country&nbsp; &nbsp; Year2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;name</P><P>01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1992&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Apple&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<FONT color="#FF0000">Apple</FONT><SPAN>&nbsp;</SPAN>| Ford&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1993&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Apple</P><P>02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1993&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Alphabet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Toyato |<FONT color="#99CC00"><SPAN>&nbsp;</SPAN>Amazon</FONT>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1995&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Amazon</P><P>03&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1994&nbsp; &nbsp; &nbsp; &nbsp;Amazon.com&nbsp; &nbsp; &nbsp; &nbsp;<FONT color="#99CC00">Amazon&nbsp;</FONT><SPAN>&nbsp;</SPAN>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1996&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Amazon</P><P>04&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1995&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Alphabet&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<SPAN>&nbsp;</SPAN><FONT color="#0000FF">Google</FONT>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; US&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1997&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Google</P><P>&nbsp;</P><P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/18408">@Ksharp</a>&nbsp;offered the following codes to me. It is helpful.</P><P>&nbsp;</P><PRE class=" language-sas"><CODE class=" language-sas"><SPAN class="token procnames">proc</SPAN> <SPAN class="token procnames">sql</SPAN><SPAN class="token punctuation">;</SPAN> <SPAN class="token statement">select</SPAN> <SPAN class="token operator">*</SPAN> <SPAN class="token keyword">from</SPAN> b inner join a on b<SPAN class="token punctuation">.</SPAN>nameO contains <SPAN class="token function">strip</SPAN><SPAN class="token punctuation">(</SPAN>a<SPAN class="token punctuation">.</SPAN>name<SPAN class="token punctuation">)</SPAN><SPAN class="token punctuation">;</SPAN> <SPAN class="token procnames">quit</SPAN><SPAN class="token punctuation">;</SPAN></CODE></PRE><P>&nbsp;However, I found another problem.&nbsp;</P><P>&nbsp;</P><P>I found that Names using the upper case such as "APPLE" in data A cannot be matched with NameO using lower cases in data b such as "apple" or "Apple".</P><P>&nbsp;</P><P>Is there any way to ignore the lower case or upper case? Thanks.</P> Mon, 11 Feb 2019 21:51:04 GMT https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534634#M146727 dapenDaniel 2019-02-11T21:51:04Z Re: Letter case when matching data https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534635#M146728 <PRE><CODE class=" language-sas">proc sql; select * from b inner join a on upper(b.nameO) contains strip(upper(a.name)); quit;</CODE></PRE> Mon, 11 Feb 2019 22:02:35 GMT https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534635#M146728 34reqrwe 2019-02-11T22:02:35Z Re: Letter case when matching data https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534636#M146729 <P>so this code changes both names as upper cases. Is it correct? Thanks.</P> Mon, 11 Feb 2019 22:11:04 GMT https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534636#M146729 dapenDaniel 2019-02-11T22:11:04Z Re: Letter case when matching data https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534638#M146731 <P>it doesnt change the case in the output , but it does convert both to uppercase when it is joining them together</P> Mon, 11 Feb 2019 22:12:56 GMT https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534638#M146731 34reqrwe 2019-02-11T22:12:56Z Re: Letter case when matching data https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534640#M146733 <P>Thank you so much!</P> Mon, 11 Feb 2019 22:15:37 GMT https://communities.sas.com/t5/SAS-Programming/Letter-case-when-matching-data/m-p/534640#M146733 dapenDaniel 2019-02-11T22:15:37Z