left join multiple tables with macro into 1 output

[nesslee]

hello, i am beginner in macros.

I would like to left join multiple tables to another table using a macro. My macro is overwriting instead and I do not know how to prevent that.

so i would like to create a macro which will do this:

proc sql;

create table result2 as

select

t1.*,

t_sa_00.code as code_sa_00,

t_sa_01.code as code_sa_01,

...

from work.result1 as t1

left join work.t_sa_00 on t1.id=t_sa_00.id

left join work.t_sa_01 on t1.id=t_sa_01.id

...

;quit;

and my actual macro looks like this:

proc sql;

select distinct (memname), count(distinct memname)

into :tbl separated by ' ' , :ntbl

from dictionary.tables

where

libname='WORK'

and memname like 'T_SA_%'

order by memname;

quit;

%macro result2;

proc sql;

%DO i=1 %TO &ntbl.;

%LET item = %scan(&tbl., &i.);

CREATE TABLE RESULT2 AS (

SELECT

t1.*,

t2.code_sa

from work.RESULT1 as t1 LEFT JOIN &item. as t2

on t1.id=t2.id

);

%END;

quit;

%mend;

%result2

[ballardw]

it appears that you may want 2 loops

proc sql;

create table result2 as

select

t1.*,

/* one loop would go here to create the rename codes*/

t_sa_00.code as code_sa_00,

t_sa_01.code as code_sa_01,

...

/* and end here for the rename  not that the last one shouldn't end with a comma

   so it may work better to look at generating , t_sa_00.code as code_sa_00 lines*/

from work.result1 as t1

/* and the other parallel loop to go over the data set names*/

left join work.t_sa_00 on t1.id=t_sa_00.id

left join work.t_sa_01 on t1.id=t_sa_01.id

...

/* and the loop ends here*/

;quit;

You do not want the the Create table or start of the select inside the loop, that is what would overwrite your resulting sets. You should make sure that you have the non-macro coding work for at least 2 sets before attempting to code a macro (3 would be better)

Note that you can turn on system options Mprint and symbolgen to se what your macro code is actually doing.

Options mprint symbolgen;

%yourmacrocall

options nomprint nosymbolgen.

[nesslee]

hello, thank you for your reply.

I managed to rename the fields using the following, but i still have difficulty to figure out the loop over the several left joins..

%let name=%SUBSTR(&item,%LENGTH(&item)-1);

[Tom]

So you want to generate

proc sql;
create table result2 as
select t1.*
/*  One group */
     , t_sa_00.code as code_sa_00
     , t_sa_01.code as code_sa_01
...
from work.result1 as t1
/* Second group */
left join work.t_sa_00 on t1.id=t_sa_00.id
left join work.t_sa_01 on t1.id=t_sa_01.id
...
;
quit;

So your macro will need two loops.