can SAS 9.4 TS Level 1M1 on W32_7PROD platform run the code below

[ballardw · Accepted solution]

Microsoft documents are a security risk and many here will not open them from an unknown source and some are restricted from opening them by organization policy or security software.

Code is best shown by copying from the SAS editor (or other plain text editor NOT word processor as they will insert and modify code sometimes) and paste into a code box here opened using the forum's {I} menu icon.

[nnl3256]

Sorry, the purpose I used attached file is to show the log of SAS. I typed the code below and result was same. Does SAS application on my machine or window environment not support the code?

ods excel file="C:\RA\temp.xlsx" options(

start_at="3,3"

frozen_heads="5"

frozen_rowheaders="3"

autofilter="1-5"

sheet_name="class report"

row_repeat="2"

embedded_titles="yes");

title "Student Report";

proc print data=sashelp.class; run;

ods excel close;

[ballardw]

Look at the top of the window: There is an icon that looks like {I}

Click on it.

ods excel file="d:\temp.xlsx" options(
   start_at="3,3"
   frozen_headers="5"
   frozen_rowheaders="3"
   autofilter="1-5"
   sheet_name="class report"
   row_repeat="2"
   embedded_titles="yes");
title "Student Report";
proc print data=sashelp.class; run;
ods excel close;

Paste text, as above.

NO, your code as shown will not run. The option frozen_heads does not exist. Notice I corrected that to frozen_headers.

[nnl3256]

I corrected my code as you pointed, but the result was same after I ran it

[Cynthia_sas]

Hi:
I'm not sure what your question is. SASHELP.CLASS only has 19 rows, so specifying ROWREPEAT doesn't make sense, since nothing about SASHELP.CLASS will go over 1 page.

Have you checked the documentation for ODS EXCEL to see what sub-options are valid?

When debugging sub-options, I typically recommend running the program multiple times, starting with one sub-option and adding them one at a time, so you know exactly which sub-option you are having issues with.

cynthia

[nnl3256]

Cynthia, I am learning codes to generate ods excel file. When copied and ran sample code  to see how it works, I found the problem and couldn’t figure it out. As you recommended, I add sub-options 1 by 1 and finally nailed down the troublesome option - start_at="3,3". I will check the documentation for ODS EXCEL. Thanks

[Cynthia_sas]

Hi:

  That's odd, START_AT works fine for me. See below:

  I guess I would recommend trying EACH option separately. And then try the options in combination with each other. If START_AT isn't working in combination with the others, my guess is that there's something that the combination is making an error combination.

cynthia

...PS: As an additional comment in your sub-option list, you specify autofilter="1-5" but your REPORT has 6 columns because OBS is column 1, name is 2, sex is 3, age is 4, height is 5 and weight is 6. So with your code I would expect to see an autofilter on OBS, which doesn't make sense and NOT a filter on WEIGHT.

[nnl3256]

ods excel file="&excpath\StartAt.xlsx"
options (start_at='3,3');
proc print data=sashelp.class; run;
ods excel close;

I simplified code and ran it again. the problem still was there. see attached log. Thank you

[Cynthia_sas]

Sorry, I do not open Microsoft files. However, I am running on SAS 9.4 M3...so if you are getting any kind of error with ODS EXCEL, it might be related to the fact that you are running 9.4 M1.

I believe that ODS EXCEL was production in 9.4 M2 or M3. So you may have to upgrade your version to make the syntax work without errors.

cynthia

[nnl3256]

Good to know. Thanks

[nnl3256]

ods excel file="C:\RA\temp.xlsx" options(
start_at="3,3" 
frozen_heads="5"
frozen_rowheaders="3"
autofilter="1-5"
sheet_name="class report"
row_repeat="2"
embedded_titles="yes");
title "Student Report";
proc print data=sashelp.class; run;
ods excel close;

[RW9]

Nope, no version of SAS or any other language can run .docx files.  Perhaps consider some of the guidance given when posting a question.  Put the problem in the post, give some test data in the form of a datastep, show what the output should look, what you have tried etc.