ods excel

[sunilreddy]

Hi,

I am using below excelxp command to export the data into excel.

*ProcessBody;

ods listing close;

data _null_;

rc= stpsrv_header('Content-type', 'application/vnd.ms-excel');

rc= stpsrv_header('Content-disposition', 'attachment;

filename= report1.xls');

run;

%stpbegin;

ods tagsets.ExcelXP options(sheet_name="Consensus REG");

  Proc Print data=&_INPUT. noobs width=full;

  run;

ods tagsets.ExcelXP close;

%stpend;

I am getting background colour is greyed and its opening .htm format with excel

CYCLE	FORECAST_PERIOD	PROCESSED_DTTM
01-Dec-11	01-Dec-11	12AUG2012:22:29:41

Could you please help me on below issues:

1)      1) How to change the greyed background colour to plain

2)      2) How to open a ods file with .xls  instead of .htm in .xls

3)      3) SAS Date value is converting from ddmmmyyyy to dd-mm-yy format. How to change this.

[Reeza]

ods tagsets produce XML files so they aren't native SAS files, though you can get the extension to be xls.

Try adding in the file=, the style= options and a format to the date to change your settings.

ods tagsets.ExcelXP file='C:\temp\my_sample.XLS' style=journal

      options(sheet_name="Consensus REG");

  Proc Print data=&_INPUT. noobs width=full;

format forecastperiod ddmmmyy10.;*not sure what fmt you want;

  run;

ods tagsets.ExcelXP close;

[Cynthia_sas]

Hi:

  First, my usual curmudgeonly rant:

When you use ODS techniques to create output that can be opened with Excel, you are NOT, NOT, NOT creating true, binary .XLS files and you are NOT, NOT, NOT technically doing an EXPORT. Only PROC EXPORT or the LIBNAME statement for Excel can create true, Excel proprietary format files. When you use ODS, you are either creating CSV, or HTML or XML ASCII text files that Excel knows how to open and render. So what you create in your stored process is just ASCII text, not proprietary Excel binary file structure.

  When you use ODS TAGSETS.EXCELXP, you are creating Spreadsheet Markup Language XML, as defined in Office 2003 (and NOT HTML).

So, to comment on your questions:

1) use a different style template in your ODS invocation (something like style=sasweb or style=htmlblue) Do be aware, though that some client applications (like WRS or PPT) might not use this style. Or, use style=overrides in your PROC PRINT code, such as

proc print data=xxx.yyy style(data)={background=white};

2) see the first comments above -- first you are creating XML, not HTML and second, it is not a true binary Excel file when you use ODS. If you don't like the message that you get from Excel when it opens the file, then change the file extension to .XML and not .XLS That may not stop Excel from complaining when it opens the XML file, but depending on how you are running your stored process, you have little choice with how Excel treats the XML.

3) This is a "feature" of Excel -- it likes to ignore your SAS format. Generally, however, I find in SAS 9.3 that if I use a format statement, like:

format mydatevar date9.; that TAGSETS.EXCELXP sends the correct Microsoft format to Excel. If you are finding this is not the case, you may need to explicitly send a Microsoft format to Excel using the TAGATTR style attribute. If you search the forums, there have been many previous postings about using TAGATTR with TAGSETS.EXCELXP.

I am not on a system where I can work with stored processes. If you have specific questions, you might want to work with Tech Support on this, because they can look at the client application you are using to execute the stored process and help you with stored process specifics. Also, there are many previous postings on using TAGSETS.EXCELXP in the Stored Process Forum postings.

cynthia