topic Re: Help with joining 2 datasets in SAS Programming

topic Re: Help with joining 2 datasets in SAS Programming https://communities.sas.com/t5/SAS-Programming/Help-with-joining-2-datasets/m-p/887231#M350544 <PRE><CODE class=" language-sas">data want; call missing(number); merge table1 table2 ; by origin; run;</CODE></PRE> Tue, 01 Aug 2023 04:44:00 GMT Kurt_Bremser 2023-08-01T04:44:00Z Help with joining 2 datasets https://communities.sas.com/t5/SAS-Programming/Help-with-joining-2-datasets/m-p/887228#M350543 <P>I have the following data:</P> <P> </P> <P>I'd like to join on "Origin" (Table1) to "Origin" on Table2 and create a new dataset as per Table3 below.</P> <P>Table1</P> <TABLE style="border-collapse: collapse; width: 96pt;" border="0" width="128" cellspacing="0" cellpadding="0"> <TBODY> <TR style="height: 15.0pt;"> <TD width="64" height="20" class="xl63" style="height: 15.0pt; width: 48pt;">Origin</TD> <TD width="64" class="xl63" style="border-left: none; width: 48pt;">Number</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl63" style="height: 15.0pt; border-top: none;">A</TD> <TD align="right" class="xl63" style="border-top: none; border-left: none;">23</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl63" style="height: 15.0pt; border-top: none;">B</TD> <TD align="right" class="xl63" style="border-top: none; border-left: none;">45</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl63" style="height: 15.0pt; border-top: none;">C</TD> <TD align="right" class="xl63" style="border-top: none; border-left: none;">65</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl63" style="height: 15.0pt; border-top: none;">D</TD> <TD align="right" class="xl63" style="border-top: none; border-left: none;">250</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl63" style="height: 15.0pt; border-top: none;">E</TD> <TD align="right" class="xl63" style="border-top: none; border-left: none;">600</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl63" style="height: 15.0pt; border-top: none;">F</TD> <TD align="right" class="xl63" style="border-top: none; border-left: none;">200</TD> </TR> </TBODY> </TABLE> <P> </P> <P>Table 2</P> <TABLE style="border-collapse: collapse; width: 144pt;" border="0" width="191px" cellspacing="0" cellpadding="0"> <TBODY> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; width: 48pt;">Origin</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-left: none; width: 48pt;">Target</TD> <TD width="63.4219px" height="15px" class="xl65" style="border-left: none; width: 48pt;">Tfs</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">A</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">AB</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">13</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">A</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">AC</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">10</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">C</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">BA</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">65</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">D</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">DA</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">200</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">D</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">DL</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">50</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">BA</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">100</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">ZA</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">150</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">MN</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">200</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">NP</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">150</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">F</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">AB</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">100</TD> </TR> <TR style="height: 15.0pt;"> <TD width="63.75px" height="15px" class="xl65" style="height: 15.0pt; border-top: none;">F</TD> <TD width="63.8281px" height="15px" class="xl65" style="border-top: none; border-left: none;">ZA</TD> <TD width="63.4219px" height="15px" align="right" class="xl65" style="border-top: none; border-left: none;">100</TD> </TR> </TBODY> </TABLE> <P> Desired Output</P> <TABLE style="border-collapse: collapse; width: 192pt;" border="0" width="256" cellspacing="0" cellpadding="0"> <TBODY> <TR style="height: 15.0pt;"> <TD width="64" height="20" class="xl65" style="height: 15.0pt; width: 48pt;">Origin</TD> <TD width="64" class="xl65" style="border-left: none; width: 48pt;">Target</TD> <TD width="64" class="xl65" style="border-left: none; width: 48pt;">Number</TD> <TD width="64" class="xl65" style="border-left: none; width: 48pt;">Tfs</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">A</TD> <TD class="xl65" style="border-top: none; border-left: none;">AB</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">23</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">13</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">A</TD> <TD class="xl65" style="border-top: none; border-left: none;">AC</TD> <TD class="xl65" style="border-top: none; border-left: none;"> </TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">10</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">C</TD> <TD class="xl65" style="border-top: none; border-left: none;">BA</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">65</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">65</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">D</TD> <TD class="xl65" style="border-top: none; border-left: none;">DA</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">250</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">200</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">D</TD> <TD class="xl65" style="border-top: none; border-left: none;">DL</TD> <TD class="xl65" style="border-top: none; border-left: none;"> </TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">50</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD class="xl65" style="border-top: none; border-left: none;">BA</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">600</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">100</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD class="xl65" style="border-top: none; border-left: none;">ZA</TD> <TD class="xl65" style="border-top: none; border-left: none;"> </TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">150</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD class="xl65" style="border-top: none; border-left: none;">MN</TD> <TD class="xl65" style="border-top: none; border-left: none;"> </TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">200</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">E</TD> <TD class="xl65" style="border-top: none; border-left: none;">NP</TD> <TD class="xl65" style="border-top: none; border-left: none;"> </TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">150</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">F</TD> <TD class="xl65" style="border-top: none; border-left: none;">AB</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">200</TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">100</TD> </TR> <TR style="height: 15.0pt;"> <TD height="20" class="xl65" style="height: 15.0pt; border-top: none;">F</TD> <TD class="xl65" style="border-top: none; border-left: none;">ZA</TD> <TD class="xl65" style="border-top: none; border-left: none;"> </TD> <TD align="right" class="xl65" style="border-top: none; border-left: none;">100</TD> </TR> </TBODY> </TABLE> Tue, 01 Aug 2023 03:51:35 GMT https://communities.sas.com/t5/SAS-Programming/Help-with-joining-2-datasets/m-p/887228#M350543 Haydo 2023-08-01T03:51:35Z Re: Help with joining 2 datasets https://communities.sas.com/t5/SAS-Programming/Help-with-joining-2-datasets/m-p/887231#M350544 <PRE><CODE class=" language-sas">data want; call missing(number); merge table1 table2 ; by origin; run;</CODE></PRE> Tue, 01 Aug 2023 04:44:00 GMT https://communities.sas.com/t5/SAS-Programming/Help-with-joining-2-datasets/m-p/887231#M350544 Kurt_Bremser 2023-08-01T04:44:00Z