topic Re: Macros and looping into Sql in SAS Programming

Macros and looping into Sql

David_Billa — Thu, 04 Mar 2021 06:42:30 GMT

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;

Re: Macros and looping into Sql

Kurt_Bremser — Thu, 04 Mar 2021 07:55:35 GMT

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.

Re: Macros and looping into Sql

Kurt_Bremser — Thu, 04 Mar 2021 07:56:43 GMT

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

Re: Macros and looping into Sql

Amir — Thu, 04 Mar 2021 10:30:59 GMT

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.