topic Re: Problem: ods html and proc export in SAS Procedures

Problem: ods html and proc export

MariaJuul — Sun, 27 May 2012 13:15:48 GMT

Hi Forum

I have a huge problem: I can't get my SAS-files into Excel.

I have tried the following code (with "file" as well as "body" in the statement)

ODS html FILE= "C:\Users\Ejer\Documents\Polit\4. semester\Økonometri B\Øvelser\Uge 18\Test\Uge 18\tabel 1 NY2.xls";

proc print data=tabel_1;

format _numeric_ commax10.2;

run;

html close;

But get the following error:

942 ods html FILE= "C:\Users\Ejer\Documents\Polit\4. semester\Økonometri B\Øvelser\Uge 18\Test\Uge 18\tabel 1 NY2.xls";

NOTE: Writing HTML Body file: C:\Users\Ejer\Documents\Polit\4. semester\Økonometri B\Øvelser\Uge 18\Test\Uge 18\tabel 1 NY2.xls

ERROR: A component of C:\Users\Ejer\Dropbox\UNI (1)\Fælles\Eksamen økonometri\SAS\Tabeller og

figurer\C:\Users\Ejer\Documents\Polit\4. semester\Økonometri B\Øvelser\Uge 18\Test\Uge 18\tabel 1 NY2.xls is not

a directory.

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

943 proc print data=tabel_1;

944 format _numeric_ commax10.2;

945 run;

NOTE: There were 15 observations read from the data set WORK.TABEL_1.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

946

947 ods html close;

Furthermore I have tried to use PROC EXPORT, but it won't work either:

proc export data=tabel_1_VARIABLE

outfile='C:\Users\Ejer\Documents\Polit\4. semester\Økonometri B\Øvelser\Uge 18\Tabel 1.xls'

DBMS=xls replace;

Sheet='Variable';

run;

quit;

The file is created in, but when I try to open it a box tells me that "it can't read the file" and therefore there's no table in it, when I open it.

I really hope you are able to help me!

Thank you in advance.

Re: Problem: ods html and proc export

MariaJuul — Sun, 27 May 2012 13:57:31 GMT

I have just noticed that in the error statement SAS writes the path to the directory twice even though I've only written it once in my program. I guess that might be a part of the problem, but how do I handle that?

If it's of any importance, I use Windows 7, 64 bit and SAS 9.2

Re: Problem: ods html and proc export

Cynthia_sas — Sun, 27 May 2012 14:50:06 GMT

Hi:

The issue is not FILE= vs BODY= -- they are aliases for each other and are not the problem. It seems to me that you are experiencing this problem:
http://support.sas.com/kb/15/046.html

where your default location for HTML output has been modified to your C:\users directory and so that information is now "prepended" to your FILE= path. This results in the error message that you see and in the full PATH appearing twice in your LOG.

You have 2 choices for a solution:

1) (probably the simplest) use ONLY the file name in the FILE= option:

ods html file='myfile.html'; or ods html file='myfile.xls'; and let the Registry setting provide the path location

2) use the PATH= option to explicitly override only the directory path that is coming from the SAS registry (note that the directory you specify must exist):

ods html path='c:\temp' (url=none)

file='test_to_excel.xls' style=sasweb;

proc print data=sashelp.class;

run;

ods _all_ close;

So, either way, one of those options should work. If you continue to have issues, you might want to work with Tech Support.

FYI -- PROC EXPORT and ODS create very different types of files for Excel to open. PROC EXPORT creates a true, binary Excel proprietary format file. On the other hand, ODS HTML (or any of the HTML destinations) creates an ASCII text file in HTML format which Excel knows how to open. You are merely "fooling" the Windows registry into launching Excel when you give the HTML file a .XLS extension. Also, ODS HTML creates HTML 4.0 compliant tags. Microsoft Excel is not as "happy" to open these type of HTML tags as it is with other types of HTML, either of these 2 destinations creates HTML output that Microsoft is generally "happier" to open: ODS HTML3 (produces HTML 3.2 tags) or ODS MSOFFICE2K (produces Microsoft "flavor" of HTML) Also, if you do NOT care about colors and fonts that come from the style template, you can also use ODS CSV to create a comma separated file for Excel.

cynthia