topic Combining multiple tables into one in SAS Programming

Combining multiple tables into one

mtgkooks — Fri, 26 Mar 2021 08:41:32 GMT

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!

Re: Combining multiple tables into one

PeterClemmensen — Fri, 26 Mar 2021 08:53:09 GMT

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  .

Re: Combining multiple tables into one

mtgkooks — Fri, 26 Mar 2021 09:06:20 GMT

What would change if the separate tables were NOT sorted?

Re: Combining multiple tables into one

Kurt_Bremser — Fri, 26 Mar 2021 09:19:56 GMT

@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.