BookmarkSubscribeRSS Feed
reecec
Fluorite | Level 6

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:

 

procfedsql.png

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

7 REPLIES 7
LinusH
Tourmaline | Level 20
If you think that you have a solid case for a sw problem, you should open a track to SAS tech support.
Data never sleeps
SylviaPowell
SAS Employee

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
Fluorite | Level 6

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
SAS Employee

Reece,

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

 

Sylvia 

SvenWagner
Fluorite | Level 6

Dear Sylvia, 

 

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

 

 

SylviaPowell
SAS Employee

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
Fluorite | Level 6

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1428 views
  • 0 likes
  • 4 in conversation