Converting macro program to explicit pass through

[David_Billa]

I've the below SAS macro code which is working.fine, Now I want to convert this macro to explicit pass though. But I'm not certain how to achive the do Loop in explicit pass through. Any help?

/* 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 ;

Code which I look for something like below.

proc sql noprint;
           connect to SQLSVR (DATAsrc=&DATASRC. AUTHDOMAIN="&AUTHDOMAIN.");
Create table meta as SELECT * 
          FROM  CONNECTION TO SQLSVR 
                ( 
              
select t1.*,t2.reportingdate
from <table_name> t1 inner join /*how to dynamically call the table name in explicit pass through*/
Meta_data t2
on (t1.ID=t2.ID)
;
               )
                
          ;           disconnect from SQLSVR;
quit;

[Tom]

Macro code is just text replacement.  So if your example macro works now  it should work the same when pushed into the remote database.  Just expand the macro variable where you want to insert the dynamically generated text into your code.

The only difference is that you need to make sure the values of the macro variable generate the proper syntax for referencing that table in the remote database.  You can figure out that syntax by playing with your second code block until you get it to work for one and see how the table references need to look.

[David_Billa]

@Tom I don't understand  the Phrase 'Just expand the macro variable where you want to insert the dynamically generated text into your code'

May I request you to help me wrap the code with the use of the program which I provided in inital post?

[Tom · Accepted solution]

When you reference a macro using the & macro trigger the value of the macro variable being referenced replaces the reference and the resulting text is passed from the macro processor onto SAS to evaluate at as actual SAS code.  Thus you have expanded the macro variable reference into the actual value of the macro variable.

Your current code has this snippet:

from &&table&i t1 inner join 

Your proposed code has this snippet:

from <table_name> t1 inner join 

They are the same thing.  So just replace <table_name> with &&table&i

Now for the original program to work the value of the macro variables TABLE1 , TABLE2, etc (whose value you have not shown in your original code) needs to work when referencing a SAS dataset since you are running it as SAS code.  In the new program you want to run that code in your remote SQLSRV database.  So the value of TABLE1 needs to be syntax that SQLSRV uses to reference a table.  It might be the exact same way that SAS references the table.  Once you figure it out, if the vlaues of TABLE1 etc need to change then change the code that is creating those values to generate the value you need. Or add a step in your macro to convert the value of &&TABLE&i into the syntax you need in SQLSRV.

[David_Billa]

I got it. Thank you. May I request you to guide me with the document which
convert the value of &&TABLE&i into the syntax which I need in SQLSRV

I did searched but I'm not finding the required documents.

[Tom]

We don't know what the tables in your SQL Server database are called.  In general they follow a similar schema.table syntax that will look like what you are used with SAS's libref.memname syntax.  One thing to watch out for is how to handle tables with non-standard names.  SQL server using square brackets..

Note if you are trying to have your SQL Server passthrough code reference SAS datasets on your SAS server that is not going to be possible.

[David_Billa]

SQL server tables in our server are very similar to SAS dataset. It's
pretty much same.

I'm looking for some references or documents which you mentioned in the
first paragraph of your previous post

[Reeza]

https://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=mcrolref&docsetTarget=n0vl7e3d...

I think this is what you're looking for here. Personally, this is exactly why I use CALL EXECUTE and don't use loops unless absolutely required. Not worth the extra testing and overhead.

More specifically, it's under References macro variables indirectly>Using more than two ampersands but the whole section is likely useful.

[ballardw]

To add a bit to @Reeza's comment on Call Execute.

Anything you place into a Call execute could be PUT to text file for verification. Or to a Program File to call with %include.

Unless I am sending very trivial bits in a call execute call I make a value of string variable and then use:

Call execute (string);

The data step concatenation functions and such are much less likely to cause issues than getting the correct number of ampersands in an indirect reference. The first time you try to get something working that looks like (made up example)

&&&&&&this&&&that.&&some&bit you'll know what I mean.

Where the string has all the needed elements quotes, semicolons or what have you.

So adding a

Put string;

with a File statement in the data step collects all the code. Which may be very helpful with a logic error in call execute crashes something. The text file would still exist - remember all the Call execute code is in a stack that executes after the data step ends.

[David_Billa]

Thank you . I'm not seeing any call execute in the document which you
mentioned.