topic Re: Shortest Distance in SAS Programming https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/723857#M224704 <P>your states and zip data appear be corrupted</P> Fri, 05 Mar 2021 13:20:11 GMT PhilC 2021-03-05T13:20:11Z Shortest Distance https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/723852#M224701 <P><SPAN>The solution (Shortest Distance Match) that was provided earlier this week worked fine, however; I have been asked to modify the criteria as below.&nbsp; I tried modifying the data step but unable to get the expected output.&nbsp; I can take dividual Miles_Rank one at a time in a separate data step but in our actual data file, we have 10 unique Miles_Ranks (1 to10) for each ZIP.&nbsp; I believe, there must be a way to have the Miles Ranks logic build in one or two data steps.&nbsp; I am attaching revised raw data (have1 and have2) and also included expected output sheet.&nbsp; Sorry, but I am posting this again.&nbsp; Once again thank you so much! </SPAN></P><P>&nbsp;</P><P><SPAN>1. Match on zip, city and state (have1 and have2)</SPAN></P><P><SPAN>2. Want1&nbsp;<U>all records</U>&nbsp;from have1 with ClientNo where <U>there is a match based on the shortest distance</U> (Miles_Rank)</SPAN></P><P><SPAN>3. Also, Want2 a list of records from have1 <U>where there is no match</U></SPAN></P> Fri, 05 Mar 2021 12:59:58 GMT https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/723852#M224701 AshPatel 2021-03-05T12:59:58Z Re: Shortest Distance https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/723857#M224704 <P>your states and zip data appear be corrupted</P> Fri, 05 Mar 2021 13:20:11 GMT https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/723857#M224704 PhilC 2021-03-05T13:20:11Z Re: Shortest Distance_var2 https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724004#M224751 <P>have1</P><TABLE><TBODY><TR><TD>ZIP</TD><TD>ClientNo</TD><TD>City</TD><TD>State</TD></TR><TR><TD>14201</TD><TD>A95572</TD><TD>Cedar Grove</TD><TD>TA</TD></TR><TR><TD>14204</TD><TD>A91374</TD><TD>Cedar Grove</TD><TD>NH</TD></TR><TR><TD>14206</TD><TD>A71649</TD><TD>Bristol</TD><TD>KA</TD></TR><TR><TD>14207</TD><TD>A63397</TD><TD>Buffalo</TD><TD>MI</TD></TR><TR><TD>14208</TD><TD>A06957</TD><TD>Cedar Grove</TD><TD>NJ</TD></TR><TR><TD>14209</TD><TD>A58062</TD><TD>Cedar Grove</TD><TD>ID</TD></TR><TR><TD>14210</TD><TD>A43865</TD><TD>Bristol</TD><TD>IL</TD></TR><TR><TD>14211</TD><TD>A06391</TD><TD>Bristol</TD><TD>HA</TD></TR><TR><TD>14212</TD><TD>A54498</TD><TD>Cedar Grove</TD><TD>NC</TD></TR><TR><TD>14213</TD><TD>A09533</TD><TD>Buffalo</TD><TD>UT</TD></TR><TR><TD>14213</TD><TD>A09534</TD><TD>Buffalo</TD><TD>UT</TD></TR><TR><TD>14214</TD><TD>A31165</TD><TD>Buffalo</TD><TD>DC</TD></TR><TR><TD>14215</TD><TD>A52498</TD><TD>Buffalo</TD><TD>NY</TD></TR><TR><TD>14216</TD><TD>A77384</TD><TD>Buffalo</TD><TD>CL</TD></TR><TR><TD>14220</TD><TD>A73552</TD><TD>Buffalo</TD><TD>OH</TD></TR><TR><TD>14220</TD><TD>A73553</TD><TD>Buffalo</TD><TD>OH</TD></TR><TR><TD>14220</TD><TD>A73554</TD><TD>Buffalo</TD><TD>OH</TD></TR><TR><TD>14222</TD><TD>A66949</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>A66950</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>A66951</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>A66952</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>A66953</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>A66954</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>A66955</TD><TD>Bristol</TD><TD>SC</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>have2</P><TABLE><TBODY><TR><TD>ZIP</TD><TD>Miles_Rank</TD><TD>Distance_Miles</TD><TD>City</TD><TD>State</TD></TR><TR><TD>14201</TD><TD>1</TD><TD>1.1</TD><TD>Cedar Grove</TD><TD>TA</TD></TR><TR><TD>14201</TD><TD>2</TD><TD>5.3</TD><TD>Sloan</TD><TD>NY</TD></TR><TR><TD>14201</TD><TD>3</TD><TD>9.3</TD><TD>Amherst</TD><TD>CL</TD></TR><TR><TD>14201</TD><TD>4</TD><TD>15.5</TD><TD>Buffalo</TD><TD>MI</TD></TR><TR><TD>14201</TD><TD>5</TD><TD>30.1</TD><TD>Mo Town</TD><TD>NJ</TD></TR><TR><TD>14204</TD><TD>1</TD><TD>0.3</TD><TD>Cedar Grove</TD><TD>HA</TD></TR><TR><TD>14204</TD><TD>1</TD><TD>0.3</TD><TD>Blasdell</TD><TD>CL</TD></TR><TR><TD>14204</TD><TD>2</TD><TD>1.5</TD><TD>Cedar Grove</TD><TD>HA</TD></TR><TR><TD>14204</TD><TD>4</TD><TD>12</TD><TD>Blasdell</TD><TD>TA</TD></TR><TR><TD>14204</TD><TD>5</TD><TD>15</TD><TD>Cedar Grove</TD><TD>HA</TD></TR><TR><TD>14211</TD><TD>1</TD><TD>1.1</TD><TD>Buffalo</TD><TD>MI</TD></TR><TR><TD>14211</TD><TD>2</TD><TD>5.3</TD><TD>Mo Town</TD><TD>NJ</TD></TR><TR><TD>14211</TD><TD>3</TD><TD>9.3</TD><TD>Bristol</TD><TD>HA</TD></TR><TR><TD>14211</TD><TD>4</TD><TD>15.5</TD><TD>Amherst</TD><TD>CL</TD></TR><TR><TD>14211</TD><TD>5</TD><TD>30.1</TD><TD>Cedar Grove</TD><TD>HA</TD></TR><TR><TD>14213</TD><TD>1</TD><TD>0.4</TD><TD>Buffalo</TD><TD>MI</TD></TR><TR><TD>14213</TD><TD>2</TD><TD>1.5</TD><TD>Buffalo</TD><TD>UT</TD></TR><TR><TD>14213</TD><TD>3</TD><TD>8</TD><TD>Blasdell</TD><TD>CL</TD></TR><TR><TD>14213</TD><TD>4</TD><TD>12</TD><TD>Buffalo</TD><TD>NJ</TD></TR><TR><TD>14213</TD><TD>5</TD><TD>15</TD><TD>Blasdell</TD><TD>TA</TD></TR><TR><TD>14214</TD><TD>1</TD><TD>0.2</TD><TD>Buffalo</TD><TD>DC</TD></TR><TR><TD>14214</TD><TD>2</TD><TD>0.4</TD><TD>Buffalo</TD><TD>DC</TD></TR><TR><TD>14214</TD><TD>3</TD><TD>1.5</TD><TD>Buffalo</TD><TD>DC</TD></TR><TR><TD>14214</TD><TD>4</TD><TD>8</TD><TD>Buffalo</TD><TD>DC</TD></TR><TR><TD>14214</TD><TD>5</TD><TD>15</TD><TD>Buffalo</TD><TD>DC</TD></TR><TR><TD>14215</TD><TD>1</TD><TD>1.2</TD><TD>Sloan</TD><TD>CO</TD></TR><TR><TD>14215</TD><TD>2</TD><TD>2</TD><TD>Amherst</TD><TD>AL</TD></TR><TR><TD>14215</TD><TD>3</TD><TD>2.5</TD><TD>Buffalo</TD><TD>NJ</TD></TR><TR><TD>14215</TD><TD>4</TD><TD>6</TD><TD>Buffalo</TD><TD>NY</TD></TR><TR><TD>14215</TD><TD>5</TD><TD>10</TD><TD>Kenmore</TD><TD>DE</TD></TR><TR><TD>14216</TD><TD>1</TD><TD>2</TD><TD>Cheektowaga</TD><TD>AR</TD></TR><TR><TD>14216</TD><TD>2</TD><TD>7.2</TD><TD>Great Hill</TD><TD>GE</TD></TR><TR><TD>14216</TD><TD>3</TD><TD>8.8</TD><TD>Buffalo</TD><TD>NJ</TD></TR><TR><TD>14216</TD><TD>4</TD><TD>14.2</TD><TD>Fort Erie</TD><TD>XX</TD></TR><TR><TD>14216</TD><TD>5</TD><TD>21.6</TD><TD>Buffalo</TD><TD>CL</TD></TR><TR><TD>14220</TD><TD>1</TD><TD>3</TD><TD>Buffalo</TD><TD>WY</TD></TR><TR><TD>14220</TD><TD>2</TD><TD>4.6</TD><TD>Milton</TD><TD>NE</TD></TR><TR><TD>14220</TD><TD>3</TD><TD>8</TD><TD>Milton</TD><TD>WA</TD></TR><TR><TD>14220</TD><TD>4</TD><TD>14.9</TD><TD>Buffalo</TD><TD>OH</TD></TR><TR><TD>14220</TD><TD>5</TD><TD>26.2</TD><TD>Milton</TD><TD>TE</TD></TR><TR><TD>14222</TD><TD>1</TD><TD>3</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>2</TD><TD>4.6</TD><TD>Bristol</TD><TD>SC</TD></TR><TR><TD>14222</TD><TD>3</TD><TD>8</TD><TD>Bristol</TD><TD>SC</TD></TR></TBODY></TABLE> Fri, 05 Mar 2021 20:03:04 GMT https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724004#M224751 AshPatel 2021-03-05T20:03:04Z Re: Shortest Distance https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724076#M224778 Hi Phil, I have reloaded and pasted have1 and have2 again..Please let me know if stil issue with the data Fri, 05 Mar 2021 22:43:47 GMT https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724076#M224778 AshPatel 2021-03-05T22:43:47Z Re: Shortest Distance https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724519#M224940 <P>My remark is two fold, that, for one, the data does not conform with USPS&nbsp;<A href="https://about.usps.com/who-we-are/postal-history/state-abbreviations.htm" target="_blank" rel="noopener">State Abbreviations</A>. Secondly, The zip codes should map many zips to one State, which is not true for your data.&nbsp; I'm afraid, Code developed on this example data may be misleadingly incorrect when applied to validated address data.</P> Mon, 08 Mar 2021 15:04:50 GMT https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724519#M224940 PhilC 2021-03-08T15:04:50Z Re: Shortest Distance https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724831#M225070 Thanks Phil. These are fake data. Any help would be greatly appreciated. Tue, 09 Mar 2021 11:59:07 GMT https://communities.sas.com/t5/SAS-Programming/Shortest-Distance/m-p/724831#M225070 AshPatel 2021-03-09T11:59:07Z