BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

12 REPLIES 12
ballardw
Super User

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
Obsidian | Level 7

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
Super User

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
Obsidian | Level 7

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

Cynthia_sas
SAS Super FREQ
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
Obsidian | Level 7

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
SAS Super FREQ

Hi:

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

start_at.png

 

  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
Obsidian | Level 7
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
SAS Super FREQ
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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1735 views
  • 1 like
  • 4 in conversation