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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.