BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jerry898969
Pyrite | Level 9

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 ;

1 ACCEPTED SOLUTION

Accepted Solutions
Florent
Quartz | Level 8

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

20 REPLIES 20
jerry898969
Pyrite | Level 9

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 ;

Florent
Quartz | Level 8

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

jerry898969
Pyrite | Level 9

Florent,

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

Thanks for the help

jerry898969
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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;

jerry898969
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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;

jerry898969
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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;

jerry898969
Pyrite | Level 9

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

Tom
Super User Tom
Super User

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;


jerry898969
Pyrite | Level 9

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.

Tom
Super User Tom
Super User

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.

jerry898969
Pyrite | Level 9

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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