Output tables as Excel files

[Kfhansen]

at the top of your EG program editor you should see "Selected Server:  .. " next to the grayed out stop menu .. click that and choose local to execute locally.

Also as tish suggested I would add a seperator in your first sql where you create the list

select .... into this_list seperated by ','

and change the %scan to %scan(&this_list,&x,",")

[Cynthia_sas]

Hi:

  In addition to all the other suggestions, related to your EG log, the log did tell you what was wrong. Here's the warning:

WARNING 1-322: Assuming the symbol HTML was misspelled as HMTL.

and here's the code you posted:

ODS HMTL FILE="C:\TEMP.XLS";  <--error in this line

PROC PRINT DATA=work.TEST;

RUN;

ODS HTML CLOSE;

Notice that you opened the HMTL destination, but you closed the HTML destination. There is no HMTL destination. Usually, when you use ODS, the opening invocation and the closing invocation have to match. Also you tried to "fool" EG by naming the file with a .XLS extension. There is a good chance that when you use this technique, you canNOT open the file in EG. EG will want to show you the HTML file, as HTML, because EG knows that it created an HTML file. Just because you named the file with a .XLS extension does not mean that EG will launch Excel for you. Sometimes, you have to go to Windows Explorer and right click on the file or you have to go inside Excel and do a File --> Open in order to open the file that you create with ODS.

In addition, Microsoft doesn't really not like the "flavor" of HTML tags created by ODS HTML. By default ODS HTML creates HTML 4.0 tags. In response to the HTML 4.0 specification of the W3C, Microsoft wrote their own "flavor" of HTML for Office 2000. ODS can create the Microsoft "flavor" of HTML, but you would have to use this destination instead of ODS HTML:

ODS MSOFFICE2K FILE="C:\TEMP_MSO.XLS";  <--use Microsoft "flavor" of HTML

PROC PRINT DATA=work.TEST;

RUN;

ODS MSOFFICE2K CLOSE;  <-- Close what was opened

And, still, EG will probably not want to open Excel for you automatically. If you open the file with Notepad, you will see HTML tags. Both Excel and EG know that there are HTML tags in the file. EG ignores the .XLS extension and shows you the file on the result tab using a browser-like viewer. Excel will open the file, outside of EG; however, since you gave the file an XLS extension, Microsoft wants you to know, that it knows that you didn't really create a binary XLS file and so Excel will display a somewhat snarky message:

The file that you are trying to open "fileabc.xls" is in a different format than   specified by the file extension. Verify that the file is not corrupted and is from a  trusted source before opening the file. Do you want to open the file now.

(as described here: http://support.sas.com/kb/31/956.html) All you have to do is click Yes to get Excel to open the file and render the HTML inside Excel.

So, aside from any other issues, this is what was happening in EG when you 1) misspelled the destination and 2) used a file extension of .XLS; Excel started to "harden" file extension in Office 2007. In the old days of SAS and Excel, if you named your HTML file with a .XLS extension, Excel would launch without complaint (in Office 97, Office 2000 and Office 2003). You are never creating a "true, binary" Excel file with ODS -- you are only creating ASCII text files that Excel knows how to open. EG pays more attention to the destination you use (HTML) than to the file extension you give the file. That just means that if you had NOT misspelled the destination, you would have needed to go outside of EG to find and open the output file.

More information,

cynthia

[nicnad]

Thank you for the precision Cynthia.

I still get an error when using the proper code :

NOTE: Some of your options or statements may not be supported with the Activex or Java series of devices.  Graph defaults for these

      drivers may be different from other SAS/GRAPH device drivers.  For further information, please contact Technical Support.

9          OPTIONS DEV=ACTIVEX;

10         FILENAME EGHTML TEMP;

NOTE: Writing HTML(EGHTML) Body file: EGHTML

11         ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault

11       ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared%20Files/BIClientStyles/EGDefault.css")

11       ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation

11       ! ;

12       

13         %gaccessible;

NOTE: Writing MSOFFICE2K Body file: C:\TEMP_MSO.XLS

ERROR: Insufficient authorization to access /u01/app/sas/Config9.1.3/EMiner/Lev1/SASMain/C:\TEMP_MSO.XLS.

WARNING: No body file. MSOFFICE2K output will not be created.

14         ODS MSOFFICE2K FILE="C:\TEMP_MSO.XLS";

15       

16         PROC PRINT DATA=work.NULL;

17       

18         RUN;

Do you know what is causing this error and how to solve it?

[nicnad]

Thank you for the reply.

I don't have the same interface as you, but when I go in the "code" menu and click choose a server, I don't have the choice to run it on local server.

Any idea how to fix this?

Thank you all for your help and time.

[Cynthia_sas]

Hi:

  Here are some other suggestions, but you might need to work with one of your colleagues who has more experience with SAS and/or work with Tech Support. The error message you got is very interesting. I don't have many solutions, just suggestions and questions. Solutions will have to come from Tech Support.

NOTE: Writing MSOFFICE2K Body file: C:\TEMP_MSO.XLS

ERROR: Insufficient authorization to access /u01/app/sas/Config9.1.3/EMiner/Lev1/SASMain/C:\TEMP_MSO.XLS.

WARNING: No body file. MSOFFICE2K output will not be created.

14         ODS MSOFFICE2K FILE="C:\TEMP_MSO.XLS";

1) the fact that you do not see a LOCAL choice for server tells me that SAS is not installed on your LOCAL computer -- you must have SAS only installed on a server. A complicating factor is: HOW is SAS installed:

