topic Re: Can I run a proc compare on data across two separate remote servers in BASE SAS 9.4 or SAS EG 7. in New SAS User

Can I run a proc compare on data across two separate remote servers in BASE SAS 9.4 or SAS EG 7.1?

ZolaGola99 — Wed, 03 Jul 2019 07:19:35 GMT

Hello SASSY SAS USERS,

I'm wondering if it's possible to be able to run a proc compare across two servers?

By that I mean -

BASE=RemoteServer1.lib.dataset

Compare = RemoteServer2.lib.dataset

I don't really care which server I actually run the proc compare in (local, RS1 or RS2) but I just need to know if it can be done and if so what is the correct rsubmit syntax to do this?

Or can I create a proc sql join across two servers in an rsubmit to do my comparison?

For example below is the script I have built so far in BASE SAS 9.4 to connect to the two servers. The rsubmit SQL join at the end is where I am having trouble as I don't know what is the right rsubmit syntax to use to get it working? Is it possible?

NOTE - I don't want to copy server1 data to server2 data or vice versa as they are very large datasets and there is really no space allocated to me on either server to do this anyway.

Thanks

/*LOGON TO REMOTE SERVER 1*/
signoff remote=SRV1;
signon remote=SRV1 user="XXXX" password="XXXX";
/* ASSIGN Remote SERVER1 WORK DIR */
libname S1WORK slibref=work server=SRV1;

/*assign the libnames where the datasets you want to run the proc compares on */
/*SRV1 LIBNAMES*/
%macro MACRO_ASSIGN_LIB (NAME=, filepath=);
%syslput name = &name.;
%syslput filepath = &filepath.;
libname &NAME. remote "&filepath." server = SRV1;
rsubmit SRV1;
libname &NAME. "&filepath.";
endrsubmit SRV1;
%mend;
%MACRO_ASSIGN_LIB (NAME=SRV1OUT, filepath=/SAS_SERVER1/XX/XX/XX/output);

/*LOGON TO REMOTE SERVER 2*/
signoff remote=SERV2;
signon remote=SERV2 user="XXXX" password="XXXX";
/* ASSIGN Remote SERVER1 WORK DIR */
libname SRV2WORK slibref=work server=SERV2;

/*assign the libnames where the datasets you want to run the proc compares on */
/*SERV2 LIBNAMES*/
%macro MACRO_ASSIGN_LIB (NAME=, filepath=);
%syslput name = &name.;
%syslput filepath = &filepath.;
libname &NAME. remote "&filepath." server = SERV2;
rsubmit SERV2;
libname &NAME. "&filepath.";
endrsubmit SERV2;
%mend;
%MACRO_ASSIGN_LIB (NAME=SERV2OUT, filepath=/SERVER2_SAS/XX/XX/XX/output);

/* this bit does not quite work */
rsubmit; /* DOES SOME EXTRA RSUBMIT SYNTAX NEED TO GO HERE ??? */
proc sql;
drop table re_int_model_input_diff;
create table re_int_model_input_diff as
select
a.account_id
,a.Var1 as Var1_SRV1
,b.Var1 as Var1_SERV2

,a.Var2 as Var2_SRV1
,b.Var2 as Var2_SERV2

,.....

,a.VarN as VarN_SRV1
,b.VarN as VarN_SERV2

from SRV1OUT.DATASETNAME a /* DOES EXTRA SYNTAX NEED TO GO HERE ??? */

inner join SERV2OUT.DATASETNAME b /* DOES EXTRA SYNTAX NEED TO GO HERE ??? */

on a.pkey=b.pkey
quit;
endrsubmit; /* extra endrsubmit syntax needed ?? */

Re: Can I run a proc compare on data across two separate remote servers in BASE SAS 9.4 or SAS EG 7.

Kurt_Bremser — Wed, 03 Jul 2019 07:24:19 GMT

If you use Remote Library Services to define remote libraries in your local session pointing to the remote locations, you can run proc compare in the local session. But be prepared that this may not be very efficient; you might find that downloading the datasets and then running the compare might perform better.

Re: Can I run a proc compare on data across two separate remote servers in BASE SAS 9.4 or SAS EG 7.

ZolaGola99 — Wed, 03 Jul 2019 07:31:46 GMT

Thanks Kurt. Yep I've done the downloading way previously but this particular dataset I'm wanting to compare is very large (wide and long) and takes a extremely long time to download (many many hours). Anyway thanks for your help. I'll read up on RLS and see what is the best way as this type of comparison work that I'm doing will be continuous and on a regular basis.

Cheers

ZolaG

Re: Can I run a proc compare on data across two separate remote servers in BASE SAS 9.4 or SAS EG 7.

Kurt_Bremser — Wed, 03 Jul 2019 07:42:56 GMT

You basically do

libname remlib1 'path on server 1' server=server1;
libname remlib2 'path on server 2' server=server2;

after signing on to both servers.

Then you can compare a dataset in remlib1 with one in remlib2.

Or you do

signon server1
rsubmit;
signon server2;
libname remlib 'path on server2' server=server2;
proc compare ........;
signoff server2;
endrsubmit;

to run the compare on one of the servers, so at least one dataset is read locally.