Hi, I have this data and some names did not align with each other. Just like table 1 and I would like to have the names align just like in table 2. Any help please?
Table 1:
FName | LName | Sex | Age | Income | City | FNameA | LNameA | SexA | AgeA | IncomeA | BOD |
John | Doe | M | 20 | 100 | KK | John | Doe |
| 100 | 100 | 121298 |
Adam | Apple | F | 21 | 300 | BC | Peter | Cat | F |
| 302 | 031288 |
John | Cloth | M | 22 | 400 | CU | Know | Know | M | 400 | 412 | 030695 |
Eric | Dog | F | 23 | 500 | PO | Bill | Table |
|
| 500 | 092287 |
Lin | Cat | M | 45 | 300 | YI | Lin | Cat | M | 300 | 300 | 013076 |
Know | Know | F | 46 | 201 | HM | John | Cloth | F |
| 201 | 070777 |
Bill | Table | F | 50 | 330 | VB | Adam | Apple |
| 330 | 330 | 090999 |
Peter | Cat | F |
| 302 | OO | Eric | Dog | F | 23 | 500 | 120388 |
Table 2:
FName | LName | Sex | Age | Income | City | FNameA | LNameA | SexA | AgeA | IncomeA | BOD |
John | Doe | M | 20 | 100 | KK | John | Doe |
| 100 | 100 | 121298 |
Adam | Apple | F | 21 | 300 | BC | Adam | Apple |
| 330 | 330 | 090999 |
John | Cloth | M | 22 | 400 | CU | John | Cloth | F |
| 201 | 070777 |
Eric | Dog | F | 23 | 500 | PO | Eric | Dog | F | 23 | 500 | PO |
Bill | Table | F | 50 | 330 | VB | Bill | Table |
|
| 500 | 092287 |
Lin | Cat | M | 45 | 300 | YI | Lin | Cat | M | 300 | 300 | 013076 |
Know | Know | F | 46 | 201 | HM | Know | Know | M | 400 | 412 | 030695 |
Peter | Cat | F |
| 302 | OO | Peter | Cat | F |
| 302 | 031288 |
Split table 1 in to table 1A (the first 6 columns) and table 1B (the next 6 columns).
Then do a join (or merge) of table 1A and table 1B by the person's first and last name.
UNTESTED CODE
proc sql;
create table want as select a.*,b.*
from table1(keep=fname--city) as a left join table1(keep=fnamea--bod) as b
on a.fname=b.fnamea and a.lname=b.lnamea;
quit;
What does "did not align" mean?
If you combined two data sets then go back a step and show us how you combined them.
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.