topic Re: matching tables and filling in values in SAS Programming https://communities.sas.com/t5/SAS-Programming/matching-tables-and-filling-in-values/m-p/730823#M227623 <TABLE width="1240"> <TBODY> <TR> <TD colspan="2" width="173">table1_base_EducDep</TD> <TD width="64">&nbsp;</TD> <TD width="70">&nbsp;</TD> <TD width="57">&nbsp;</TD> <TD width="31">&nbsp;</TD> <TD width="43">&nbsp;</TD> <TD width="135">&nbsp;</TD> <TD width="95">&nbsp;</TD> <TD width="90">&nbsp;</TD> <TD width="92">&nbsp;</TD> <TD width="104">&nbsp;</TD> <TD width="79">&nbsp;</TD> <TD width="96">&nbsp;</TD> <TD width="111">&nbsp;</TD> </TR> <TR> <TD>2020DSS_clientNumber</TD> <TD>lname</TD> <TD>fname</TD> <TD>dob</TD> <TD>address</TD> <TD>city</TD> <TD>county</TD> <TD>2021DSS_clientNumber</TD> <TD>2021DSS_lname</TD> <TD>2021DSS_fname</TD> <TD>2021DSS_dob</TD> <TD>2021DSS_address</TD> <TD>2021DSS_city</TD> <TD>2021DSS_county</TD> <TD>2021DSS_program</TD> </TR> <TR> <TD>1</TD> <TD>Meyer</TD> <TD>Danny</TD> <TD>12/1/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>1</TD> <TD>Meyer</TD> <TD>Danny</TD> <TD>12/1/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>&nbsp;</TD> <TD>Smith</TD> <TD>Rose</TD> <TD>6/1/2013</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>2</TD> <TD>Smith</TD> <TD>Rosa</TD> <TD>6/1/2013</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>3</TD> <TD>Quy</TD> <TD>Mary</TD> <TD>5/7/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>3</TD> <TD>Quy</TD> <TD>Mary</TD> <TD>5/7/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>4</TD> <TD>Martin</TD> <TD>Nia</TD> <TD>6/15/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>4</TD> <TD>Martin</TD> <TD>Nia</TD> <TD>6/15/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>&nbsp;</TD> <TD>Varquiz</TD> <TD>Antonio</TD> <TD>2/24/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>5</TD> <TD>Varquiz</TD> <TD>Antonio</TD> <TD>3/24/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>&nbsp;</TD> <TD>Howell</TD> <TD>Arianna</TD> <TD>3/14/2012</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>6</TD> <TD>Howell</TD> <TD>Arianne</TD> <TD>3/14/2012</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> </TBODY> </TABLE> Thu, 01 Apr 2021 19:25:23 GMT Bintang18 2021-04-01T19:25:23Z matching tables and filling in values https://communities.sas.com/t5/SAS-Programming/matching-tables-and-filling-in-values/m-p/730808#M227615 <TABLE><TBODY><TR><TD>table1_base_EducDep</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>clientNumber</TD><TD>lname</TD><TD>fname</TD><TD>dob</TD><TD>address</TD><TD>city</TD><TD>county</TD><TD>&nbsp;</TD></TR><TR><TD>1</TD><TD>Meyer</TD><TD>Danny</TD><TD>12/1/2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>Smith</TD><TD>Rose</TD><TD>6/1/2013</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>3</TD><TD>Quy</TD><TD>Mary</TD><TD>5/7/2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>4</TD><TD>Martin</TD><TD>Nia</TD><TD>6/15/2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>Varquiz</TD><TD>Antonio</TD><TD>2/24/2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>Howell</TD><TD>Arianna</TD><TD>3/14/2012</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>table2_DepofSocService</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>clientNumber</TD><TD>lname</TD><TD>fname</TD><TD>dob</TD><TD>address</TD><TD>city</TD><TD>county</TD><TD>dss_program</TD></TR><TR><TD>1</TD><TD>Meyer</TD><TD>Danny</TD><TD>12/1/2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>2</TD><TD>Smith</TD><TD>Rosa</TD><TD>6/1/2013</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>3</TD><TD>Quy</TD><TD>Mary</TD><TD>5/7/2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>4</TD><TD>Martin</TD><TD>Nia</TD><TD>6/15/2010</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>5</TD><TD>Varquiz</TD><TD>Antonio</TD><TD>3/24/2011</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR><TR><TD>6</TD><TD>Howell</TD><TD>Arianne</TD><TD>3/14/2012</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></TR></TBODY></TABLE><P>I need to combine 2 tables above, has all <STRONG>six students WITH their client numbers and dss_program.</STRONG> Table1: the state has over a million students, and Table2: DSS has over 300,000 kids receive some type of benefit/programs. The goal is to get as many students as possible qualify for the benefits by matching with kids in DSS' file.</P><P>Meyer, Quy and Martin currently have ClientNumber, pulled from 2019-2020 school year, but the other three are new this year so they do not have clientNumber.</P><P>First round, using proc sql I pulled data from DSS on clientNumber=clientNumber.</P><P>Second round, I pulled more data using a.lname=b.lastname and a.fname=b.firstname and a.dob=b.dob. This process is long and tedious, had to change var names twice, etc. and had to 'fill in' blank values var by var 'manually'. At the end I have hundred of students missing, like Smith, Varquis and Howell because of typo: lname/fname and/or dob do not match.</P><P>Is there a better way to do this?&nbsp;</P><P>Thank you all in advance, and hope this makes sense. I am using EG version 8.2</P><P>&nbsp;</P><P>&nbsp;</P> Thu, 01 Apr 2021 18:43:13 GMT https://communities.sas.com/t5/SAS-Programming/matching-tables-and-filling-in-values/m-p/730808#M227615 Bintang18 2021-04-01T18:43:13Z Re: matching tables and filling in values https://communities.sas.com/t5/SAS-Programming/matching-tables-and-filling-in-values/m-p/730811#M227618 <P>What does your desired result look like?</P> Thu, 01 Apr 2021 18:51:41 GMT https://communities.sas.com/t5/SAS-Programming/matching-tables-and-filling-in-values/m-p/730811#M227618 PeterClemmensen 2021-04-01T18:51:41Z Re: matching tables and filling in values https://communities.sas.com/t5/SAS-Programming/matching-tables-and-filling-in-values/m-p/730823#M227623 <TABLE width="1240"> <TBODY> <TR> <TD colspan="2" width="173">table1_base_EducDep</TD> <TD width="64">&nbsp;</TD> <TD width="70">&nbsp;</TD> <TD width="57">&nbsp;</TD> <TD width="31">&nbsp;</TD> <TD width="43">&nbsp;</TD> <TD width="135">&nbsp;</TD> <TD width="95">&nbsp;</TD> <TD width="90">&nbsp;</TD> <TD width="92">&nbsp;</TD> <TD width="104">&nbsp;</TD> <TD width="79">&nbsp;</TD> <TD width="96">&nbsp;</TD> <TD width="111">&nbsp;</TD> </TR> <TR> <TD>2020DSS_clientNumber</TD> <TD>lname</TD> <TD>fname</TD> <TD>dob</TD> <TD>address</TD> <TD>city</TD> <TD>county</TD> <TD>2021DSS_clientNumber</TD> <TD>2021DSS_lname</TD> <TD>2021DSS_fname</TD> <TD>2021DSS_dob</TD> <TD>2021DSS_address</TD> <TD>2021DSS_city</TD> <TD>2021DSS_county</TD> <TD>2021DSS_program</TD> </TR> <TR> <TD>1</TD> <TD>Meyer</TD> <TD>Danny</TD> <TD>12/1/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>1</TD> <TD>Meyer</TD> <TD>Danny</TD> <TD>12/1/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>&nbsp;</TD> <TD>Smith</TD> <TD>Rose</TD> <TD>6/1/2013</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>2</TD> <TD>Smith</TD> <TD>Rosa</TD> <TD>6/1/2013</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>3</TD> <TD>Quy</TD> <TD>Mary</TD> <TD>5/7/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>3</TD> <TD>Quy</TD> <TD>Mary</TD> <TD>5/7/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>4</TD> <TD>Martin</TD> <TD>Nia</TD> <TD>6/15/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>4</TD> <TD>Martin</TD> <TD>Nia</TD> <TD>6/15/2010</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>&nbsp;</TD> <TD>Varquiz</TD> <TD>Antonio</TD> <TD>2/24/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>5</TD> <TD>Varquiz</TD> <TD>Antonio</TD> <TD>3/24/2011</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> <TR> <TD>&nbsp;</TD> <TD>Howell</TD> <TD>Arianna</TD> <TD>3/14/2012</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>6</TD> <TD>Howell</TD> <TD>Arianne</TD> <TD>3/14/2012</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> <TD>&nbsp;</TD> </TR> </TBODY> </TABLE> Thu, 01 Apr 2021 19:25:23 GMT https://communities.sas.com/t5/SAS-Programming/matching-tables-and-filling-in-values/m-p/730823#M227623 Bintang18 2021-04-01T19:25:23Z