Hello,
I have one table which is a fixed table has 4 columns, ID, date, hour, product, the table name as Product
and I have another 10 tables, which has more columns, ID, date, hour, product, category, subcategory, region. And the 10 tables name as YEAR2021_WW01, YEAR2021_WW02, YEAR2021_WW03...YEAR2020_WW02.
I would like these 10 tables (YEAR2021_WW01...) inner join with Product on a.id=b.id and a.date=b.date and a.hour=b.hour.
and i want to keep all the columns from table a, and one column "product" from table PODUCT, then output the 10 tables with the same table name(YEAR2021_WW01, YEAR2021_WW02, YEAR2021_WW03...YEAR2020_WW02).
I write a proc sql just for joining one table,
proc sql;
create table YEAR2021_WW01 as
select a*, b.Product
from YEAR2021_WW01 a inner join PRODUCT b on (a.id=b.id and a.date=b.date and a.hour=b.hour)
quit;
How to write a macro to join the 10 tables at once?
PRODUCT
ID | DATE | HOUR | PRODUCT |
1001 | 01JAN2021 | 8 | CAMBRIDGE |
1002 | 15JAN2021 | 16 | SUMMERSET |
YEAR2021_WW01
ID | DATE | HOUR | STATUS | MOOD | REGION | CATEGORY | SUBCATEGORY |
1001 | 01JAN2021 | 8 | NORMAL | TRUE | PARIS | A | AA |
1002 | 15JAN2021 | 16 | NORMAL | TRUE | PARIS | B | BB |
Assuming that the year-tables have the same variables. this looks like suboptimal data-design, so i would append them into one table (or view). Afterwards you need only one merge. If you really want to write a macro, start with working code to do one merge and you should not use proc sql, but a data step to avoid the warning:
WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity problem.
Note: Overwriting an existing dataset during merge etc. is a bad idea, because you can't repeat the step if something goes wrong, you have to repeat all steps necessary to re-create the overwritten dataset.
No macro needed. Something like
proc sql;
create table WANT as
select a.*, b.PRODUCT
from (select * from YEAR2021_WW01
union
....
select * from YEAR2021_WW10) a
inner join PRODUCT b
on (a.ID=b.ID and a.DATE=b.DATE and a.HOUR=b.HOUR);
quit;
Hi Chris,
Thank you Chris, how can I get 10 separate tables after inner join with the PRODUCT table?
Why use 10 tables rather than a where clause?
So you do a basic lookup on three keys to get the name of product; Do this with a hash object:
data product;
input ID $ DATE :date9. HOUR PRODUCT :$10.;
format date yymmdd10.;
datalines;
1001 01JAN2021 8 CAMBRIDGE
1002 15JAN2021 16 SUMMERSET
;
data YEAR2021_WW01;
input ID $ DATE :date9. HOUR STATUS $ MOOD $ REGION $ CATEGORY $ SUBCATEGORY $;
format date yymmdd10.;
datalines;
1001 01JAN2021 8 NORMAL TRUE PARIS A AA
1002 15JAN2021 16 NORMAL TRUE PARIS B BB
;
data want;
set YEAR2021_WW01;
if _n_ = 1
then do;
length product $10;
declare hash p (dataset:"product");
p.definekey("ID","DATE","HOUR");
p.definedata("product");
p.definedone();
call missing(product);
end;
if p.find() = 0;
run;
You simply add all table names to the SET statement.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.