Unix Files capture through SAS dataset

[Banu]

Hi,

I am new to SAS and got one requirement to automate one manual task. I have multiple servers like 'A','B','C','D'. Here 'A' is the Cenrtal server which has SFTP/SSH connections to all B,C & D servers. We have monthly jobs in 'B server which outputs SAS dataset in 'B' server and then SFTP to the 'A' server. From 'A' server we publish to 'C' and 'D' servers. Now I wants to automate the script to capture the SAS dataset which published in 'B' server is available in all A,C and D servers. Have all SFTP connections from A to B and A to C and D.

Please suggest how to can we achieve on this. SAS datasets are in UNIX server.

[AnandVyas]

Hi @Banu,

You have mentioned that you already publish from A server to C and D servers. Can't you use the same way to publish it from B to A,C,D?

[Banu]

Thanks for your reply. Here issue is not with copying through SFTP. I wants to show whether SAS dataset is published in all servers or not & Whether process it self published SAS dataset in same server or not & If process published SAS dataset, whether it published in Remote servers or not for this month. Needs generate report by showing all this info. Attached is the sample report. Please have a look once. Thank you.

[Shmuel]

Looking on the report I suggest you need:

1) Define FILENAMEs to each file on each server.

2) Use FILEEXIST sas function to check does the file exist

3) create a macro to check existence of a file given filename and server name

4) do a loop to check them all and append result to a reporting dataset

    finale create the report

[SASKiwi]

@Banu  - Moving SAS data between SAS servers is a LOT easier if you have a SAS/CONNECT installed on all of them and USE PROC UPLOAD and DOWNLOAD. The SAS logs from these jobs then become the audit trails for your data transfers.

[Kurt_Bremser · Accepted solution]

So you want to test if a file (in your case a .sas7bdat file) exists on a remote server, and do this non-interactively.

A possible command to do this is

ssh servername.domain.com test -f '/complete_path/dataset.sas7bdat'

The single quotes are there if you need to send something like $HOME to the remote server. The command will return a 0 if the file exists, and a 1 if not.

So start with creating a dataset with host names and dataset names:

data datasets;
input servername :$100 dsname :$200;
datalines;
server_a /path/test1.sas7bdat
server_b /path/test1.sas7bdat
;

Then you can run a data step off that and record the result:

data results;
set datasets;
length command $200;
command = "ssh " !! strip(servername !! " test -f '" !! strip(dsname) !! "'";
result = system(command);
run;

If you're not sure what the command returns, use a dynamic pipe to get messages:

data results;
set datasets;
length command $200;
command = "ssh " !! strip(servername !! " test -f '" !! strip(dsname) !! "'; echo $?";
infile dummy pipe filevar=command truncover eof=done end=last;
start:
  input line $100.;
  if last then result = input(line,best.);
  output;
  go to start;
done:
run;

I got the hint with using a remote test through ssh from https://superuser.com/questions/850158/how-to-check-if-file-exists-in-remote-sftp-server-from-local-..., which was the first result of a Google search for "check existence of file with sftp".

I tested the command from one of our AIX servers to another (both use openSSH), and it worked as described.

You have to set up passwordless authentication with public/private key pairs to all servers first, or the ssh would always prompt for a password.

Edit: added capture of return code to the dynamic pipe.

[Kurt_Bremser]

Hi @Banu, since you accepted my suggestion as the solution, I am very interested in the actual solution you implemented.

The reason for this is that the method I presented here is the final point of the paper I will present at #SASGF 2020 (Talking To Your Host). I would really like to include your solution as a "real world" example, including you in the acknowledgements and this thread as a reference.