DATA Step, Macro, Functions and more

export 13 digit number to excel

Reply
Regular Contributor
Posts: 217

export 13 digit number to excel

Anybody know how to export a 13 digit number to excel without it coming out in scientific notation? 

Super User
Posts: 13,583

Re: export 13 digit number to excel

Posted in reply to tomrvincent

How are you currently "exporting" the values.

And have you tried making the column wider in Excel?

Regular Contributor
Posts: 217

Re: export 13 digit number to excel

I'm trying not to do anything in excel manually (that would be too easy...plus I'm dealing with several dozen monthly files).

I wanted to simply send it as a CSV or text, but the customer is a moron who doesn't know how Excel works.

I just wondered if anyone had a quick fix for this nonsense.
Super User
Posts: 13,583

Re: export 13 digit number to excel

Posted in reply to tomrvincent

@tomrvincent wrote:
I'm trying not to do anything in excel manually (that would be too easy...plus I'm dealing with several dozen monthly files).

I wanted to simply send it as a CSV or text, but the customer is a moron who doesn't know how Excel works.

I just wondered if anyone had a quick fix for this nonsense.

That is part of why I asked HOW you are exporting the data. The ODS EXCEL supports an option Absolute_column_width. The following example sets all of the columns to a width of 16

 

ods excel file='footer.xlsx'    
          options(absolute_column_width='16');
proc print data=sashelp.class(obs=5);
run;
ods excel close;

Or to set width per column:

 

ods excel file='x:\footer.xlsx'    
          options(absolute_column_width='16,5,8,10,10' );
proc print data=sashelp.class(obs=5);
run;
ods excel close;

ods tagsets.excelxp creates excel readable XML with the same options if you don't have SAS/Access Interface to PC Files

 

Regular Contributor
Posts: 217

Re: export 13 digit number to excel

I tried using ods excel but it blows out of memory, so I'm just using proc export.
Regular Contributor
Posts: 217

Re: export 13 digit number to excel

Posted in reply to tomrvincent
tried it with 5 records and still get scinote in the resulting file. Width had no effect.
Super User
Posts: 13,583

Re: export 13 digit number to excel

Posted in reply to tomrvincent

@tomrvincent wrote:
tried it with 5 records and still get scinote in the resulting file. Width had no effect.

What is the actual format associated with the variable?

If you use Proc Print as a basic ODS generator then the format applies. If you have a format of best8., or just about anything with a less than 13, or possibly 14, width you'll get some form of truncation or rounding.

data _null_;
  x= 123456781234;
  put  x best8.;
  put x  best11.;

  put x best13.;
run;
Regular Contributor
Posts: 217

Re: export 13 digit number to excel

I certainly wouldn't use less than 13 with a 13 digit number. That doesn't make any sense at all.

I tried everything I could think of...settled on bestd13.
Ask a Question
Discussion stats
  • 7 replies
  • 104 views
  • 0 likes
  • 2 in conversation