Using Macros in SQL FROM Statement

[smg3141]

Hello!

I'm attempting to do an inner join on multiple tables. This code is within a %DO loop with n as the variable. 

%let file1=datahave1;

%let file2=datahave2;

.

.

.

%let file10=datahave10;

%let numoffiles=10;

libname input "path";

%macro formatex;
%do n=1 %to &numoffiles;

proc sql;
create table name1&&file&n as
select *
from name2&&file&n a inner join input.&&file&n.._base b
on a.ssn = b.&pat_ssn and a.record_id= b.record_id
quit;
run;

%end;
%mend;
%formatex;

Right now it just runs the SQL step forever. I'm new to macros, so any help would be greatly appreciated!

[FreelanceReinh · Accepted solution]

Hello @smg3141,

---

@smg3141 wrote:

on a.ssn = b.&pat_ssn and a.record_id= b.record_id;
quit;

run;

Right now it just runs the SQL step forever.

---

This is because you missed the semicolon that I've inserted in red above.

The RUN statement can be deleted. PROC SQL doesn't need it.

[smg3141]

Doh! What a rookie mistake. Thank you!

[Tom]

When writing a single statement that spans multiple lines put the semi-colon that ends it on its own line.  Like the END in a DO/END block.

Then you are less likely to forget it or insert extra ones.

create table name1&&file&n as
  select *
  from name2&&file&n a
  inner join input.&&file&n.._base b
     on a.ssn = b.&pat_ssn
    and a.record_id= b.record_id
;