Proc FedSQL and concatenated libraries

Patrick — Fri, 02 Jan 2015 03:00:50 GMT

Hi all

I've realized that with PROC FedSQL I can't use concatenated libraries (see code below). I couldn't find an explanation for this in the documentation. Does anybody know if this is just an implementation feature or if there is a specific reason for this.

My environment is: SAS 9.4 M2, Win X64_7PRO

Thanks

Patrick

/** create test data **/

options dlcreatedir;

libname test1 'c:\temp\test1';

data test1.class1;

set sashelp.class;

run;

libname test2 'c:\temp\test2';

data test2.class2;

set sashelp.class;

run;

/** Working: list data using Proc SQL and concatenated library **/

libname test (test1, test2);

proc sql;

title 'SQL class1, concatenated library';

select * from test.class1;

title 'SQL class2, concatenated library';

select * from test.class2;

quit;

/** Working: list data using Proc SQL and simple library **/

libname test1 'c:\temp\test1';

proc fedsql;

title 'FedSQL class1, simple library';

select * from test1.class1;

quit;

libname test2 'c:\temp\test2';

proc fedsql;

title 'FedSQL class2, simple library';

select * from test2.class2;

quit;

/** NOT Working: list data using Proc SQL and concatenated library **/

libname test (test1, test2);

proc fedsql;

title 'FedSQL class1, concatenated library';

select * from test.class1;

title 'FedSQL class2, concatenated library';

select * from test.class2;

quit;

Log:

NOTE: Libref TEST was successfully assigned as follows:

Levels: 2

Engine(1): V9

Physical Name(1): c:\temp\test1

Engine(2): V9

Physical Name(2): c:\temp\test2

57 proc fedsql;

NOTE: Connection string:

NOTE: DRIVER=FEDSQL;CONOPTS= ( (DRIVER=base;CATALOG=WORK;SCHEMA=

(NAME=WORK;PRIMARYPATH={C:\Users\ssapam\AppData\Local\Temp\SEG9188\SAS Temporary Files\_TD784_SSAPAM_\Prc2}));

(DRIVER=base;CATALOG=TEST2;SCHEMA= (NAME=TEST2;PRIMARYPATH={c:\temp\test2})); (DRIVER=base;CATALOG=TEST1;SCHEMA=

(NAME=TEST1;PRIMARYPATH={c:\temp\test1})); (DRIVER=base;CATALOG=MAPS;SCHEMA= (NAME=MAPS;PRIMARYPATH={C:\Program

Files\SASHome\SASFoundation\9.4\maps})); (DRIVER=base;CATALOG=MAPSSAS;SCHEMA= (NAME=MAPSSAS;PRIMARYPATH={C:\Program

Files\SASHome\SASFoundation\9.4\maps})); (DRIVER=base;CATALOG=MAPSGFK;SCHEMA= (NAME=MAPSGFK;PRIMARYPATH={C:\Program

Files\SASHome\SASFoundation\9.4\mapsgfk})); (DRIVER=base;CATALOG=SASUSER;SCHEMA=

(NAME=SASUSER;PRIMARYPATH={C:\Users\ssapam\Documents\My SAS Files\9.4})))

58 title 'FedSQL class1, concatenated library';

59 select * from test.class1;

ERROR: Table "TEST.CLASS1" does not exist or cannot be accessed

ERROR: BASE driver, schema name TEST was not found for this connection

NOTE: PROC FEDSQL has set option NOEXEC and will continue to prepare statements.

60 title 'FedSQL class2, concatenated library';

61 select * from test.class2;

ERROR: Table "TEST.CLASS2" does not exist or cannot be accessed

ERROR: BASE driver, schema name TEST was not found for this connection

62 quit;

Re: Proc FedSQL and concatenated libraries

jakarman — Fri, 02 Jan 2015 15:55:09 GMT

The behavior as concatenation is documented it can work marvelous but has limitations.

It is adviced as with conversions: Migration: Maintaining Files from Different Releases

With a libname the following applies: SAS(R) 9.3 Statements: Reference (rules concatenation)

Proc DS2 (designed for parallel processing) is having basic problems with concatenation. 51043 - Librefs with concatenated libraries are not supported in DS2 No, this one you will not find documented. SAS(R) 9.4 FedSQL Language Reference, Third Edition is based on multithreading etc to multiple data-sources.

For in databases processing the concatenation is a limitation SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition . That makes sense a concatenation is SAs environment specific. http://support.sas.com/rnd/migration/planning/files/mixed.html

Thinking on the implications it makes sense Fed-SQL will not access data like old classid data-step SQL approaches. By that a concatenation of libraries giving issues makes sense.

Still painful that is referring ANSI-SQL-1999 and not ANSI-SQL:2008 SQL:2008 - Wikipedia, the free encyclopedia (already 7 years old) SQL - Wikipedia, the free encyclopedia (several updates mentioned). Calling java methods is probably used with mining scoring.

topic Proc FedSQL and concatenated libraries in SAS Programming

Proc FedSQL and concatenated libraries

Re: Proc FedSQL and concatenated libraries