writing to separate excel sheets in an excel file using separate proc print commands

[emaguin]

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

[andreas_lds]

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]

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 · Accepted solution]

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]

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]

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]

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]

?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]

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.