topic Re: Moving data from one remote server to another remote server with different sas versions. in SAS Procedures

Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Mon, 29 Apr 2013 20:09:51 GMT

I need to move many data tables from different directories on a remote server to another remote server. The first remote server is running sas 9.1.3 32-bit and i'm trying to move them to a sas 9.2 64-bit on a different remote server. I want to make sure the tables are created with the 9.2 64-bit.

On remote server 1 I have directories 1968 through 1997. So I need to copy the data from each directory from the first server to the second server. I don't want to write individual libname statements for each year. Can anyone tell me the best way to do this with a macro or loop?

I'm able to get the data down from the first remote server.

Connect to 9.1.3 remote server.

rsubmit ;

%let type=prod ;

libname &type. "~/tempdata/1968/" compress=yes ;

proc download inlib=&type. outlib=work ;

run;

endrsubmit ;

signoff ;

Connect to 9.2 remote server.

rsubmit ;

%let type=test ;

libname &type. "~/data/1968/" compress=yes ;

proc upload in=work out=&type ;

run;

endrsubmit ;

signoff ;

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Tue, 30 Apr 2013 15:38:54 GMT

I get an error running the code below and I can't seem to find the reason.

I'm uploading a table(Paths) that has a list of directories. I have to concatnate that value to the libname below so I can have libnames to all my subdirectories.

TABLE PATHS

path

------

/year/1910/

/year/1920/

*Connected to remote server already ;

rsubmit ;

proc upload data = work.paths out = work.paths ;

run ;

data _null_ ;

set paths ;

call symput('path'||trim(left(_n_)), trim(path) ) ;

call symput('nrow', trim(left(_n_)) ) ;

run ;

data _null_ ;

%do i=1 %to 1 ;

libname prod&&i "~/temp/test&&path&i" access=readonly ;

%end ;

run ;

endrsubmit ;

This is the errors I get :

10 data _null_ ;

11 %do i=1 %to 1 ;

ERROR: The %DO statement is not valid in open code.

12 libname prod&&i "~/temp/test&&path&i" access=readonly ;

WARNING: Apparent symbolic reference I not resolved.

ERROR: Libref in LIBNAME statement must be followed either by quoted string or

engine name or semicolon; "&" found.

ERROR: Error in the LIBNAME statement.

WARNING: Apparent symbolic reference I not resolved.

WARNING: Apparent symbolic reference PATH not resolved.

WARNING: Apparent symbolic reference I not resolved.

13 %end ;

ERROR: The %END statement is not valid in open code.

14 run ;

Re: Moving data from one remote server to another remote server with different sas versions.

Florent — Tue, 30 Apr 2013 17:09:22 GMT

Hello,

As it is mentioned in the log, it is not allowed to have macro code in open code.

You should replace the following:

data _null_ ;

%do i=1 %to 1 ;

libname prod&&i "~/temp/test&&path&i" access=readonly ;

%end ;

run ;

by:

%macro createLibname;

%do i=1 %to 1 ;

libname prod&&i "~/temp/test&&path&i" access=readonly;

%end ;

%mend createLibname;

%createLibname;

Kr,

Florent

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Tue, 30 Apr 2013 17:30:33 GMT

Florent,

Thank you that was it. I looked at that code for so long I couldn't focus anymore.

Thanks for the help

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Wed, 01 May 2013 12:55:19 GMT

Florent,

I'm still having an issue I need some help with. Here is the code i'm running and I keep getting errors.

data paths ;

length path $30 ;

input @1 path $30. ;

cards;

/year/1997/

/year/1998/

/year/1999/

;

run ;

options netencryptalgorithm = tripledes ;

%let host=testhost.testhost ;

signon host.50001 user=?????? password=?????? ;

%macro paths ;

rsubmit ;

proc upload data = work.paths out = work.paths ;

run ;

data _null_ ;

set paths ;

call symput('path'||trim(left(_n_)), trim(path)) ;

call symput('nvar', trim(left(_n_))) ;

run ;

%do i=1 %to 3 ;

