topic Re: Merge Multiple tables in SAS Programming

Merge Multiple tables

ybz12003 — Mon, 31 Jul 2017 14:50:33 GMT

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.

Re: Merge Multiple tables

Kurt_Bremser — Mon, 31 Jul 2017 14:33:07 GMT

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.

Re: Merge Multiple tables

Shmuel — Mon, 31 Jul 2017 15:11:04 GMT

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;

Re: Merge Multiple tables

ybz12003 — Mon, 31 Jul 2017 14:49:57 GMT

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)

Re: Merge Multiple tables

Kurt_Bremser — Mon, 31 Jul 2017 15:01:09 GMT

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

Re: Merge Multiple tables

ybz12003 — Mon, 31 Jul 2017 15:12:02 GMT

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?

Re: Merge Multiple tables

Shmuel — Mon, 31 Jul 2017 15:13:09 GMT

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

Re: Merge Multiple tables

Shmuel — Mon, 31 Jul 2017 15:16:21 GMT

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.

Re: Merge Multiple tables

Kurt_Bremser — Tue, 01 Aug 2017 06:05:46 GMT

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

Re: Merge Multiple tables

LinusH — Tue, 01 Aug 2017 06:14:22 GMT

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.