topic Re: How do I merge two tables located on two different servers in SAS Enterprise Guide? in SAS Programming

How do I merge two tables located on two different servers in SAS Enterprise Guide?

BarryP — Wed, 30 Jan 2019 22:25:50 GMT

Hi,

in SAS Enterprise Guide I am required to select which server I am working in using the drop down at the top of the page. What if I wanted to merge tables from different servers?

If they are on the same server, I would select it and write:

Data Table_I_Want;

merge LIBID1.Table_I_Have1 LIBID2.Table_I_Have2;

by Common_Field;

run;

Thanks!

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

Reeza — Wed, 30 Jan 2019 23:18:31 GMT

You can’t in EG, but you can in Base SAS - with a caution, it downloads both of the data sets to do the merge so it’s not super efficient.

@BarryP wrote:

Hi,

in SAS Enterprise Guide I am required to select which server I am working in using the drop down at the top of the page. What if I wanted to merge tables from different servers?

If they are on the same server, I would select it and write:

Data Table_I_Want;

merge LIBID1.Table_I_Have1 LIBID2.Table_I_Have2;

by Common_Field;

run;

Thanks!

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

ChrisNZ — Wed, 30 Jan 2019 23:21:18 GMT

All the data needs to be on the same server to be merged.

So you must move data sets. There are several ways to do this, but this is an inescapable requirement.

Can you use SAS/Connect to connect from one server to the other?

It works like this:

choose SERVER1 from the dropdown menu in EG

and submit:

signon SERVER2;

This will make SERVER1 try to connect to SERVER2.

You need to supply the correct connection parameters.

Ask your administrator if SAS/Connect is available, and what the parameters are.

If not, you have to copy the data manually from one server to the other, either via Windows explorer or via EG (copy and paste). This can be very slow.

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

ChrisNZ — Wed, 30 Jan 2019 23:26:52 GMT

A third way would be to define a UNC libname.

For example run this on SERVER1:

libname SVR2 '\\SERVER2\path';

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

BarryP — Fri, 01 Feb 2019 02:25:09 GMT

Thanks for the help, but I still don't understand how I would do this.

If I'm on server1 and type:

set Library_Name.Table

Its going to assume that the library is on server1. How would I get it to server2? Or how would I copy from server2 into the work library of server1?

Right now the only thing I would know how to export the table from server2 and then use the import wizard to get it back into server1.

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

ChrisNZ — Fri, 01 Feb 2019 02:40:41 GMT

Like this?

libname SVR1 '\\SERVER1\path';
libname SVR2 '\\SERVER2\path';
data MERGED;
  merge SVR1.TAB1 
        SVR2.TAB2;
 run;

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

BarryP — Fri, 01 Feb 2019 05:27:29 GMT

Okay, I think I know what to do here. I'll try it out tomorrow and let you know how it goes.

When you say

libname SVR1 '\\SERVER1\path';

You simply mean the libname statement that I can look up in SAS Console Manager. Correct?

Thanks!

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

ChrisNZ — Sun, 03 Feb 2019 20:35:01 GMT

I don't know what you have in your Console manager.

This is the local library.

Re: How do I merge two tables located on two different servers in SAS Enterprise Guide?

Patrick — Sun, 03 Feb 2019 23:34:54 GMT

@BarryP

For merging/joining all the data must be on the same server (your Server1).

You can either find a way to issue libname statements which can access all the data you need - the libnames with UNC paths as proposed. That's the easiest way if it works.

If you have SAS/Connect licensed then you can also use remote library services. Such a library definition allows you to transparently access data via a remote SAS server (your Server2).

How this works is documented here (the SAS/Connect client in the docu is your Server1 to which you connect via SAS EG):

https://go.documentation.sas.com/?docsetId=connref&docsetTarget=p12j8b6w2nuxpon15xu0fqsisfkk.htm&docsetVersion=9.4&locale=en