exporting xlsx files to Google drive

[Cynthia_sas]

Hi, Reeza:
In my experience, ODS Excel is generally very, very good with Dates and will respect the SAS format for date/time values. I still find that I usually need TAGATTR for long numbers, leading zeroes, percents and number of decimal places.

cynthia

[GreggB]

Yes. ODS handled it with no problem.

[GreggB]

My comments about the 12-digit IDs may have been misleading. There's no problem on the SAS side. The corruption occurs when other users open the csv file. This is no longer a problem since I learned to use ODS to export an xlsx file.

[Cynthia_sas]

Hi: Just to further clarify that I knew there was no problem on the SAS side of things. SAS doesn't really care how big a number it has to display as long as your SAS format is big enough to display the number with all the punctuation (commas, decimal points, etc). But Microsoft Excel does not always respect big numbers and past a certain size, has a tendency to replace significant digits in the number with zeroes.

Sometimes you can work around this issue by telling Microsoft that the number is a text string, but that doesn't always work. Usually, in cases where Excel does not use the SAS format, as written to the CSV file, you need to "move up" to use ODS to create more than a CSV file -- both ODS TAGSETS.EXCELXP and ODS EXCEL give you the ability (via TAGATTR) to specify a Microsoft format instruction for Excel to use when it opens the file you create. This is a key point -- you are NOT sending a SAS format with TAGATTR and ODS -- you are sending a Microsoft format.

As Reeza suggested, there are some SAS formats that the TAGSET destinations will automatically convert to Microsoft formats for you -- that was the posting exchange we had about date formats. I find the TAGATTR method of sending Microsoft formats to be very reliable, when the file is opened with Excel. I am glad to hear that Google sheets also supports the Microsoft format.

cynthia