topic Re: left join multiple tables with macro into 1 output in SAS Programming

left join multiple tables with macro into 1 output

nesslee — Tue, 04 Apr 2017 20:48:05 GMT

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

Re: left join multiple tables with macro into 1 output

ballardw — Tue, 04 Apr 2017 21:01:29 GMT

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.

Re: left join multiple tables with macro into 1 output

nesslee — Wed, 05 Apr 2017 09:02:20 GMT

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);

Re: left join multiple tables with macro into 1 output

Tom — Wed, 05 Apr 2017 12:58:28 GMT

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.