STP text output in excel using Microsoft Add-in

[jplarios]

I have a stp that outputs in Excel. One type of character value that it outputs is MAR2011. Eventhough this is character , Excel transforms this into a date of Mar/20/2011. Is there a way to output an STP in text ? Or someway for the character value not to be converted into this Excel date?
code in STP:
proc sql;
select column_name label = '00'x /*no column headers*
from table A
quit;

[Cynthia_sas]

Hi:
The issue is that when you create CSV output, Excel does the -default- formatting for columns. So, even though you think that "Mar2011" is a character variable, Excel decides that it is a date and shows it to you as a date string.

If you would switch to HTML output with the SAS Add-in for Microsoft Office (with Excel), you can pass "mso-number-format" values from SAS to the Excel worksheet. This technique does NOT work with CSV returned from a stored process.

You can test out how to use the "mso-number-format" in a regular SAS session, by using the HTMLSTYLE attribute with a STYLE= override. Compare the Excel treatment for the 2 date fields in the CSV file versus the Excel treatment when I use the MSOFFICE2K destination (Microsoft "flavor" HTML).

Then, you can use the PROC PRINT code in your stored process. Instead of returning CSV output, change your SP result type to HTML. And, remember to remove the ODS "sandwich" code and use %STPBEGIN/%STPEND instead in your SP code.

cynthia
[pre]
data class;
set sashelp.class;
date = '01Mar2011'd;
chardate='Mar2011';
format date date9.;
run;

ods csv file='csvout.csv' ;
ods msoffice2k file='msoout.xls';

proc print data=class;
var name age height;
var chardate / style(data)={htmlstyle="mso-number-format:@"};
var date / style(data)={htmlstyle="mso-number-format:mm.dd.yyyy"};
run;
ods _all_ close;
[/pre]

[jplarios]

Cynthia,
Thank you for the response.
I tried this but it still , on html, forces a date into the value. Besides trying this, I guess I can have the user output in SAS Report and format as text and save as CSV.

jl

[Cynthia_sas]

Hi:
I don't know very much about Microsoft formats. However, when I "reverse-engineered" by creating a little spreadsheet with "Mar2001" formatted as Text in a cell and then saving the spreadsheet as an '.mht' file, I noticed that the Microsoft format had a slash in front "mso-number-format:\@".

You might try this:
[pre]
var chardate / style(data)={htmlstyle="mso-number-format:\@"};
[/pre]

and see whether that gives the output you want. Otherwise, you might wish to open a track with Tech Support for more help with your stored process and Microsoft formats.

cynthia

[jplarios]

Cynthia,
Cool. Thanks again for your help.

Juan