Using macro variable value to transfer files to remote server.

[woo]

Hello, if someone can please help

Four step process. 

/*Step-1-working fine - creating sas dataset to list out number of files */

/*Getting list of the files from dir*/

data filenames;
length fref $8 fname $200;
did = filename(fref, '/woo/dir');
did = dopen(fref);
do i = 1 to dnum(did);
fname = dread(did,i);
output;
end;
did = dclose(did);
did = filename(fref);
keep fname;
run;

/*Step-2-working fine - creating macro variable based on number of value previous dataset has */

proc sql;
select fname into: vars separated by ',' from work.filenames;
quit;

/*Need help here for step-3 and step-4*/

/*step-3- want to transfer files into remote server  using sftp command*/

%let localDir = '/woo/dir';
%let remoteDir = '/remote/woo/dir';
%let remoteServer = 'woo@remoteserver.com';

/*sftp command - but this should be for multiple files may be*/

%let sftp_cmd=sftp &remote_Server <<! put &local_file &remote_path quit !;

x "&sftp_cmd.";

/*step-4-send email notification if there are files and got transferred - if not, send email no file to transfer */

filename mymail email "woo@company.com"

subject="sales file transfer email"

to="woo@company.com";

data _null_;

file mymail;

put "x number of file transferred";

run;

[woo]

I would like to add one extra step in last as well.

/*step-5-renmae file after transfer*/

/*if files exit and once file gets transferred rename file with may be extension "_old" 

[Patrick]

You could combine your step3 & step4 for into a data step using code as below. You actually could already run this as part of your step 1.

data _null_;
  set filenames;
  rc=system("<here your sftp command");
run;

I would be using rsync instead of sftp as this will allow you to transfer all files in your source folder with a single command. 

rsync has a lot of options so you could also delete all pre-existing files in target that don't exist in source, you can transfer the files compressed over the network, etc. etc.

Here a sample rsync command: rsync -avz -e ssh /path/to/local/directory/ user@remote:/path/to/remote/directory/

Your steps 1 to 3 could then look like:

data work.rsync_msg;
  infile "<here your rsync command>" truncover end=done;
  do until(done);
    input rsync_msg $200.;
    output;
  end;
  stop;
run;

If you configure passwordless ssh (one time config) then you don't need to provide a password in the command, and if the remote user is the same as the local one then you don't even need to provide a remote user.

[woo]

 you Patric. 

i would go with below route but trying to figure how sftp command would look like with multiple files 

data _null_;
  set filenames;
  rc=system("<here your sftp command");
run;

/*i tried this but didn't work*/

rc=system("sftp &remote_server <<! put &var. quit !");

[Patrick]

1. Make your command be it sftp or rsync as I suggest fully working out of a command prompt (Putty).
2. Call the fully working command out of SAS as-is and without any change
3. If 1 and 2 are working then you can alter your OS command to use macro variables and the like. Apply one change at a time and test it/make it work

[Patrick]

@woo 

Using rsync in an environment where passwordless ssh between the source and target server is configured for the user under which the process runs, below code works for me with SAS9.4 M7 under RHEL.

%let source_path=/src_folder1/src_folder2;
%let target_path=/trg_folder1/trg_folder2/trg_folder3;
%let target_server=<name of server - something like myserver.domain.company.com>;

/** -n for dry-run: test the script without actually transfering the files */
/* filename _rsync pipe "rsync -nriv --update &source_path/* &target_server.:&target_path"; */

/* transfer files newer in source than in target */
filename _rsync pipe "rsync -riv --update &source_path/* &target_server.:&target_path";
data work.rsync_messages;
  infile _rsync dlm=' ' truncover end=done;
  do until(done);
    input rsync_messages $200.;
    output;
  end;
  stop;
run;
filename _rsync clear;

proc print data=work.rsync_messages;
run;

rsync provides a lot of options that should allow you to have it behave exactly as you need it.

Configuring passwordless ssh isn't hard and easy to Google. Eventually have an admin doing it for you if you want to execute the process in batch under a functional user.

rsync would also allow you to pass user and password explicitly but I wouldn't consider this a very good option - certainly not for the password.

