topic Re: Erratic proc fedsql behaviour in SAS Procedures

Erratic proc fedsql behaviour

reecec — Tue, 11 Apr 2017 23:22:06 GMT

Hi,

I am using SAS 9.4 and have been experimenting with PROC FEDSQL and have been experiencing some weird results as shown below in the log:

As you can see everytime I run it I get a different result (I manually checked the dataset and the rowcount does match the log so that part is consistent). Changing the code back to using proc sql I get the expected row count every time it is run.

Does anyone know why fedsql is giving random results? The input datasets are static and have never changed.

Many thanks,

Reece

Re: Erratic proc fedsql behaviour

LinusH — Wed, 12 Apr 2017 15:35:38 GMT

If you think that you have a solid case for a sw problem, you should open a track to SAS tech support.

Re: Erratic proc fedsql behaviour

SylviaPowell — Tue, 16 May 2017 18:57:44 GMT

Hi Reece,

I will follow up on this. What data source is the LSPMB library accessing? Also what SAS 9.4 release and platform are you using?

Sylvia

Re: Erratic proc fedsql behaviour

reecec — Tue, 16 May 2017 23:14:33 GMT

Hi Sylvia,

LSPMB is given by: LIBNAME LSPmb SPDE "/data/SPDE/M_B_PNCHPI" partsize=1028;

%PUT sys-ver = &sysvlong and OS = &SYSSCP &SYSSCPL;

sys-ver = 9.04.01M3P062415 and OS = LIN X64 Linux

Many thanks,

Reece

Re: Erratic proc fedsql behaviour

SylviaPowell — Wed, 17 May 2017 14:38:20 GMT

Reece,

Set the ANSIMODE option in the PROC FEDSQL statement. That should resolve the discrepancy.

Sylvia

Re: Erratic proc fedsql behaviour

SvenWagner — Tue, 09 Oct 2018 06:14:23 GMT

Dear Sylvia,

what is the syntax for doing so? Would you mind to give an coding example? Thanks!

Re: Erratic proc fedsql behaviour

SylviaPowell — Tue, 09 Oct 2018 17:00:48 GMT

Simply add the keyword to the PROC FEDSQL statement:

proc fedsql ansimode;

When the underyling driver is SPD Engine, using ANSIMODE changes the location where the WHERE clause is processed. Normally, the driver processes the request. When ANSIMODE is specified, because SPD Engine uses SAS missing values, the driver rejects any WHERE clause, and the FedSQL driver handles the WHERE clause instead.

If this doesn't work, please file a software problem report.

Re: Erratic proc fedsql behaviour

SvenWagner — Wed, 10 Oct 2018 06:19:14 GMT

Thanks, but unfortunately, your proposal does not fix the problem in my case. Please, would you take a look at my post https://communities.sas.com/t5/SAS-Procedures/PROC-FedSQL-with-stochastic-results/td-p/501773 ? The problem is the same with und without using SPDE.