topic Re: Macro for one table join with multiple tables in SAS Programming

Macro for one table join with multiple tables

amandahe — Tue, 29 Jun 2021 03:33:42 GMT

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

Re: Macro for one table join with multiple tables

andreas_lds — Tue, 29 Jun 2021 04:49:54 GMT

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.

Re: Macro for one table join with multiple tables

ChrisNZ — Tue, 29 Jun 2021 05:14:22 GMT

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;

Re: Macro for one table join with multiple tables

amandahe — Tue, 29 Jun 2021 07:21:08 GMT

Hi Chris,

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

Re: Macro for one table join with multiple tables

Kurt_Bremser — Tue, 29 Jun 2021 07:40:12 GMT

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.

Re: Macro for one table join with multiple tables

ChrisNZ — Tue, 29 Jun 2021 10:31:14 GMT

Why use 10 tables rather than a where clause?