| table1_base_EducDep | | | | | | |
| clientNumber | lname | fname | dob | address | city | county | |
| 1 | Meyer | Danny | 12/1/2010 | | | | |
| | Smith | Rose | 6/1/2013 | | | | |
| 3 | Quy | Mary | 5/7/2011 | | | | |
| 4 | Martin | Nia | 6/15/2010 | | | | |
| | Varquiz | Antonio | 2/24/2011 | | | | |
| | Howell | Arianna | 3/14/2012 | | | | |
| | | | | | | | |
| table2_DepofSocService | | | | | | |
| clientNumber | lname | fname | dob | address | city | county | dss_program |
| 1 | Meyer | Danny | 12/1/2010 | | | | |
| 2 | Smith | Rosa | 6/1/2013 | | | | |
| 3 | Quy | Mary | 5/7/2011 | | | | |
| 4 | Martin | Nia | 6/15/2010 | | | | |
| 5 | Varquiz | Antonio | 3/24/2011 | | | | |
| 6 | Howell | Arianne | 3/14/2012 | | | | |
I need to combine 2 tables above, has all six students WITH their client numbers and dss_program. 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.
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.
First round, using proc sql I pulled data from DSS on clientNumber=clientNumber.
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.
Is there a better way to do this?
Thank you all in advance, and hope this makes sense. I am using EG version 8.2
Click image to register for webinar
