BookmarkSubscribeRSS Feed
Patrick
Opal | Level 21

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;

1 REPLY 1
jakarman
Barite | Level 11

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.    

---->-- ja karman --<-----

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!

How to Concatenate Values

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.

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
  • 1 reply
  • 1659 views
  • 2 likes
  • 2 in conversation