topic Re: HOW TO MERGE MULTIPLE DATABASES TO A COMMON DATABASE? in SAS Programming

HOW TO MERGE MULTIPLE DATABASES TO A COMMON DATABASE?

Aldo_Leal — Fri, 06 Sep 2019 20:18:55 GMT

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

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!!

Re: HOW TO MERGE MULTIPLE DATABASES TO A COMMON DATABASE?

ballardw — Fri, 06 Sep 2019 21:29:25 GMT

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.

Re: HOW TO MERGE MULTIPLE DATABASES TO A COMMON DATABASE?

Astounding — Fri, 06 Sep 2019 22:21:36 GMT

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?

Re: HOW TO MERGE MULTIPLE DATABASES TO A COMMON DATABASE?

Patrick — Sat, 07 Sep 2019 01:06:18 GMT

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;