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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.