10-06-2015 05:55 PM
First I would like to ask that any help be provided in a simple to understand manner. I am not a coder, I use EG point and click interface
I have a report and I'm using a list task to group by "category" and I would then like to export to excel, where each category has it's own excel tab.
I found a solution on the interwebs to modify my list task by inserting custom code as follows:
ods tagsets.excelxp file='multisheet.xls'
The above works, but I cannot access the file, because SAS puts it in the following directory: /sas5/opt/SAS_9.3/config/Lev1/SASApp
I tried to change the path to one I had access to and is my usual SAS export location:
\\safedata\SHARED.DATA\Vendor Relations\Business Analysis\SAS\Output\multisheet.xls
But when I do that, it still appends the /sas5/opt/SAS_9.3/config/Lev1/SASApp directory first and I can't get to my file.
I can see a file icon, which does have my data. I can then double click it, open it in excel and manually save my file, but this prevents me from automating my queries, which I'm being told is not an option.
Through googling and working with my IT, I believe this is some sort of communication issue due to SAS being on a UNIX server. But they do not know how to fix.
Appreciate your time in advance.
10-06-2015 06:14 PM
10-06-2015 07:41 PM
The file needs to be saved to a server directory first e.g. ods tagsets.excelxp file='/home/myuserid/multisheet.xls'.
After running the List task, right click on the file icon that's been created in the Process Flow palette, and select
Export --> Export <file> as a Step in Project...
From there you will be able to specify a location on your local machine. (The default file extension will need changing from .txt back to .xls)
Because the Export is just another task, you should be able to automate the whole process.
Hope this helps.
10-07-2015 12:17 AM
It looks like your SAS server runs on Unix (see the forward slashes in your error) but in your ODS file = option you are trying to write to a Windows shared folder (back slashes in your SAS export location).
I suspect the SAS server cannot reference your Windows shared folder like you can from your PC. Jerry's solution is probably the easiest for now but you could talk to your Unix admin about setting up Windows folder shares from the Unix server.