DATA Step, Macro, Functions and more

Filename statement

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

Filename statement

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


Accepted Solutions
Solution
‎08-24-2017 01:00 PM
Super User
Super User
Posts: 9,416

Re: Filename statement

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


All Replies
Super User
Posts: 9,890

Re: Filename statement

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 62

Re: Filename statement

Posted in reply to KurtBremser
Super User
Super User
Posts: 9,416

Re: Filename statement

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.

Contributor
Posts: 62

Re: Filename statement

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

RG

Super User
Super User
Posts: 9,416

Re: Filename statement

Apply html blue style.

Super User
Posts: 23,296

Re: Filename statement


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.

Contributor
Posts: 62

Re: Filename statement

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.

 

 

 

 

Super User
Posts: 23,296

Re: Filename statement

TAGSETS.EXCELXP will also work.

 

 

Contributor
Posts: 62

Re: Filename statement

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;
Super User
Posts: 9,890

Re: Filename statement


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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎08-24-2017 01:00 PM
Super User
Super User
Posts: 9,416

Re: Filename statement

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 324 views
  • 2 likes
  • 4 in conversation