BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ybz12003
Rhodochrosite | Level 12

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
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.

View solution in original post

9 REPLIES 9
Kurt_Bremser
Super User
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
Garnet | Level 18

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
Rhodochrosite | Level 12

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
Super User

@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
Garnet | Level 18

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

ybz12003
Rhodochrosite | Level 12

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
Garnet | Level 18

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
Super User

@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
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 25145 views
  • 7 likes
  • 4 in conversation