topic Re: add computed column based on first letter of previous column in SAS Enterprise Guide https://communities.sas.com/t5/SAS-Enterprise-Guide/add-computed-column-based-on-first-letter-of-previous-column/m-p/442323#M28544 brilliant! thanks Kurt <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span> Mon, 05 Mar 2018 12:28:02 GMT Kran 2018-03-05T12:28:02Z add computed column based on first letter of previous column https://communities.sas.com/t5/SAS-Enterprise-Guide/add-computed-column-based-on-first-letter-of-previous-column/m-p/442283#M28537 <P>Hi all,</P><P>&nbsp;</P><P>I would like to <STRONG>add</STRONG> an indicator column&nbsp;named <STRONG>'Zone'&nbsp;</STRONG>for <STRONG>postcode</STRONG> data&nbsp;for &nbsp;<STRONG>'From'</STRONG> and <STRONG>'To'</STRONG> in my shipment dataset.</P><P>These columns are of <STRONG>character</STRONG> format.</P><P>&nbsp;</P><P>I have data as below:</P><P>ShipmentID&nbsp;&nbsp;&nbsp; Typeofservice&nbsp;&nbsp;&nbsp;From&nbsp;&nbsp;&nbsp;&nbsp; To&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Quantity&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Price</P><P>&nbsp;&nbsp;&nbsp;&nbsp; xx1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1010&nbsp;&nbsp;&nbsp;&nbsp; 2010&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 5&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100</P><P>&nbsp;&nbsp;&nbsp;&nbsp; xx2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3030&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1050&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 80</P><P>&nbsp;&nbsp;&nbsp;&nbsp; xx3&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; z&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2010&nbsp;&nbsp;&nbsp;&nbsp; 4050&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 100</P><P>&nbsp;&nbsp;&nbsp;&nbsp; xx4&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; y&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7050&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;1010&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 150</P><P>&nbsp;</P><P>so above I would like to create a column named <STRONG>Zone</STRONG> dependent on the <STRONG>first</STRONG> <STRONG>letter</STRONG> of the <STRONG>From</STRONG> and <STRONG>To</STRONG> columns..</P><P>which means</P><P><STRONG>1000</STRONG> - <STRONG>1999</STRONG> is zone <STRONG>1</STRONG>...</P><P><STRONG>2000</STRONG>-<STRONG>2999</STRONG> is zone <STRONG>2</STRONG></P><P><STRONG>3000-3999 </STRONG>is zone <STRONG>3</STRONG> and so on...</P><P>&nbsp;</P><P>Thank you in advance for any help!</P><P>&nbsp;</P><P>&nbsp;</P> Mon, 05 Mar 2018 09:53:06 GMT https://communities.sas.com/t5/SAS-Enterprise-Guide/add-computed-column-based-on-first-letter-of-previous-column/m-p/442283#M28537 Kran 2018-03-05T09:53:06Z Re: add computed column based on first letter of previous column https://communities.sas.com/t5/SAS-Enterprise-Guide/add-computed-column-based-on-first-letter-of-previous-column/m-p/442287#M28538 <PRE><CODE class=" language-sas">zone_from = substr(left(from),1,1));</CODE></PRE> <P>The left() function left-adjusts the contents and so eliminates any leading blanks, if such were present.</P> Mon, 05 Mar 2018 10:03:58 GMT https://communities.sas.com/t5/SAS-Enterprise-Guide/add-computed-column-based-on-first-letter-of-previous-column/m-p/442287#M28538 Kurt_Bremser 2018-03-05T10:03:58Z Re: add computed column based on first letter of previous column https://communities.sas.com/t5/SAS-Enterprise-Guide/add-computed-column-based-on-first-letter-of-previous-column/m-p/442323#M28544 brilliant! thanks Kurt <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span> Mon, 05 Mar 2018 12:28:02 GMT https://communities.sas.com/t5/SAS-Enterprise-Guide/add-computed-column-based-on-first-letter-of-previous-column/m-p/442323#M28544 Kran 2018-03-05T12:28:02Z