libname prod&&i "~/temp/test&&path&i" access=readonly ;

%end ;

endrsubmit ;

%mend paths ;

%paths ;

signoff ;

Gives me these errors:

WARNING: Apparent symbolic reference I not resolved.

8 libname prod&&i "~/dc/data_test&&path&i" access=readonly ;

WARNING: Apparent symbolic reference I not resolved.

ERROR: Libref in LIBNAME statement must be followed either by quoted string or

engine name or semicolon; "&" found.

ERROR: Error in the LIBNAME statement.

WARNING: Apparent symbolic reference I not resolved.

WARNING: Apparent symbolic reference PATH not resolved.

WARNING: Apparent symbolic reference I not resolved.

Thank you for any help you can give me. I have to complete this project today and I keep hitting a wall.

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Wed, 01 May 2013 13:01:51 GMT

Why are you using a macro?

rsubmit;

proc upload data = work.paths out = work.paths ;

run ;

data _null_;

set paths;

libref=cats('path',_n_);

rc=libname(libref,path);

put (rc libref path) (=);

run;

endrsubmit;

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Wed, 01 May 2013 13:11:54 GMT

Hi Tom,

Thank you so much for your reply. The reason I was using a macro was because was to have it loop through and create all my libnames then I have to take all the data from that libname and copy it to my local sas and then taking it from my local sas to a completely different remote server. I thought it would be cleaner with a macro. Thank you for your code I will try and accomplish the same thing using your code. It looks so much cleaner as well.

Thank you

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Wed, 01 May 2013 13:52:21 GMT

If you do need to loop then the only macro variable you will need is the upper bound. To create a macro variable NPATHS with then number of observations in the PATHS dataset you could modify the data _null_ as below.

rsubmit;

proc upload data = work.paths out = work.paths ;

run ;

data _null_;

if eof then call symputx('npaths',_n_-1);

set paths end=eof;

libref=cats('path',_n_);

rc=libname(libref,path);

put (rc libref path) (=);

run;

endrsubmit;

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Wed, 01 May 2013 14:50:42 GMT

Hi Tom,

Thanks for the reply. I still have to be able to loop through the different libnames to pull all the data tables and download them to my local work folder.

I have to use a loop to connect to remote server 1 and create the first libname then download the data from that libname to my local work. From there I need to signoff of remote server 1 and connect to remote server 2 which will use the same path and have the data uploaded to that directory on that server.

So the the i variable in my do loop is used to identify which libname to copy from and which one to upload too. I hope I explained this clearly if not please let me know.

Loop through the number of paths 1-n. Below is the steps i'm trying to take

do loop i = 1 - n

connect remote server 1

create libname prod&i "~/temp/test&&paths&i" access = readonly ;

download data from libname prod&i to my local work folder

signoff remote server 1

connect remote server 2

create libname prod&i "~/temp/test&&paths&i" access = readonly ;

upload data within my local work to the directory on remote server 2

signoff remote server 2

end loop

Thank you for you help and patience.

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Wed, 01 May 2013 15:05:41 GMT

Why not reframe the solution so that it does not involve any remote submitted code?

Connect to server1
Connect to server2
Create two librefs for each path on local session using REMOTE engine to the two different server.
Copy from libref to libref.

You could put the loop around steps 1 to 4, 3 and 4 or just loop within step 4.

connect server1;

connect server2;

libname from1 remote 'path1' server=server1;

libname to1 remote 'path1' server=server2;

proc copy inlib=from1 outlib=to1;

run;

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Wed, 01 May 2013 15:19:16 GMT

Hi Tom,

Thanks for showing me a different way to approach my issue. I have a few questions if you don't mind.

When I connect using this code

options netencryptalgorithm = tripledes ;

%let host=testhost.testhost ;

signon host.50001 user=?????? password=?????? ;

What value from above to I use for the server value on the libname statement? Is it host?

Thank you again

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Wed, 01 May 2013 15:26:58 GMT

Should be HOST. Try this:

%let host1=testhost.testhost ;

%let host2=testhost.testhost ;

