Macros and looping into Sql

[David_Billa]

I'm seeking for some help to convert the below macro program to proc SQL without any macros and Loop. Because I want to use this query in explicit pass through

/* Simple macro to print out the values of the macro variables created in step above */
%macro check() ;
     %do i=1 %to &tableCnt ;
          %put table&i = &&table&i;
Proc sql;
create table output as select t1.*,t2.reportingdate
from &&table&i t1 inner join 
Meta t2
on (t1.ID=t2.ID)
;
quit; 
     %end ;

%mend ;

/* Call check macro */
%check ;

Excepted code:

proc sql noprint;
           connect to SQLSVR (DATAsrc=&DATASRC. AUTHDOMAIN="&AUTHDOMAIN.");
Create table meta as SELECT * 
          FROM  CONNECTION TO SQLSVR 
                ( 
              <Query to be used once converting the macro program to sql>
                )
                
          ;           disconnect from SQLSVR;
quit;

[Kurt_Bremser · Accepted solution]

Whenever you want to take code a step further, FIRST make sure that the current iteration works.

Your current code won't work, as the dataset OUTPUT will contain the results of the last %DO loop only. All previous results will be lost.

For the correct syntax of SQL Server, I suggest you study the relevant Microsoft documentation, and have a talk with your SQL Server admins about the names of your tables there.

[Kurt_Bremser]

PS you do not have to convert the macro, you have to convert the SQL code.

[Amir]

Hi @David_Billa,

You might find that you can use macro code to loop through all of the tables with the pass through SQL as the macro code is resolved first to generate the text (SQL code). You'll still need to address issues, as highlighted by @Kurt_Bremser.

Kind regards,

Amir.