topic Re: export 13 digit number to excel in SAS Programming

export 13 digit number to excel

tomrvincent — Thu, 12 Jul 2018 16:52:30 GMT

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

Re: export 13 digit number to excel

ballardw — Thu, 12 Jul 2018 17:54:17 GMT

How are you currently "exporting" the values.

And have you tried making the column wider in Excel?

Re: export 13 digit number to excel

tomrvincent — Thu, 12 Jul 2018 19:52:51 GMT

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.

Re: export 13 digit number to excel

ballardw — Thu, 12 Jul 2018 22:52:45 GMT

@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

Re: export 13 digit number to excel

tomrvincent — Fri, 13 Jul 2018 13:58:20 GMT

I tried using ods excel but it blows out of memory, so I'm just using proc export.

Re: export 13 digit number to excel

tomrvincent — Fri, 13 Jul 2018 14:39:31 GMT

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

Re: export 13 digit number to excel

ballardw — Fri, 13 Jul 2018 17:48:04 GMT

@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;

Re: export 13 digit number to excel

tomrvincent — Fri, 13 Jul 2018 18:05:44 GMT

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.