Erratic proc fedsql behaviour

[reecec]

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

[LinusH]

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

[SylviaPowell]

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

[reecec]

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

[SylviaPowell]

Reece,

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

Sylvia 

[SvenWagner]

Dear Sylvia, 

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

[SylviaPowell]

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.

[SvenWagner]

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.