SAS Data to Excel

[deleted_user]

Hi,

I am working with SAS 9.1. I am trying to create a report in Excel whose input is a SAS Dataset. Kindly suggest me the methods available to do this. Also is there a way to run Excel macros using SAS?

Kindly suggest.

Mee

[Peter_C]

have a look at http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

[deleted_user]

Hi Peter,

Thanks for the link. It was useful. I read the docs and concluded to use ODS HTML. Wrote a simple code to output to excel:

ODS HTML FILE="C:\NEW.XLS";
PROC PRINT DATA=SASHELP.CLASS;
RUN;

This gave the following error:
ERROR: Invalid file, C:\NEW.XLS.
ERROR: Invalid file, C:\NEW.XLS.
ERROR: Invalid file, C:\NEW.XLS.
WARNING: No body file. HTML output will not be created.

Could you help me proceed?

Thanks,
Mee

[Peter_C]

you just need to close the ods destination, like in log-line 93 below:[pre]90 ODS HTML FILE="C:\NEW.XLS";
NOTE: Writing HTML Body file: C:\NEW.XLS
91 PROC PRINT DATA=SASHELP.CLASS;
92 RUN;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.11 seconds
cpu time 0.01 seconds


93 ODS HTML CLOSE ;[/pre]
Of course, I do not know where and when your error message appeared, so you may have some other problem, still to be revealed......

Good luck
PeterC Message was edited by: Peter.C

[deleted_user]

Hi Peter,

Thanks for your quick response. I still have the same issue. Find the log below:

9 ODS HTML FILE="C:\NEW.XLS";
NOTE: Writing HTML Body file: C:\NEW.XLS
ERROR: Invalid file, C:\NEW.XLS.
ERROR: Invalid file, C:\NEW.XLS.
ERROR: Invalid file, C:\NEW.XLS.
WARNING: No body file. HTML output will not be created.
10 PROC PRINT DATA=SASHELP.CLASS;
11 RUN;

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The PROCEDURE PRINT printed page 1.
NOTE: The PROCEDURE PRINT used 0.01 CPU seconds and 17046K.
NOTE: The address space has used a maximum of 644K below the line and 18228K above the line.
12 ODS HTML CLOSE;
ERROR: Errors printed on page 1.
+ERROR: Errors printed on page 1.
+ERROR: Errors printed on page 1.

Kindly suggest.

Thanks,
Mee

[Peter_C]

have you the security permissions to write in that path?

[deleted_user]

Yes. I am able to create files in the C Drive.

[sbb]

But are you able to write to the ROOT of the C:\ drive? Suggest you attempt to create a text document (or an Excel XLS file for grins). This should not be too difficult to figure out.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Hi Scott,

I am able to create a text or excel file and input some data and save it it the ROOT C:\ drive. But when i run the job in mainframes, i get the error.

Thanks,
Mee

[sbb]

"Run the job in the mainframe"? This statement indicates that you are attempting to submit/execute a batch SAS job with code that directly references your Windows C:\ drive in that batch job? If this is the case, the two are not compatible.

Scott Barry
SBBWorks, Inc.

[deleted_user]

Is it not possible to create Excel files using batch SAS jobs? Kindly suggest.

[Cynthia_sas]

Hi:
Adding my .02 to the mix for clarification purposes.

When you use ODS to create files, you are NOT creating true, binary Excel files. You are creating Markup Language files -- either HTML markup, comma separated markup or XML markup -- ASCII text files -- that Excel knows how to open and render.

When you create an ODS HTML file, for example, you would normally give that file a file extension of .HTML. However, for convenience purposes, and on Windows only, many people do this:

[pre]
ods html file='c:\temp\myfile.xls';
[/pre]

and give their HTML file a .XLS extension for the convenience of "fooling" the Windows registry into opening Excel as the result of a double-click action (instead of launching the browser).

So, that's the long explanation for the answer to your last question (which has 2 parts). Yes, it is possible to create an ODS HTML file on the mainframe using SAS batch jobs.

However, most mainframes do not have a direct connection to YOUR personal C drive. Many times, you either write to the mainframe file system and then FTP the resulting file to a Windows platform (where the FTP process allows you to name the file appropriately). Or you use SAS with the FILENAME FTP method and a remote submit to get the HTML file from the mainframe to Windows. Or you write to an AIX region on the mainframe and then access the AIX region from your Windows machine via mapping. Or....use use NDM to move files. Or...I'm sure there are many different methods to get files from one Platform to another. But directly writing from the mainframe to your C drive is not one of them.

Depending on the naming conventions at your site, I would expect your SAS job to look like this (approximately) as a non-JCL way to create a file for FTP:

[pre]
filename forExcel 'hqczz.class.tempfile.myfile';
ods html file=forExcel rs=none;
[/pre]

On our system, mainframe datasets have a 4 or 5-part name. Then, after ODS HTML creates an HTML file, I can FTP that file down to my C: drive and in the process of FTP, I can rename the file myfile.xls or myfile.html.

