topic Re: how to shorten a merge code of many tables? in SAS Procedures

how to shorten a merge code of many tables?

skipper — Sat, 21 Jul 2012 11:59:39 GMT

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;

Re: how to shorten a merge code of many tables?

Linlin — Sat, 21 Jul 2012 12:54:29 GMT

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

Re: how to shorten a merge code of many tables?

Tom — Sun, 22 Jul 2012 20:10:27 GMT

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;

Re: how to shorten a merge code of many tables?

Ksharp — Mon, 23 Jul 2012 05:27:08 GMT

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