DATA Step, Macro, Functions and more

Merge Multiple tables

Accepted Solution Solved
Reply
Super Contributor
Posts: 319
Accepted Solution

Merge Multiple tables

[ Edited ]

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.


Accepted Solutions
Solution
‎10-13-2017 01:56 PM
Super User
Posts: 7,782

Re: Merge Multiple tables

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎10-13-2017 01:56 PM
Super User
Posts: 7,782

Re: Merge Multiple tables

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,566

Re: Merge Multiple tables

[ Edited ]

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;

 

 

Super Contributor
Posts: 319

Re: Merge Multiple tables

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)

Super User
Posts: 7,782

Re: Merge Multiple tables


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,566

Re: Merge Multiple tables

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

Super Contributor
Posts: 319

Re: Merge Multiple tables

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?

Trusted Advisor
Posts: 1,566

Re: Merge Multiple tables

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.

Super User
Posts: 7,782

Re: Merge Multiple tables


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 5,429

Re: Merge Multiple tables

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.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 324 views
  • 2 likes
  • 4 in conversation