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
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.
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.
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.
Wanted to get the same appearance like when a html file is wrotten in an Excel sheet.
RG
Apply html blue style.
@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.
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.
TAGSETS.EXCELXP will also work.
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;
@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.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.
Lock in the best rate now before the price increases on April 1.
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.
Ready to level-up your skills? Choose your own adventure.