BookmarkSubscribeRSS Feed
mfeller
Fluorite | Level 6

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' 

options( sheet_interval='bygroup');

 

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.

 

Please help!

 

Appreciate your time in advance.

3 REPLIES 3
Reeza
Super User
You can create the file somewhere that you have access to on the SERVER, and then you can FTP or transfer the file to your local drive. As far as I know that step can't be automated.
JerryLeBreton
Pyrite | Level 9

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.

 

  

SASKiwi
PROC Star

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.  

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 3 replies
  • 960 views
  • 0 likes
  • 4 in conversation