topic Re: How can I prevent Character data from being converted to numeric in Excel? in ODS and Base Reporting

How can I prevent Character data from being converted to numeric in Excel?

amritasingh — Sun, 08 Mar 2020 18:51:05 GMT

Hi,

I have some numeric data that has more than 16 significant decimal places. So I read them in as character variables and am using ODS Excel with Proc Print to write out a report. The character variables print fine in the listing with all the significant decimals. However in the excel output file they get converted to numeric and get rounded. Is there any way to avoid that and print out the full number as text in the Excel file?

Code:

data rpt;
infile in dsd dlm=',' truncover firstobs=2;
input
minscore :$char25.
maxscore :$char25.
;

ODS excel FILE="\\mcksas4\amrita\Files\Model Segment Counts.xlsx" STYLE=excel;

proc print data=rpt noobs;
var minscore maxscore;
format minscore maxscore $25.;
ods excel close;

Sample input data:

Minscore Maxscore

0.2750573833205815 0.3476960152906469

Proc print output in listing:

Minimum Score Maximum Score

0.2750573833205815 0.3476960152906469

Proc print output in Excel file:

Minimum Score Maximum Score
0.275057383 0.347696015

Thank you,

Amrita

Re: How can I prevent Character data from being converted to numeric in Excel?

Patrick — Mon, 09 Mar 2020 07:20:38 GMT

Below should work. Based on here.

ODS excel FILE="\\mcksas4\amrita\Files\Model Segment Counts.xlsx"  STYLE=excel;

proc print data=rpt noobs;
  var minscore / style(data)={tagattr="type:String"};
  var maxscore / style(data)={tagattr="type:String"};
run;

ods excel close;

Re: How can I prevent Character data from being converted to numeric in Excel?

amritasingh — Mon, 09 Mar 2020 14:31:33 GMT

Thank you so much Patrick!!! This worked perfectly!! It's much appreciated!
Amrita