topic PROC SQL passthrough versus direct call in SAS Procedures

PROC SQL passthrough versus direct call

mikeydubs23 — Wed, 11 Sep 2019 21:38:44 GMT

I have two identical data structures. One where it's most efficient to use the passthrough and another where I have to reach it directly. This scenario is going to apply to much of my programming in the future so I want to try to pull the data as efficently as possible and recycle the actual SQL for both.

My idea was to put the SQL statement in a macro and call the macro in both the proc sql for the passthrough and the regular proc sql using an insert. The issue is that the passthrough requires the statement to be enclosed in paretheses and the regualr proc sql statement will NOT allow paretheses at all. Seems stupid, but it's got me stuck.

Example:

%MACRO Get_table;
(SELECT &regno,

t1.Field1,
t1.Field2
from &source.thetablename t1
WHERE (t1.Field1 Between '0*' And '999'));
%Mend Get_table;

If I drop this into my passthrough it works great. If I drop it into something like:

%MACRO RUN_CODE;
PROC SQL FEEDBACK;
INSERT INTO WORK.LOCAL_thetablename
%Get_table;
QUIT;
%mend RUN_CODE;

I get an error because of the parentheses -

ERROR 22-322: Syntax error, expecting one of the following: ), ','.

ERROR 76-322: Syntax error, statement will be ignored.

How can I use the same SQL code in both a passthrough and regular proc SQL statement?

Re: PROC SQL passthrough versus direct call

SASKiwi — Wed, 11 Sep 2019 22:26:20 GMT

It would be helpful if you could explain what you are doing here. Are you loading SAS data into the external database or not? If you are then the following comments are relevant.

PASSTHRU mode cannot reference SAS data as everything is happening in your external database.

The easiest way to insert SAS data into an external dataset in my experience is PROC DATASETS using an APPEND statement using a database LIBNAME.

Re: PROC SQL passthrough versus direct call

mikeydubs23 — Thu, 12 Sep 2019 13:52:23 GMT

I am not loading data, only retrieving/pulling. What I'm doing is setting up a blank table in the work environment using a proc sql statement. Next I'm running a simple passthrough (Sybase) where I have all of the necessary parts of the passthrough except the actual SQL statement which I've place in a macro. I use the macro instead of the actual SQL statement. Since the macro contains the opening and closing parentheses, it works and inserts into the blank table.

Next, I run the direct proc sql:

PROC SQL FEEDBACK;
INSERT INTO WORK.LOCAL_thetablename
%Get_table;
QUIT;

and it fails because of the parentheses.

I pass the data-source via a macro.

The idea is to avoid writing the same SQL code twice in a program so that I (or a future developer) can be sure I'm leveraging or applying the same code to these separate databases.

I know it works when I remove the parentheses so my question is how can I accommodate both the passthrough and direct message if the passthrough requires the parentheses and the direct method breaks with parentheses.

Re: PROC SQL passthrough versus direct call

Tom — Thu, 12 Sep 2019 13:59:34 GMT

Huh? Don't put the ( ) in the text to be repeated.

%let sql_code = select ..... ;
proc sql;
* Just in SAS ;
&sql_code ;
* Push into database ;
connect to oracle ;
select * from connection to oracle
  (&sql_code)
;
quit;

But I think you will find that there are enough difference between SAS syntax and whatever flavor of SQL your external database is using that you will either want separate queries.

Or let SAS figure it out.

libname saslib 'my directory';
libname oralib oracle ... ;
proc sql;
select * from saslib.mydata ;
select * from oralib.mydata ;
quit;

Re: PROC SQL passthrough versus direct call

mikeydubs23 — Thu, 12 Sep 2019 14:02:30 GMT

I tried that, but maybe the issue is that I was putting the SQL code in a macro instead of a macro variable. I'll try that and see if that's more successful.

Re: PROC SQL passthrough versus direct call

Tom — Thu, 12 Sep 2019 14:05:53 GMT

@mikeydubs23 wrote:
I tried that, but maybe the issue is that I was putting the SQL code in a macro instead of a macro variable. I'll try that and see if that's more successful.

A macro should work. Just make sure the macro doesn't generate any semi-colons.

Re: PROC SQL passthrough versus direct call

mikeydubs23 — Thu, 12 Sep 2019 16:47:25 GMT

I did ultimately use the macro instead of the macro variable and the issue was that the trailing semi-colon that I thought was necessary after the sql statement but before the %mend statement was the problem. I thought the semi-colon was ending the statement not part of the SQL text but your last suggestion helped me realize that the semi-colon was being included.
Thansk