Formatting Excel Columns

[StaticFX]

I am stuck.. I am trying to export to an excel file (which works fine)

but i cannot get the text column to remain as text.
instead of 7568626110106 i get 7.56863E+12

I found one solution but it forces EVERYTHING to text.. no good since then the number columns dont add.

ODS CHTML FILE="VRFD_&FILENAME..xls" HEADTEXT="<STYLE> TD {MSO-NUMBER-FORMAT:\@}</STYLE>" PATH="/home/xxxxxxxx/check_output/";
TITLE;
PROC PRINT DATA=POUT NOOBS;
RUN;
ODS CHTML CLOSE;

I cant seem to get it to work correctly.

thanks!

 

[dblan]

That field is formatted as a character in the SAS dataset, correct? (i.e. - $20 or something similar)

I don't use ODS very much so I don't know if that is what is causing that, but I have used proc export and the Excel libname engine with good success to export long numbers that have been formatted as a character.

Dave

 

[StaticFX]

I actually figured it out! Havent tried the libname export..(and yes, they are formatted text in the dataset)

Creating an html output, and using the htmlstyle on the text columns ensures they come out as text!!!!!
Took HOURS of testing and tweaking and testing last night lol. in case anyone is interested:

http://www2.sas.com/proceedings/sugi28/012-28.pdf

has the code that i based it on... a few tweaks and it worked!

ODS HTML FILE="VRFD_&FILENAME..xls" PATH="/home/xxxxxxx/check_output/";

TITLE;

PROC PRINT DATA=POUT NOOBS;

var NETWORK_COST;

var NC_Val;

var DOCUMENT_NUM / style(column)={htmlstyle="MSO-NUMBER-FORMAT:\@"};

var SUPPLIER;

var SUP_SITE / style(column)={htmlstyle="MSO-NUMBER-FORMAT:\@"};

var SUPPBANK / style(column)={htmlstyle="MSO-NUMBER-FORMAT:\@"};

var PAY;

var DOCAMT;

var SEQNUM;

var INV_NUM / style(column)={htmlstyle="MSO-NUMBER-FORMAT:\@"};

var INVOICE_DT;

var BAN / style(column)={htmlstyle="MSO-NUMBER-FORMAT:\@"};

var GROSSAMT;

var DISCAMT;

var PAYAMT;

var IPAY;

RUN;

ODS HTML CLOSE;