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
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
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
Reece,
Set the ANSIMODE option in the PROC FEDSQL statement. That should resolve the discrepancy.
Sylvia
Dear Sylvia,
what is the syntax for doing so? Would you mind to give an coding example? Thanks!
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.