Combining multiple tables into one

[mtgkooks]

I have multiple SAS datasets, all of which I need to combine into one, including each table's unique variables. For example:

Table 1:

Yr/Mo         FName          LName          Amount

202102       Angela           Apple             $150.00

202102       Edward          Engles           $200.00

Table 2:

Yr/Mo         FName          LName          DOB

202102       Angela           Apple             08/09/1970

202102       Charles         Carter             11/15/1945

Table 3:

Yr/Mo         FName          LName         Gender

202102       Brian             Berry             Male

202102       Charles         Carter            Male

202102       Doris             Dixon             Female

I need the resultant table to look like this:

Yr/Mo          FName          LName          Amount          DOB              Gender

202102        Angela           Apple             $150.00          08/09/1970

202102        Brian              Berry                                                          Male          

202102        Charles          Carter                                   11/15/1945    Male

202102        Doris              Dixon                                                          Female

202102        Edward          Engles           $200.00

Any assistance you can provide would be most welcome. Thanks!

[PeterClemmensen · Accepted solution]

Since your data is sorted, you can simply merge the tables like this

data table1;
input YrMo $ FName $ LName $ Amount :dollar8.2;
format Amount dollar8.2;
datalines;
202102 Angela Apple  $150.00
202102 Edward Engles $200.00
;

data table2;
input YrMo $ FName $ LName $ DOB :mmddyy10.;
format DOB mmddyy10.;
datalines;
202102 Angela  Apple  08/09/1970
202102 Charles Carter 11/15/1945
;

data table3;
input YrMo $ FName $ LName $ Gender $;
datalines;
202102 Brian   Berry  Male
202102 Charles Carter Male
202102 Doris   Dixon  Female
;

data want;
   merge table1 table2 table3;
   by YrMo FName LName;
run;

Result:

YrMo    FName    LName   Amount   DOB         Gender  
202102  Angela   Apple   $150.00  08/09/1970          
202102  Brian    Berry   .        .           Male    
202102  Charles  Carter  .        11/15/1945  Male    
202102  Doris    Dixon   . .                  Female  
202102  Edward   Engles  $200.00  .                   

[mtgkooks]

What would change if the separate tables were NOT sorted?

[Kurt_Bremser]

---

@mtgkooks wrote:

What would change if the separate tables were NOT sorted?

---

You would have to sort first, or use a FULL JOIN in PROC SQL.

BTW having a period date in a lookup table for date-of-birth or gender seems to be redundant.