topic Calling FEDSQL from call execute loop in SAS Programming

Calling FEDSQL from call execute loop

andrea_magatti — Thu, 21 Jul 2022 15:42:43 GMT

Hi all,

a simple question:

while trying to run a proc fedsql inside a call execute loop i'm getting a strange error:

data _null_;
 a= "MD";
 set GNC.GNC_CAUSES_NO_EDGE_BACK(obs=1);
 call execute('proc fedsql sessref=EG_Session; select distinct id_remi  from GNC.REMI_VOL_2008_2021_FINAL  where id_class ='||a||'; quit;');
run;

BTW: the dataset live in a cas library

I'm getting this error:

NOTE: There were 1 observations read from the data set GNC.GNC_CAUSES_NO_EDGE_BACK.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

NOTE: CALL EXECUTE generated line.
1         + proc fedsql sessref=EG_Session; select distinct id_remi  from GNC.REMI_VOL_2008_2021_FINAL  where id_class =MD;
1         +                                                                                                                 quit;
ERROR: Column "MD" not found or cannot be accessed
ERROR: Azione interrotta a causa di errori.
ERROR: The FedSQL action was not successful.
NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.

NOTE: PROCEDURE FEDSQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

I've tested the same code using proc SQL, with no problem, but since the main table is huge i would like to use fedsql (much faster)

I'm on viya 3.5 executed from a local SAS session.

Re: Calling FEDSQL from call execute loop

ballardw — Thu, 21 Jul 2022 15:51:00 GMT

Pretty clear:

1         + proc fedsql sessref=EG_Session; select distinct id_remi  from GNC.REMI_VOL_2008_2021_FINAL  where id_class =MD;

No quotes around MD means SAS is using it as a variable not a literal value.

You want to generate

proc fedsql sessref=EG_Session; select distinct id_remi  from GNC.REMI_VOL_2008_2021_FINAL  where id_class ='MD';

which might be easiest with

 call execute('proc fedsql sessref=EG_Session; select distinct id_remi  from GNC.REMI_VOL_2008_2021_FINAL 
 where id_class ='|| quote(a) ||'; quit;');

Re: Calling FEDSQL from call execute loop

andrea_magatti — Thu, 21 Jul 2022 15:57:52 GMT

Even stranger now:

after using your suggestion, I'm getting the same error:

NOTE: CALL EXECUTE generated line.
1         + proc fedsql sessref=EG_Session; select distinct id_remi  from GNC.REMI_VOL_2008_2021_FINAL  where id_class ="MD";
1         +                                                                                                                   quit;
ERROR: Column "MD" not found or cannot be accessed
ERROR: Azione interrotta a causa di errori.
ERROR: The FedSQL action was not successful.

It still says that it can't find the column, despite the quote function as:

data _null_;
 a= "MD";
 set GNC.GNC_CAUSES_NO_EDGE_BACK(obs=1);
 call execute('proc fedsql sessref=EG_Session; select distinct id_remi  from GNC.REMI_VOL_2008_2021_FINAL  where id_class ='||quote(a)||'; quit;');
run;

Many thanks for considering my request.

Re: Calling FEDSQL from call execute loop

ballardw — Thu, 21 Jul 2022 16:22:29 GMT

Show a DIRECT call to fedsql that does work.

as in not generated by a data step, just the Proc fedsql submitted directly.

I am not familiar with all the stuff that gets involved with the connections. I am assuming the ID_class is a variable in the table GNC.REMI_VOL_2008_2021_FINAL. If not then I'm clueless at this point.

Re: Calling FEDSQL from call execute loop

Tom — Thu, 21 Jul 2022 16:36:03 GMT

PROC FEDSQL is probably treating "MD" as a variable reference and is expecting you to use single quotes for literals. In PROC SQL you have to request this behavior but perhaps it is the default in PROC FEDSQL.

Tell the QUOTE() function to use single quotes.

quote(a,"'")

Re: Calling FEDSQL from call execute loop

andrea_magatti — Thu, 21 Jul 2022 16:39:06 GMT

Thanks!

Working!