topic Filename statement in SAS Programming

Filename statement

DoumbiaS — Wed, 23 Aug 2017 11:50:47 GMT

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

Re: Filename statement

Kurt_Bremser — Wed, 23 Aug 2017 12:01:39 GMT

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.

Re: Filename statement

RW9 — Wed, 23 Aug 2017 12:07:06 GMT

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.

Re: Filename statement

DoumbiaS — Wed, 23 Aug 2017 12:09:08 GMT

This one also:

http://support.sas.com/resources/papers/proceedings12/150-2012.pdf

Re: Filename statement

DoumbiaS — Wed, 23 Aug 2017 12:19:12 GMT

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

Re: Filename statement

RW9 — Wed, 23 Aug 2017 12:31:50 GMT

Apply html blue style.

Re: Filename statement

Reeza — Wed, 23 Aug 2017 15:16:57 GMT

@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.

Re: Filename statement

DoumbiaS — Wed, 23 Aug 2017 21:23:02 GMT

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.

Re: Filename statement

Reeza — Wed, 23 Aug 2017 21:31:18 GMT

TAGSETS.EXCELXP will also work.

Re: Filename statement

DoumbiaS — Thu, 24 Aug 2017 07:40:47 GMT

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;

Re: Filename statement

Kurt_Bremser — Thu, 24 Aug 2017 07:47:54 GMT

@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.

Re: Filename statement

RW9 — Thu, 24 Aug 2017 08:31:07 GMT

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.