Using SAS X Command with PCFILES server

Question

I have some SAS code that writes out to a specific sheet in an excel workbook. The other sheets have formulas that look to this sheet so the workbook is basically a template that gets populated when my code is run.

I want to be able to run the code multiple times, and end up with a different version of the template each time it is run. I'm thinking the easiest way to achieve this is write out to the template and then use x command or something to copy the workbook and then rename it.

SAS is on a linux server and I use a pc files server to write to excel. How do I set up x command to copy the file and change its name on the remote server?

Is the workbook physically on the linux server (or on a share it has mapped)? — Joe, Jan 21 '14 at 18:39
The workbook is on a windows server. I've switched back and forth between connecting remotely using proc export and using libname with the pcfiles engine. — JstSomeGuy, Jan 21 '14 at 18:46
If it can't directly see the workbook, I don't believe it can copy it (using `x` or anything else). You could import the workbook and create a new copy of it, if that won't harm it, but if you have connections or macros in it, that won't solve your problem. You might consider using a scripting solution; Chris Hemedinger's "Copy Files" task might be adaptable, for example. — Joe, Jan 21 '14 at 18:52
I'm not sure what you mean by 'see'. SAS is able to access it and write to it (using either proc export or the libname and a data step) and I can tell SAS where it is located with the libname or with a filepath. — JstSomeGuy, Jan 21 '14 at 18:55
Hmm, not sure I understand your earlier comment. From the linux machine, can you (in the command line) write something like 'cp \\pathto\myfile.xlsx \\pathto\myfile1.xlsx'? Not taking SAS into account at all. — Joe, Jan 21 '14 at 18:58
That's what I mean by 'see'; that means nothing you could do on the linux server with `x` could help. I'm not aware of a way to execute `x` with `PCFILES SERVER`. You might consider posting on communities.sas.com to see if one of the devs or other power users has a suggestion, if nobody here does. — Joe, Jan 21 '14 at 19:16

score 0 · Answer 1 · answered Jul 11 '14 at 20:45

Sorry for the late answer, but I just encountered this myself and can provide a solution.

Can you access files on the sas server through windows (with an SMB share or similar)? That is the easiest way to do this. If you can't, it is typically very easy to set up Samba on linux.

First, store the excel template on the SAS server where it can be seen from your windows computer (with the PC Files Server on it).

Lets say the file is in '/home/files/template.xlsx' on the SAS server, and that directory is shared and accessible on your windows server as '\linuxservername\files\template.xlsx' (or \192.168.1.5\files\template.xlsx if you are using IP addresses)

Now you just have to use the SMB path for the PCFILES stuff and the local linux path for the x command. Something like

x 'cp /home/files/template.xlsx /home/files/output.xlsx';
libname output PCFILES 
  path='\\linuxservername\files\template.xlsx'
  server='PCFilesServerName'
  port=1234;

Note that when feeding a path to PCFiles, you use the network address (since your windows box has to be able to read and write it) and when you issue the copy command, you use the local address.

You may also be able to use the network path for everything depending on your system configuration. When I tried it, I could not make it work since the unix server did not like it (it shares the folder over SMB, but it didn't know how to access it from the command line).

Using SAS X Command with PCFILES server

1 Answers1