topic Re: A Macro to merge data sets in SAS Programming

A Macro to merge data sets

Timbim — Wed, 06 Feb 2019 06:57:28 GMT

Hi Community,

I'm here seeking help for a macro to merge tables with similar table structures.

I've written the below code but I believe a macro would be easy.

Data Accounts_FY18;

set Account _201801

Account _201802

Account _201803

Account _201804

Account _201805

Account _201806

Account _201807

Account _201808

Account _201809

Account _201810

Account _201811

Account _201812;

run;

Appreciate your help.

Kind regards

Re: A Macro to merge data sets

andreas_lds — Wed, 06 Feb 2019 07:04:11 GMT

Well, i would be a good starter to discuss why those 12 datasets have been created at all, that seems to be bad design.

Now the good news: you don't need macro-code!

data Accounts_FY18;
    set Account_2018:;
run;

This causes all datasets named like "Account_2018*" to be appended.

Re: A Macro to merge data sets

RW9 — Wed, 06 Feb 2019 09:45:29 GMT

Just to add to @andreas_lds great answer, use correct terminology. You are not "merging" data, you are appending data. There is a big difference.

I would also agree strongly with the "why those 12 datasets", as this may also impact your processing. If these datasets are large, then you may need to use the append procedure rather than the datastep approach, example:

data a1;
  a=1;
run;
data a2;
  a=2;
run;
data a3;
  a=3;
run;

data _null_;
  set sashelp.vtable (where=(libname="WORK" and char(memname,1)="A"));
  if _n_=1 then call execute(cats('data want; set work.',memname,';run;'));
  else call execute(cats('proc append base=want data=work.',memname,';run;'));
run;

Re: A Macro to merge data sets

Ksharp — Wed, 06 Feb 2019 13:19:38 GMT

Data Accounts_FY18;

set Account _201801 - Account _201812;

run;