topic Re: How to join tables from different SAS servers in SAS Programming

How to join tables from different SAS servers

PericoPalotes — Tue, 22 Jan 2019 13:41:15 GMT

Hi!

I've a two server SAS infrastructure and I want to join two tables from different servers.

I've Server_A with an example table named PAYLIST_A inside the folder /data/PROCLIB

and I've Server_B with an example table named PAYLIST_B inside the folder /data/ORIOLIB

If I try to join the tables using the query builder, what I get is:

%_eg_conditional_dropds(WORK.QUERY_FOR_PAYLIST);

PROC SQL;
   CREATE TABLE WORK.QUERY_FOR_PAYLIST AS 
   SELECT t1.IdNum, 
          t1.Gender, 
          t1.Jobcode, 
          t1.Salary, 
          t1.Birth, 
          t1.Hired, 
          t2.IdNum AS IdNum1, 
          t2.Gender AS Gender1, 
          t2.Jobcode AS Jobcode1, 
          t2.Salary AS Salary1, 
          t2.Birth AS Birth1, 
          t2.Hired AS Hired1
      FROM PROCLIB.PAYLIST_A t1
           INNER JOIN WORK.PAYLIST_B t2 ON (t1.IdNum = t2.IdNum);
QUIT;

So what Query Builder does, is to download PAYLIST_B from server B, and save it in server A WORK, but if I try to run this code without previously Query Builder download the PAYLIST_B table, it does not work. It simply says

ERROR: File WORK.PAYLIST_B t2 does not exist.

How can I join to tables in different servers in a program without using the query builder?

Re: How to join tables from different SAS servers

VDD — Tue, 22 Jan 2019 13:59:34 GMT

@PericoPalotes you say that Server_B with an example table named PAYLIST_B.

Server_B is not the same as work.

have you tried changing this WORK.PAYLIST_B t2 to this Server_B.PAYLIST_B t2

Re: How to join tables from different SAS servers

PericoPalotes — Tue, 22 Jan 2019 15:48:42 GMT

Hi @VDD , thanks for your response.

Unfortunatelly, this does not work, I'm running the program in the SERVER_A, and if I change the WORK to the name of the library ORIOLIB in SERVER_B, it simply says:

42               FROM PROCLIB.PAYLIST t1
43                    INNER JOIN ORIOLIB.PAYLIST t2 ON (t1.IdNum = t2.IdNum);
ERROR: File ORIOLIB.PAYLIST.DATA does not exist.

If I try something like SEVER_B.ORIOLIB.PAYLIST it says:

42               FROM PROCLIB.PAYLIST t1
43                    INNER JOIN SERVER_B.ORIOLIB.PAYLIST t2 ON (t1.IdNum = t2.IdNum);
                                                 _
                                                 22
                                                 76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, AS, ON.  

ERROR 76-322: Syntax error, statement will be ignored.

Re: How to join tables from different SAS servers

ballardw — Tue, 22 Jan 2019 17:54:38 GMT

I would say "Show use your LIBNAME statements". Especially for your ORIOLIB.

Likely one of those libname statements needs a UNC or similar address to tell your SAS session which server is handling requests.

If your libname does not reference the other server than likely your SAS session is assuming that the library is "local" or on the default server.

Re: How to join tables from different SAS servers

PericoPalotes — Wed, 23 Jan 2019 10:23:49 GMT

Hi @ballardw

Thanks for your response. I've tried to reference to the other server with LIBNAME but I get the following error:

27         
28         libname rorilib slibref=ORILIB server=SERVER_B;
ERROR: Attempt to connect to server SERVER_B failed.
ERROR: A communication subsystem partner link setup request failure has occurred.
ERROR: Cannot find TCP service 'server_b'.
ERROR: Error in the LIBNAME statement.

Re: How to join tables from different SAS servers

ballardw — Wed, 23 Jan 2019 18:26:48 GMT

@PericoPalotes wrote:

Hi @ballardw

Thanks for your response. I've tried to reference to the other server with LIBNAME but I get the following error:
27         
28         libname rorilib slibref=ORILIB server=SERVER_B;
ERROR: Attempt to connect to server SERVER_B failed.
ERROR: A communication subsystem partner link setup request failure has occurred.
ERROR: Cannot find TCP service 'server_b'.
ERROR: Error in the LIBNAME statement.

Network communications is not my strength but perhaps you have a SAS admin to talk to about how to connect to your different servers. Perhaps you need to provide some credentials such as username and/or password. By any chance is the ORILIB actually an Oracle database? You may need SAS/Access for Oracle installed and configured in that case and the libname may look more like

libname myoralib oracle user=myusr1 password=mypwd1 path='mysrv1';

Re: How to join tables from different SAS servers

VDD — Wed, 23 Jan 2019 18:39:05 GMT

do you also need to have schema=schema_to_the_DB for Oracle

Re: How to join tables from different SAS servers libname rorilib slibref=ORILIB server=SERVER_B;

ChrisNZ — Thu, 24 Jan 2019 02:23:20 GMT

>So what Query Builder does, is to download PAYLIST_B from server B, and save it in server A WORK,

Your code runs in SERVER_A, so all the data must be available to SERVER_A.

Four options:

1- Download it to the WORK library on SERVER_A using SAS/CONNECT and proc download.

This seems to be the current working option.

2- Point to the remote path.

Under Windows this is done using SMB to create a UNC path Your libname will be something like

libname ORIOLIB '\\SERVER_B\folder\data\ORIOLIB';

Under unix you can use NFS. See your unix admin.

3- Use SAS/CONNECT to create RLS libraries.

The syntax that you gave is correct:

libname rorilib slibref=ORILIB server=SERVER_B;

The error you are showing points to your trying to connect using the wrong server name.

How does Query Builder connect?

4- Transfer the file outside of SAS, for example using FTP.

Note that all methods require downloading the table to SERVER_A for local processing. So you won't save any time there.