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.
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.
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.
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;
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)
@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.
You can change b.price and b.region to b.* in order to include all variables.
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?
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.
@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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.