how to shorten a merge code of many tables?

[skipper]

How can I shorten this code? (my real code is much longer, i.e goes up to Z, the ZA, ZB, ... ect) An so I want to compress this code

%macro mymacro(A, B, C, D, E, F);

data want;

                     merge    &A.(in=inA  rename=(&A._dt=dt))         

                                  &B.(in=inB  rename=(&B._dt=dt))                   

                                  &C.(in=inC  rename=(&C._dt=dt))

                                  &D.(in=inE  rename=(&D._dt=dt))

                                  &E.(in=inF  rename=(&F._dt=dt))

                                  &F.(in=inG  rename=(&G._dt=dt))

  ;

                    by dt;

/**  other code here **/

%macro mymacro;

Something like

%macro mymacro(A, B, C, D, E, F);

data want;

                while var = A to F;

                     merge    &var.(in=invar  rename=(&var._dt=dt))         

  ;

                    by dt;

/**  other code here **/

%macro mymacro;

[Linlin]

you have to use capital letters where I used.

example:

data exclude;

  set sashelp.class;

run;

data a;

input a_dt age;

cards;

1 20

2 30

;

data b;

input b_dt wt;

cards;

1 20

2 30

;

data c;

input c_dt ht;

cards;

1 20

2 30

3 40

;

proc sql noprint;

  select memname into :dsn separated by ' '

    from dictionary.tables

   where libname='WORK' and memname not in ('EXCLUDE','WANT');

  quit;

  %put &dsn;

  %macro test;

  data want;

    merge

  %do i=1 %to %sysfunc(countw(&dsn));

  %let dd=%scan(&dsn,&i);

  &dd.(rename=(&dd._dt=dt) in=in&dd.)

  %end;;

  by dt;

   run;

   %mend test;

   %test

   proc print data=want;run;

                         Obs    dt    age    wt    ht

                            1      1     20    20    20

                            2      2     30    30    30

                            3      3      .     .    40

Linlin

[Tom]

If you want to pass an unspecified number of values to a macro then try to do it as a delimited list in a single parameter.

Then your macro can loop over the list.

In your case a space delimited list is a natural way to pass in the names of many datasets.

Here is a version that just handles the generation of the dataset names and options.

%macro merge_list(dslist);

%local i ds ;

%do i=1 %to %sysfunc(countw(&dslist,%str( )));

  %let ds=%scan(&dslist,&i,%str( ));

  &ds (in=in&ds rename=(&ds._dt=dt))

%end;

%mend merge_list;

* Example usage ;

data want ;

  merge %merge_list(A B C D E F G H);

  by dt;

* other code ;

run;

[Ksharp]

Using dictionary table.

Assuming all the tables are under WORK library.

proc sql noprint;
select catt(memname,'(in=in',memname,' rename=(',memname,'_dt=dt)) ')
       into : list separated by ' '
 from dictionary.members where libname='WORK';
quit;

%put &list;
data want;
 merge &list   ;
 by dt;
run;

Ksharp