Date:         Thu, 5 Feb 2004 10:19:28 -0500
Reply-To:     "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Chang Y. Chung" <chang_y_chung@HOTMAIL.COM>
Subject:      Re: ODS HTML to Excel
Comments: To: whitelaw70@HOTMAIL.COM

On Thu, 5 Feb 2004 05:43:40 -0800, Sheila Whitelaw
<whitelaw70@HOTMAIL.COM> wrote:

>Hi, I generated an output using ODS HTML and then opened the HTML file
>in Excel. Excel defaulted the field as numeric and somehow set the
>last two digits as zeros. Does anyone know how to fix it? Following is
>my code and the outputs.
>
>Program:
>data test;
>infile cards;
>input var1 $17.;
>cards;
>10003010960234567
>10030109602345670
>10301096023456701
>13010960234567012
>30109602345670123
>11096023456701234
>10960234567012345
>19602345670123456
>96023456701234567
>;
>run;
>ods html file='test.html';
>proc print data=test;
>run;
>ods html close;
>run;
>
>LST output:
>Obs          var1
>
> 1     10003010960234567
> 2     10030109602345670
> 3     10301096023456701
> 4     13010960234567012
> 5     30109602345670123
> 6     11096023456701234
> 7     10960234567012345
> 8     19602345670123456
> 9     96023456701234567
>
>HTML output viewed in Excel
>Obs     var1
>1       10003010960234500
>2       10030109602345600
>3       10301096023456700
>4       13010960234567000
>5       30109602345670100
>6       11096023456701200
>7       10960234567012300
>8       19602345670123400
>9       96023456701234500
>
>Any help would be appreciated.

Hi, Sheila,

Short of generating Excel workbook directly, there seems to be few quick-
and-easy options. I have a really ugly hack: The following puts formulas
instead of values. Once in Excel, you can select all of them. copy and
pasteSpecial (values) to convert all of them into strings... :-)

Cheers,
Chang

data test;
length var1 $23;
input var1;
var1 = "=t(""" || trimn(var1) || """)";
cards;
10003010960234567
10030109602345670
10301096023456701
13010960234567012
30109602345670123
11096023456701234
10960234567012345
19602345670123456
96023456701234567
;
run;
ods html file='test.xls';
proc print data=test;
run;
ods html close;
run;