Explicit pass through debug options

[David_Billa]

Can someone help me with the system automatic macro variable and options to see how the execuetion happening with explicit pass through in the code below? This would help us to determine the reason during any error.

/*Macro to Integrate data*/
%macro generic_extraction;
Proc sql noprint;
           Connect to SQLSVR (DATAsrc=&DATA. AUTHDOMAIN="&AUTH.");

Create table meta_data as SELECT * 
          FROM  CONNECTION TO SQLSVR 
                ( 
Declare @SQL nvarchar(max) = ''

Select @SQL = @SQL 
<queries>

%if "&function."="LT" %then %do;
where SUBSTRING(dataname, 0, CHARINDEX('_', dataname)) in ('ID','ID')
%end;

%if "&function."="AP" %then %do;
where substring(dataname,1,3)='AP'
%end;

Select @SQL
EXEC(@SQL)
                )
                
          ;   Disconnect from SQLSVR;
Quit;

/*looking for system automatic variable to put the error message in the log*/
%put &sysrc.;

%mend;

/*Macro Execution*/
%generic_extraction; 

[Tom]

So you are using a SAS macro to dynamically generate some code to run in a remote database that dynamically generates code to run in the remote database?

How big is the code that is being generated in the remote database?  Why not just pull down the information needed to generate the code to your local SAS session and generate the code there to submit to the remote database?

[David_Billa]

My code is very small, not more than 20 lines.

I want to execute some SQL Server queries via explicit pass through and I
want to debug if there is any errors or warnings. Right now without debug
options it's very difficult find where the issue is.

Also I would like to know if there is any automatic variable which I can
use in my program to get the return code.

[Tom]

PROC SQL will set some automatic macro variables with error codes. I am not sure how helpful they are in debugging the actual errors.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=sqlproc&docsetTarget=p0xl...

Check out SQLXRC and SQLXMSG.

You will have much easier time debugging if the SQL you are submitting to run is a simple query and not the dynamic syntax suggested by the snippet in your first example.

create table want as
  select * from connection to SQLSRV
( select a,b,c
  from myschema.mytable
);
%put &=sqlxrc SQLXMSG=%superq(sqlxmsg);

If you really need to run something complex in the database then you might need to do that via EXECUTE instead of CONNECTION TO. In which case you will then need to run a separate step to pull the results from where ever the code you execute in the remote database stored them.

[David_Billa]

Thanks. Don't we have any options like symbolgen, mlogic,.. for debugging
SQL queries with explicit pass through?

How to handle the debugging when working with dynamic syntax via pass through?

[SASKiwi]

You can't have multiple selects and other extraneous SQL Server statements in a SAS SELECT passthru statement. There is no way SAS can make sense of any data coming back from that. If you want to run a whole bunch of SQL Server statements then you have to do that in a SAS EXECUTE statement.

As a general rule a SAS SELECT passthru statement must only contain ONE SQL Server query producing one result set.

Try this:

%macro generic_extraction;
Proc sql noprint;
           Connect to SQLSVR (DATAsrc=&DATA. AUTHDOMAIN="&AUTH.");


  execute ( 
Declare @SQL nvarchar(max) = ''

Select @SQL = @SQL 
<queries>

%if "&function."="LT" %then %do;
where SUBSTRING(dataname, 0, CHARINDEX('_', dataname)) in ('ID','ID')
%end;

%if "&function."="AP" %then %do;
where substring(dataname,1,3)='AP'
%end;

Select @SQL
EXEC(@SQL)
                ) by SQLSVR
 ;   
Disconnect from SQLSVR;
Quit;

/*looking for system automatic variable to put the error message in the log*/
%put &sysrc.;

%mend;

[David_Billa]

I got it. Thank you. I see that you have used EXECUTE. How to debug this code?

[SASKiwi]

Get the SQL Server code working in SQL Server Management Studio first. Once it is working OK there then copy it into your SAS passthru EXECUTE statement. 

[David_Billa]

Ok, no need of any debug options or statements to debug in case of any
errors or warnings?

[SASKiwi]

Only the usual passing on of cryptic error messages from other databases which you get by default anyway in a SAS error message.

I find most database error messages aren't particularly helpful and it is better just to start with a really simple query that works, then add more statements one by one testing each change.