topic Re: Using Macros in SQL FROM Statement in SAS Programming

Using Macros in SQL FROM Statement

smg3141 — Fri, 13 Aug 2021 16:01:01 GMT

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!

Re: Using Macros in SQL FROM Statement

FreelanceReinh — Fri, 13 Aug 2021 16:48:26 GMT

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.

Re: Using Macros in SQL FROM Statement

smg3141 — Fri, 13 Aug 2021 16:52:46 GMT

Doh! What a rookie mistake. Thank you!

Re: Using Macros in SQL FROM Statement

Tom — Fri, 13 Aug 2021 17:03:40 GMT

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
;