signon host1.50001 user=?????? password=?????? ;

signon host2.50001 user=?????? password=?????? ;

libname from1 remote 'path1' server=host1;

libname to1 remote 'path2' server=host2;

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Wed, 01 May 2013 15:44:04 GMT

Tom,

I was able to do the connect parts but when I run this libname statement I get this error

%let host1=Server1 ;

%let host2=Server2 ;

signon host1.50001 user=????? password=????? ;

signon host2.50001 user=????? password=????? ;

libname from1 remote '~/temp/test/year/1997/' server=host1 ;

ERROR: Attempt to connect to server HOST1 failed.

ERROR: A communication subsystem partner link setup request failure has occurred.

ERROR: Cannot find TCP service 'host1'.

ERROR: Error in the LIBNAME statement.

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Wed, 01 May 2013 16:20:45 GMT

I have never needed to specify the port number when using SAS/Connect. (the .5001 suffix on the hostname alias in your example).

Try the SIGNON command without it.

If you find that you need the port number then the documentation for SAS 9.3 says you need to use

server=host1._ _5001

in the LIBNAME statement.

Re: Moving data from one remote server to another remote server with different sas versions.

jerry898969 — Wed, 01 May 2013 16:27:42 GMT

Tom,

The reason for the port number is there are 2 different versions of SAS that are running. 9.1.3 on one of the servers and 9.2 on the other.

I tried what you listed for 9.3 and it gave me the same error. Should I go back to doing this by connectiong to one server copy the data down and then disconnect then connect to server 2 and then upload?

I think i'm making this much more difficult that it should be and I do apologize.

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Wed, 01 May 2013 16:34:42 GMT

You can open a ticket with SAS Support to figure out how to get REMOTE libname engine to work right for your connections.

Technical Support Form

Re: Moving data from one remote server to another remote server with different sas versions.

Florent — Thu, 02 May 2013 12:14:31 GMT

Hi Jerry,

When looking at your errors (see below), it looks obvious to me that you did not declare the macro variables Path1, Path2 and Path3. Because of that, you get the warning on symbolic reference PATH not resolved.

WARNING: Apparent symbolic reference I not resolved.

8 libname prod&&i "~/dc/data_test&&path&i" access=readonly ;

WARNING: Apparent symbolic reference I not resolved.

ERROR: Libref in LIBNAME statement must be followed either by quoted string or

engine name or semicolon; "&" found.

ERROR: Error in the LIBNAME statement.

WARNING: Apparent symbolic reference I not resolved.

WARNING: Apparent symbolic reference PATH not resolved.

WARNING: Apparent symbolic reference I not resolved.

Also, the definition of the libname should rather look as follows (without a double ampersand in the name of the libname):

libname prod&i "~/temp/test&&path&i" access=readonly ;

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Thu, 02 May 2013 13:06:31 GMT

The other syntax in the help file is to put the port number into the macro variable, then you can reference it as a single level name.

%let host1=Server1 50001 ;

signon host1 user=????? password=????? ;

libname from1 remote '~/temp/test/year/1997/' server=host1;

Re: Moving data from one remote server to another remote server with different sas versions.

Tom — Thu, 02 May 2013 13:16:13 GMT

You are still having trouble because of the interaction of the macro execution and the remote submission.

You are running the macro on your local session so the variable I is defined in the local session. One way to fix that is to copy it to the remote session using %SYSLPUT.

%macro xx;

rsubmit;

%do i=1 %to 1 ;

%syslput i=&i ;

libname path&i '.';

%end;

endrsubmit;

%mend xx;

%xx;

Another way is to define and execute the macro on the remote session.

rsubmit;

%macro xx;

%do i=1 %to 1 ;

libname path&i '.';

%end;

%mend xx;

%xx;

endrsubmit;

Re: Moving data from one remote server to another remote server with different sas versions.

MarkW — Thu, 02 May 2013 13:19:30 GMT

I just went though this exercise on remote linux servers and found the easiest and fastest way was to ftp the data and then run proc migrate.