HOW TO MERGE MULTIPLE DATABASES TO A COMMON DATABASE?

[Aldo_Leal]

Hello!

I've beeing build several databases in a macro as follows:

FUTURE_ORD_201601

FUTURE_ORD_201602

FUTURE_ORD_201603

               .

               .

               .

FUTURE_ORD_201903

I have created a table (ACCOUNT_NUMBER) that contains all the account numbers that are in common (it will be the key to be merged)

ACCOUNT_NUMBER

1

2

3

.

.

.

1894039183298219219

I'm mergin all the datasets as follows

DATA MERGE;

MERGE ACCOUNT_NUMBER(IN=A);

              FUTURE_ORD_:        (IN=B);

BY ACCOUNT_NUMBER;

IF A;

RUN;

I wonder if I can include someway the FUTURE databases into the macre where is created in order to merge the FUTURE database in every single step and let fix the ACCOUNT_NUMBER database as pivot for the merging.

This is what I've beeing doing so far:

proc sql;
create table future as
account_number from DATABASE;
RUN;

%MACRO FUTURE(VAR1,VAR2,VAR3);
creation of the database;
order of every single database created into the macro with the name FUTURE_ORD_&DATE.
%MEND;

DATA MERGE;
MERGE future (IN=A);
FUTURE_ORD_;(IN=C);
by account_number;
if A;
run;

I want to make the merge inside the macro and be sure that in every single step the merge is beeing doing in propper order.

Thanks in regards!!

[ballardw]

I would really like to see the log of submitting this code if it is actually accomplishing what you want.

DATA MERGE;
MERGE future (IN=A);
FUTURE_ORD_;(IN=C);
by account_number;
if A;
run;

I suspect that you have two too many semicolons in that code.

[Astounding]

So you have one merge that works, with no macro language.

Why would you want to replace that with dozens of merges, while adding the complexities of macro language?

[Patrick]

The merge statement you've posted can't work like @ballardw is hinting. Working code needs a semicolon removed and could look like:

data merge;
  merge account_number(in=a) future_ord_: ;
  by account_number;
  if a;
run;

I believe though you don't really want to merge the data but you want to stack all the rows with a matching account number to your reference table with account numbers. If so then code as below should do the job.

data stacked;
  if _n_=1 then
    do;
      dcl hash h1(dataset:'account_number(keep=account_number)');
      h1.definekey('account_number');
      h1.definedata('account_number');
      h1.definedone();
    end;
  set future_ord_: ;
  if h1.check()=0;
run;