You will note that I've added "RS=NONE" to the job invocation. That's because in a cross platform environment, you will want to make sure that "universal" carriage return/line feed characters are used and that your HTML lines do not wrap awkwardly.

Mentioning that you were creating files on a mainframe is a critical piece of information -- generally, you cannot write directly to a Windows file system from your mainframe. If you have SAS/Connect and are doing RSUBMIT, you may have other choices. If you have SAS Enterprise Guide, again...other choices.

Perhaps in this instance a more experienced programmer at your shop can help you figure out how your company does FTP (using CLIST, using NDM, using line commands, using SAS), what your naming conventions are for mainframe data sets and dataset allocation and give you some pointers on how other folks move files from the mainframe to their local systems. You can also call Tech Support for some help in this regard. They will have a list of questions to ask about your mainframe configuration (are you using TSO, WYLBUR, ROSCOE, PanValet, ISPF, etc) and will help you figure out the correct job submission method.

cynthia

[deleted_user]

Hi Cynthia,

Thanks for your response. I atlast know how to proceed on with this. I go by your suggestion of creating the excel in mainframe file system and then FTP to Windows. Or i am also thinking of sending it via email too. That will solve my requirement now.

Moving to the next level, i want to know if its possible to run a VB Excel Macro using SAS? The VB Excel Macro i have, just formats the excel report. It will be simple if i am able to run the macro by just submitting a SAS program in mainframe. Else should i use SAS to format the excel file similar to what the macro does? (using ODS tagset.excelxp or ODS HTML).

Kindly let me know your thoughts.

Thanks once again for making me clear what i am doing.

Regards,
Mee

[Cynthia_sas]

Hi:
I'm glad things are a bit clearer.

Now, I'm going to be something of a curmudgeon. You are NOT, NOT, NOT creating Excel files on the mainframe. You are creating HTML files that Excel knows how to open and render. I know, it seems like a meaningless distinction -- what is the difference between an "excel file" and a "file that excel knows how to open" -- who cares, right?? Why do I hammer on this point. Well, it's precisely for the reason you asked your question.

What -else- can you do with ODS vis-a-vis how the file is opened and treated by Excel. Can you supply the name of an Excel macro? Can you alter or password protect the spreadsheet? Can you show or hide gridlines? For ODS HTML files that you create, the answer is that you CANNOT do anything with ODS that "touches" Excel pulldown menus, like password protecting the spreadsheet or show or hide gridlines. (Although with TAGSETS.EXCELXP, the XML has many more ways to impact print formatting than the ODS HTML ever could... see this paper: http://www.nesug.org/Proceedings/nesug08/ap/ap06.pdf -- which shows what you can do exclusively with TAGSETS.EXCELXP that is NOT possible with ODS HTML.)

I'm going to repeat this from my previous posting ....just because you give your file a .XLS file extension this does not make the file (created by ODS) a "true binary Excel file". After you create your file with ODS HTML and FTP the file to your windows machine, open the file with Notepad....you will see HTML tags. Then open the file with Excel -- you will see the HTML table rendered in spreadsheet form....but using the colors and fonts from the style template (if you use ODS MSOFFICE2K, instead of ODS HTML).

The only way to create "true binary Excel files" with SAS is to use PROC EXPORT or the SAS LIBNAME engine. The downside of using either of these techniques are that 1) you need a SAS dataset to use with either of these methods and 2) there are no colors or fonts or formatting with either of these methods.

So the short answer to your question -- you CANNOT easily specify an Excel macro to use with ODS HTML files. Because they are HTML -- they are not binary Excel files. (And, even if you used PROC EXPORT or the LIBNAME engine, I don't believe there's a way to provide an Excel macro that should be used with the binary spreadsheet.)

If you used the MSOFFICE2K destination and -IF- you changed the tagset template used to generate the MSOFFICE2K HTML you MIGHT be able to specify an Excel macro to load when the HTML file was opened. But for a new ODS user -- you would be taking on a huge task -- to understand how to alter the MSOFFICE2K HTML markup generated by ODS to invoke the Excel macro. For example, take a look at this paper
http://www2.sas.com/proceedings/sugi28/012-28.pdf (You reallly have to understand EVERYTHING in this paper -- before you get to page 8, where there's an example of specifying the Excel macro to load when the HTML page renders.)

I am curious why you chose ODS HTML to create your output instead of the ODS MSOFFICE2K destination (which is Microsoft HTML) or ODS TAGSETS.EXCELXP (which is Microsoft Spreadsheet Markup Language XML) -- either of those last destinations was DESIGNED to work better with Microsoft Office than ODS HTML. For one thing, files created with ODS HTML do not have their style formatting respected by Excel; whereas files created with ODS MSOFFICE2K or ODS TAGSETS.EXCELXP -DO- have their style formatting (supplied by ODS) respected by Excel. In my mind, a far easier thing to do would be to use ODS to format your output instead of using an Excel macro....depending on what you are doing with your formatting. There are many papers that show the use of STYLE= option changes to impact your output when it is rendered by the destination -- any destination -- not just Excel. But that's another topic.

At any rate -- enough for now....and more for you to think and read about.

cynthia