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;
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.
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.
PS you do not have to convert the macro, you have to convert the SQL code.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.