--a) just a client/server install (with local EG and remote SAS on a server)

--b) as part of an Enterprise Miner install

--c) as part of a BI install

2) The ERROR message above (red highlighting mine tells me several different things:

--a. The Insufficiant authorization message tells me that your SAS server has a working directory defined for programs and you might not be allowed to write to that directory. Whether you believe this is correct or incorrect, operating system permissions are created by an admin inside your company. Someone at your company has set permissions in such a way that they do not expect you to or they do not want you to write to the directory on the SAS server. Or, you may have write permissions to that location (which I think is unlikely given the "Lev1" piece of the name), but, your FILE= combined with the default server directory generates an incorrect UNIX path -- read on.

--b. The fact that all the slashes go a different direction than the Windows slashes implies to me that your SAS is running on a UNIX flavor computer (or region or partition). And, since there are no UNIX boxes with a C: drive, you've probably generated an incorrect UNIX file name by putting that C:\ in the FILE= option. You might not get the same message if you did this:

ODS MSOFFICE2K FILE="TEMP_MSO.XLS";

BUT, your admin still may have locked down that physical directory. So, there's a good chance that this suggestion will NOT work.If you do not receive any error messages and you CAN write to  /u01/app/sas/Config9.1.3/Eminer/Lev1/SASMain directory path, then you will probably have to naviagate to that directory or FTP from that directory to your local machine to look at the file. As far as I know, there is no Microsoft Office for UNIX, so you'll have to get that file to your machine to open it. If you let EG do the download to your local machine, it will probably come down as an HTML File and you probably do not want that.

--c. The fact that you can't write to /u01/app/sas/Config9.1.3/Eminer/Lev1/SASMain coupled with the fact that EMINER is in the path name AND the fact that LEV1 is in the path name implies that you are using either Enterprise Miner standalone on a server or are using Enterprise Miner in a BI configuration. Or you could have both Enterprise Guide and Enterprise Miner. Or you could have EM and EG and the BI Platform -- and that is even more uncertainty about how you are trying to create your output. (Although most of your log looks like EG log messages, so I figure that someone possibly configured your EG to go to an Enterprise MIner directory) -- I find this confusing and possibly in need of more research on your part, either with guidance from Tech Support or guidance from your SAS Admin.

3) Since your FILE= string is appended to some default path, there is a possible resolution for this issue -- to use an explicit PATH= option:

ODS MSOFFICE2K PATH="C:\TEMP\" FILE="TEMP_MSO.XLS";

4) The fact that you also see a first ODS statement (created by EG) tells me that you have HTML turned on as the result type. I would recommend turning it off
(either using Tools-->Options)

or by -ADDING-

ODS _ALL_ CLOSE;

at the very top of all your code -- to turn off the EG automatically created output and ONLY create your output. Leave the rest of your program the same.

At this point, you are close to exhausting the type of help that you can get on the Forum. It may be time to open a track with Tech Support for more personalized help. They can look at all your code, ask questions about your configuration and make some recommendations, based on what they figure out. In addition, if you have older versions of SAS, they can test your code in that earlier version of SAS.

cynthia

[nicnad]

Thank you for your reply cynthia.

Very complete and precise answer.

I will investigate this with my IT support team and will let you know the outcome.

In the perspective that I am able to export SAS tables to Excel on my local drive, what would be the code used to do the following :

For each tables in work library

export to "c:\test\" with table name as file name

next

end sub

Thank you for your help and time!

[Cynthia_sas]

Hi:

    There are several possible approaches, but I will talk about the  basic approaches that are SAS-oriented (but each approach is composed of multiple possibilities):

1) Export "SAS data to data worksheet" methods--this export approach creates true Excel files in Excel proprietary format:

---PROC EXPORT

---the SAS Libname engine for Excel

2) Create report output from a SAS procedure and open the report output with Excel. This is not "technically" a data to data export. In this method, you mostly use ODS to create a report (could be from PROC PRINT, but might be from PROC UNIVARIATE or PROC GLM or...)

---create plain text delimited file (mostly using ODS CSV or ODS CSVALL)

---create ASCII text HTML file using ODS HTML, ODS MSOFFICE2K or ODS TAGSETS.MSOFFICE2K_X to create HTML files that Excel knows how to open and render

---create ASCII text XML lfile formatted using Microsoft spreadsheet markup XML format for Office 2003 using ODS TAGSETS.EXCELXP to create an XML file that Excel knows how to open and render.

So far, you have had both types of examples posted. Different examples -- you had LIBNAME engine examples posted and ODS examples posted. Since the last batch of error messages were related to using ODS, that's when I chimed in.  I still recommend that you work with Tech Support to get 1 dataset or report correctly opened with Excel before you move into the question of "how do export everything in WORK". First you have to pick a method, successfully get your data or report into Excel using that method, then you have to repeat that process for however many datasets you need to send to Excel. Whether you use ODS or use PROC EXPORT or the LIBNAME engine is up to you.

Tech Support can help you figure out what the issue is with getting 1 data set or 1 report into Excel. As for how to repetitively generate output, there have been many, many previous forum postings on this topic. Many of them involve the SAS Macro facility. It doesn't make sense to me to go forward with more, and different, examples until you have working code of some kind.

Other folks may chime in with more examples, but, to me the downside of this approach is that if someone posts code that you can't run because of some of the issues you've already encountered, then you will have travelled a long way only to end up in the same place. You might have code, but how useful is it if it's not successful on your system/configuration.

Tech Support can stick with you for the long haul -- they can start with one program and can monitor your progress until you have the type of output you want.

cynthia