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
What does your desired result look like?
table1_base_EducDep | ||||||||||||||
2020DSS_clientNumber | lname | fname | dob | address | city | county | 2021DSS_clientNumber | 2021DSS_lname | 2021DSS_fname | 2021DSS_dob | 2021DSS_address | 2021DSS_city | 2021DSS_county | 2021DSS_program |
1 | Meyer | Danny | 12/1/2010 | 1 | Meyer | Danny | 12/1/2010 | |||||||
Smith | Rose | 6/1/2013 | 2 | Smith | Rosa | 6/1/2013 | ||||||||
3 | Quy | Mary | 5/7/2011 | 3 | Quy | Mary | 5/7/2011 | |||||||
4 | Martin | Nia | 6/15/2010 | 4 | Martin | Nia | 6/15/2010 | |||||||
Varquiz | Antonio | 2/24/2011 | 5 | Varquiz | Antonio | 3/24/2011 | ||||||||
Howell | Arianna | 3/14/2012 | 6 | Howell | Arianne | 3/14/2012 |
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.