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 ?? */