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.
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
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
OR
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: