Merge Multiple tables

[ybz12003]

Hello:

I have 21 tables and I would like to merge them all into one table. Table 1 includes Date, ID, and Sale record. Table 2 includes Date, ID, and Sale price. Table 3 includes ID, Sale regions.   I intend to create a final table only containing the unique column (sample list below). Please advise how to do it. Thanks.

Table 1 : Date, ID, Sale record, etc. (5 columns)

Table 2: Date, Sale price, etc. (11 columns)

Table 3: ID, Sale regions, etc. ( 16 columns)

Final merged table: Date, ID, Sale record, Sale price, Sale Regions, etc.

[Kurt_Bremser · Accepted solution]

proc sql;
create table want as
select
  a.date,
  a.id,
  a.sale_record,
  b.sale_price,
  c.sale_regions
from
  table1 a,
  table2 b,
  table3 c
where
  a.date = b.date and
  a.id = c.id
;
quit;

If that does not work as intended, post your three example tables in a data step form and an example for the expected result.

[Shmuel]

You can merge datasets either using sql or data steps:

proc sql;
    create table temp as 
    select a.*, b.price
    from table1 as a
    left join table2 as b
    on a.date = b.date;

    create table want as
     select a.* , b.region
     from temp as a
     left join table3 as b
    on a.ID = b.ID;
quit; 

if you use data step to merge, you need two steps equivalent to above with sorting tables 

by ID and/or DATE;

[ybz12003]

I apologize that I didn't state clearly.   My tables are more columns than I list below.

Table 1 : Date, ID, Sale record, etc. (5 columns)

Table 2: Date, Sale price, etc. (11 columns)

Table 3: ID, Sale regions, etc. ( 16 columns)

[Kurt_Bremser]

---

@ybz12003 wrote:

I apologize that I didn't state clearly.   My tables are more columns than I list below.

Table 1 : Date, ID, Sale record, etc. (5 columns)

Table 2: Date, Sale price, etc. (11 columns)

Table 3: ID, Sale regions, etc. ( 16 columns)

---

Then add the additional columns to the SQL code as needed.

[Shmuel]

You can change b.price and b.region to b.* in order to include all variables.

[ybz12003]

So, my actural table contains more columns and I have twenty-one tables.   Is there a way I could avoid looking through all of columns in each table and list all their column names in merging steps?

[Shmuel]

As you first post - not all tables have same and all ID (key) variables.

You can expand the sql to continue join more tables with similar logic to first 3 tables.

[Kurt_Bremser]

---

@ybz12003 wrote:

So, my actural table contains more columns and I have twenty-one tables.   Is there a way I could avoid looking through all of columns in each table and list all their column names in merging steps?

---

TANSTAAFL - There Ain't No Such Thing As A Free Lunch.

Will soon be Maxim 45.

Sometimes working as a SAS programmer (and creating clean, maintainable code) involves work. Tedious work, even. What a surprise.

Using the asterisk must be done with caution in order to deal with variables that are present in more than one input dataset.

[LinusH]

A) You could build a macro that extracts column names from SQL dictionary.columns or sashelp.vcolumn. This will be some overhead in work and probably not worth it if it's a one time operation.

B) Use a GUI tool that lets you drag and drop columns, like the query builder in Enterprise Guide.