Macro for one table join with multiple tables

[amandahe]

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

[andreas_lds]

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.

[ChrisNZ]

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;

[amandahe]

Hi Chris,

Thank you Chris, how can I get 10 separate tables after inner join with the PRODUCT table? 

[ChrisNZ]

Why use 10 tables rather than a where clause?

[Kurt_Bremser]

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.