BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
emaguin
Quartz | Level 8

I've been looking online and i've seen the ods "oxford english language dictionary" excel documentation but i don't know enough to really use it. What i want to do is this:

 

*   sheet 1;

proc contents data=file1; set=contents (keep=varnum name);

proc sort data=contents; by varnum;

ods excel file=varlistf1;

proc print data=contents;

run;

ods excel close;

 

repeat for file2-10, writing to sheets 2-10;

 

I know there is a sheet name (or sheetname) subcommand to name the sheets and i'd like to do that. But, i'll just bet there's a problem because, i'll bet, each 'write' for file1-10 goes to sheet1 and overwrites whatever is there. 

 

Thanks, Gene Maguin

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

If you place all of your Proc Print calls within the same Ods excel / ods excel close "sandwich" each proc print will be on a separate sheet:

Example:

ods excel file= outfile; 

proc print data=sashelp.class;
run;

proc print data=sashelp.class;
run;
ods excel close;

assuming outfile is a legitimate file reference will print the same data set twice, once on each of two sheets.

 

If you have 20 proc prints then you should get 20 sheets.

View solution in original post

8 REPLIES 8
andreas_lds
Jade | Level 19

Sorry, but i hardly understand what you are trying to do.

 

If you want the output of multiple procedure on a single worksheet, use sheet_interval = "none" to disable the default behaviour of using a new worksheet for each procedure. If you need multiple files with multiple sheets, you won't be able to achieve this by using ods excel alone, but you will find some hints and code by using the search-function of the community - this topic has been discussed recently.

emaguin
Quartz | Level 8
Thank you for your reply.

Sorry, bad explanation. I have little experience with any part of SAS and none with the intracacies (sp?) of ods and excel. Please tell me how you understand "multiple files with multiple sheets". I am not sure exactly what that phrase means. Does "multiple files" refer to the input to proc print or to the output (excel) files? What is the correspondence between "files" and "sheets"?

And, let's turn the question around. Where did my post become not-understandable to you?

You say this has been discussed recently. Do you recall key phrases in the post that i can search on or a date range for the discussion? I've searched listservs before, which this is not, and those search elements are helpful.

Gene Maguin


ballardw
Super User

If you place all of your Proc Print calls within the same Ods excel / ods excel close "sandwich" each proc print will be on a separate sheet:

Example:

ods excel file= outfile; 

proc print data=sashelp.class;
run;

proc print data=sashelp.class;
run;
ods excel close;

assuming outfile is a legitimate file reference will print the same data set twice, once on each of two sheets.

 

If you have 20 proc prints then you should get 20 sheets.

Reeza
Super User

I know there is a sheet name (or sheetname) subcommand to name the sheets and i'd like to do that. But, i'll just bet there's a problem because, i'll bet, each 'write' for file1-10 goes to sheet1 and overwrites whatever is there. 

 

Can you please clarify what you mean here? Are you betting that there's problems or are you actually encountering errors? What's an actual use case for you?

 

You can generate multiple worksheets per workbook by using BY group processing automatically, but if you want multiple files you need to either create a macro or have unique code to create each file. 

 

 

emaguin
Quartz | Level 8
I'm only thinking there will be problems. I do not know that there are or will be.


In the link i was looking at yesterday, i saw an example using a by keyword and that operation makes natural sense to me.


Gene Maguin

Reeza
Super User
Ok, well it's very vague on what your question is then or what you need help with. We don't know what you have, what you're trying to do, what you've tried, what works or doesn't work. Right now it seems like you're fishing for an approach or resources regarding ODS?

I'm also very unsure of what "ods "oxford english language dictionary" excel documentation" refers to. The SAS documentation is pretty thorough and has plenty of examples. If you clarify what you want/need someone should be able to point you to what you need.

Generic examples - user written paper:
https://www.lexjansen.com/wuss/2018/153_Final_Paper_PDF.pdf

Documentation reference:
https://documentation.sas.com/?docsetId=odsug&docsetTarget=p09n5pw9ol0897n1qe04zeur27rv.htm&docsetVe...
emaguin
Quartz | Level 8
?Thank you for the user written paper.

Oxford ... was a comment on the overwhelming complexity and quantity of documentation.

I'm extremely grateful to the help that you and your fellow responders are giving me.
Gene Maguin
SuzanneDorinski
Lapis Lazuli | Level 10

Quick example that produces 2 files from the SASHELP.BASEBALL dataset:

 

%let path=/folders/myfolders/ODS Excel examples/;

ods excel file="&path.one_sheet_per_city.xlsx"
  options(sheet_name="#byval(team)"
          index='on'
          embedded_titles='on'
          frozen_headers='on');
  
proc sort data=sashelp.baseball   
          out=sorted_by_team;
  by team;
run;

options nobyline;

proc print data=sorted_by_team noobs;
  title "#byval(team) players in 1986";
  by team;
  pageby team;
run;

ods excel close;

proc sort data=sorted_by_team
          out=sorted_by_league_division;
  by league division name;
run;

ods excel file="&path.one_sheet_per_league_and_division.xlsx"
  options(sheet_name="#byval(league) #byval(division)"
          index='off'
          embedded_titles='on'
          frozen_headers='on'
          start_at='2,2'
          autofilter='all');
          
proc print data=sorted_by_league_division noobs;
  title "Players listed in #byval(league) #byval(division) in 1986";
  by league division;
  pageby division;
run;

ods excel close;

I'd suggest searching on "ods excel Chevell Parker" to get a bunch of papers with examples, and a few videos of his talks.  

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 2157 views
  • 2 likes
  • 5 in conversation