How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

[#1]

hi ,

i find the best way to export to an excel format without a wizard is by using ods tagsets.ExcelXP.

this works on a linux based server and you get a excel output in .xls format.

here is an example (fill in the XXXXX and the path) :

*************************************************

ods tagsets.ExcelXP options(embedded_titles='Yes')
file="/file_path/file_name.xls"  style=Sasweb
options(Autofit_height='yes' Absolute_Column_Width='3,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10' autofilter='all'   );

title "XXXXX";
ods tagsets.ExcelXP options (sheet_name = "XXXXXX") ;
proc print data=XXXXXXX  label;
;
label
;
run;

title ;
ods _all_ close;

*****************************************************

[j4copo]

Hi guys,

thanks a lot for all the help..

I finally found a way to export an excel file using code and no wizard. I used ods tagsets.ExcelXP, but the created files are saved in a temporary folder in my pc, then it is not all good..

Today I got the add-in PC Files installed on my machine and I am making new tries..

the problem seems to be that whenever i try to fix a local path (C:\desktop ... ) sas automatically append at the beginning of this path the following:

/sas/sw/config/Lev1/SASApp/ and consequently my path is not correct. seems like i am near to the solution but i'm sure there is a much simpler way to do it now that I have this PC Files installed on, maybe some initial setting modification ? 

I keep on making tries.

thanks for any help,

Jacopo

[Patrick]

This is a UNIX path and it's likely where your SAS session on the server gets started from:  /sas/sw/config/Lev1/SASApp/

This is a Windows path which I assume relates to your local workstation where SAS EG is installed: C:\desktop ..

SAS EG is the client. The EG Import & Export Wizards allow you to do stuff on the client side. Everything else runs on the server side. Writing Excel output via SAS code executes on the SAS server - and it must be a path to a directory known to the Server. And that's why c:\desktop... will not work.

The following link should be helpful to you

How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy

[jakarman]

That error is because you are using an Unix server not Windows and the current directory is set wrong by SAS and your IT staff did not correct that. 50345 - Changing the current working directory for the SAS® Workspace Server

You can still use the proc export creating an excel file on UNIX SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition The trick is using the XLSX option for the DBMS name. See the table what is working on Windows and what on Unix.

The XLSX option for the libname is new for 9.4 the export/import already do have them with 9.

The ods tagset can generate a xml-file that is what it does and Excel can read that. The compression of the data can be problematic (sizing network).