[ballardw]

If you expect to move multiple files I would suggest looking up the MPUT command for FTP and SFTP. That is MultiplePUT and is intended to transfer multiple files.

If you have multiple files that start with the same characters you can often use a wildcard with PUT or MPUT.

For step 4, if I understand the flow of your program, then the result of STEP 2 will (temporarily) set a SAS automatic macro variable &sqlobs with the number of results returned from the SQL. So you could capture that immediately after running the code for the fnames.

Something like:

proc sql;
select fname into: vars separated by ',' from work.filenames;
quit;

 %let filecount = &sqlobs;

Then use it as:

data _null_;
   file mymail;
   put "&filecount. files transferred";
run;

I don't see where you are using the macro variable Vars that you create.

It has been a very long time since I dealt with FTP or SFTP access via command line. But I remember that you should be able to write a text file for a script containing all the needed commands. It may be easier to do that and then pass the script to the SFTP program than to attempt to write a bazillion connect put strings the way your skeleton code implies you are trying.

On advantage of writing to a text script file with the commands is that you can give the file different names and have a "daily" or similar history of the commands. And reading the text of the script would be easier to debug than trying to guess what went wrong with a macro logic issue. You data set WORK.FILENAMES should suffice to write a text file. Write the connection stuff first instead of macro variables and then Put Fname to execute the transfer. Something along the lines of

data _null_;
   file scriptfile;
   set work.filenames eof=lastone;
   if _n_=1 then do;
     /* here would go Put with the connection information*/
   end;
   /* the Put in quotes is the PUT for SFTP*/
   put "Put " fname;
   if lastone then do;
     put 'quit'; 
     /* and anything else the end of the connection needs*/
   end;
run;

Depending on you set up execute with something like (likely to be very OS and installation dependent)

X "SFTP  < scriptfile";

Likely need more details for the PATH to the name of the script file but this is the sort of thing where the file gets treated somewhat like a keyboard typing in the commands.

[Kurt_Bremser]

See my WUSS presentation Talking to Your Host

At the end, you'll find an example of running multiple external commands off a dataset, with full retrieval of the results.

[Tom]

No need to use macro code for this problem. 

You can probably just use scp instead of sftp, the syntax is easier.

/*Getting list of the files from dir*/

%* Step 0 - Set inputs;
%let localDir = '/woo/dir';
%let remoteDir = '/remote/woo/dir';
%let remoteServer = 'woo@remoteserver.com';

* Step 1 - get list of files ;
data filenames;
  length fref $8 fname $200;
  did = filename(fref, "&localDir");
  did = dopen(fref);
  do i = 1 to dnum(did);
    fname = dread(did,i);
    output;
  end;
  did = dclose(did);
  did = filename(fref);
  keep fname;
run;

* Step 2 - Transfer files ;

data _null_;
  set filenames ;
  length cmd $500 ;
  cmd = catx(' ','scp',cats("&localdir/",fname),cats("&remoteServer:&remotedir/",fname));
  infile cmd pipe filevar=cmd end=eof;
  do while (not eof);
     input;
     put _infile_;
  end;
run;

If you want to detect some errors then try forcing errors by using an invalid remote dir or invalid server and see what response scp sends back to SAS.  Then you can test the value of _INFILE_ and make decisions on success/failure of the transfer.

And if the goal is to transfer ALL of the files they might be able to do it with one scp command and skip making the dataset altogether.

[woo]

Thank you Tom - i am trying to take this approach but seems output command in the log not coming out fine, single quote for file path and file name doesn't cover complete path for file. 

From Log: 

Pipe command="scp '/woo/dir'/SAS_work213452007553_servername_tets_file_name.txt
'username@servername.com':'/remote/woo/dir'/SAS_work213452007553_servername_tets_file_name.txt"

[Tom]

You are doing something silly somewhere probable.

How did that SAS work folder name get into your command?

If you are going to use the path you stored in the macro variable as PART of the filename then do not add the quotes into the macro variable.  Add the quotes when you need them. So something like:

%let localdir=/data/woo;
...
cmd=catx(' ','scp',quote(cats("&localdir/",filename),"'"),....