Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 400
Accepted Solution

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

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 ;


Accepted Solutions
Solution
‎04-30-2013 01:09 PM
Frequent Contributor
Posts: 127

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

Posted in reply to jerry898969

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

View solution in original post


All Replies
Super Contributor
Posts: 400

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

Posted in reply to jerry898969

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.

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 ;

Solution
‎04-30-2013 01:09 PM
Frequent Contributor
Posts: 127

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

Posted in reply to jerry898969

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

Super Contributor
Posts: 400

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

Florent,

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

Thanks for the help

Super Contributor
Posts: 400

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

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.

Super User
Super User
Posts: 7,039

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

Posted in reply to jerry898969

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;

Super Contributor
Posts: 400

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

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

Super User
Super User
Posts: 7,039

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

Posted in reply to jerry898969

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;

Super Contributor
Posts: 400

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

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.

Super User
Super User
Posts: 7,039

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

Posted in reply to jerry898969

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

  1. Connect to server1
  2. Connect to server2
  3. Create two librefs for each path on local session using REMOTE engine to the two different server.
  4. 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;

Super Contributor
Posts: 400

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

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

Super User
Super User
Posts: 7,039

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

Posted in reply to jerry898969

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;


Super Contributor
Posts: 400

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

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.

Super User
Super User
Posts: 7,039

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

Posted in reply to jerry898969

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.

Super Contributor
Posts: 400

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

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. 

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 1185 views
  • 3 likes
  • 4 in conversation