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

Hey,


I use filename and proc print statements to make exports, like this.

Is it possible to get have1 and have2 in the same excel file but in different sheets ?

 

filename _temp_ "...folder\excel.xls";

ods noresults;

ods listing close;

ods html file=_temp_ rs=none style=minimal;

proc print data=Work.'have1'N label noobs;run;

proc print data=Work.'have2'N label noobs;run;

 

Regards

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

This:

"File.xml"

Should be replaced with the path to file and filename you want.

Do note that I call the file XML.  It is important that the file extension shows what the file contains not what you want to use it for.  Much like in your first post you create HTML file called xls, it is not an XLS file, it ia HTML file and should be called such to avoid confusion.

View solution in original post

11 REPLIES 11
Kurt_Bremser
Super User

Ouch. You are NOT creating an Excel file, but a HTML that disguises as xls. Excel will automatically convert when you open that, but you can never have separate spreadsheets in there, as HTML does not provide that.

 

You can use ODS TAGSETS.EXCELXP to create Excel-compatible XML, which allows setting sheet destinations before separate outputs.

Do a google search for "ODS TAGSETS.EXCELXP" to find the relevant documentation (like https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html).

 

Another way for storing raw data is libname XLSX, which opens a Excel workbooik as if it was a SAS library, and the sheets appear as datasets.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

There are several methods, depeding on what you have installed.  Simplest:

proc export data=have1 file="....xls";
  sheet="xyz1";
run
proc export data=have2 file="....xls";
  sheet="xyz2";
run

Or to create tagset output (more style options, compatible with < 9.4):

ods tagsets.excelxp file="...xml" options(sheet_name="xyz1");
proc report data=have1;
run;
ods tagsets.excelxp options(sheet_name="xyz2");
proc report...;
run;
ods tagsets.excelxp close;

Or 9.4 Excel engine:

http://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/

 

Without some information on what you have, what you need, what formatting etc. its hard to say which is most approriate.

DoumbiaS
Quartz | Level 8

Wanted to get the same appearance like when a html file is wrotten in an Excel sheet.  

RG

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Apply html blue style.

Reeza
Super User

@DoumbiaS wrote:

Wanted to get the same appearance like when a html file is wrotten in an Excel sheet.  

RG


@DoumbiaS Include more details in your posts please. 

 

Use ODS EXCEL instead with the style set to HTMLBlue. It will be a native XLSX file and have the style you want. This works if you're on SAS 9.4+

 

Otherwise you're stuck with PROC EXPORT if you want an XLSX file. 


When referencing papers, please note the dates. Things from a decade ago, may not be applicable today.

DoumbiaS
Quartz | Level 8

Thank you. In the first post I illustrate it. It was how an html file is write in an Excel sheet.

But as it cannot be used to get many sheets in the same Excel file, so I looking for another one.

I don't have SAS 9.4 to use ODS EXCEL and the HTMLBlue style.

 

 

 

 

Reeza
Super User

TAGSETS.EXCELXP will also work.

 

 

DoumbiaS
Quartz | Level 8

When i use this, I cannot find the resulting created file to see how it looks.

 

ods tagsets.excelxp file="File.xml" options(sheet_name="xyz1");
proc report data=have1;
run;
ods tagsets.excelxp options(sheet_name="xyz2");
proc report data=have2;
run;
ods tagsets.excelxp close;
Kurt_Bremser
Super User

@DoumbiaS wrote:

When i use this, I cannot find the resulting created file to see how it looks.

 

ods tagsets.excelxp file="File.xml" options(sheet_name="xyz1");
proc report data=have1;
run;
ods tagsets.excelxp options(sheet_name="xyz2");
proc report data=have2;
run;
ods tagsets.excelxp close;

Always make it a point to use absolute filenames that start at the root (UNIX) or on a drive letter/resource name (Windows). Simple filenames will (try to) create files in the current working directory of the SAS process, which is usually somewhere in the SAS configuration tree.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

This:

"File.xml"

Should be replaced with the path to file and filename you want.

Do note that I call the file XML.  It is important that the file extension shows what the file contains not what you want to use it for.  Much like in your first post you create HTML file called xls, it is not an XLS file, it ia HTML file and should be called such to avoid confusion.

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
  • 11 replies
  • 3285 views
  • 2 likes
  • 4 in conversation