CODEWITHSUNDEEP
excelsas
Zebra
Zebra

Reputation: 139

SAS truncating while exporting

I have a SAS data set with the first column being customer account IDs with 20 digit. I need to write my output of the table to a excel file. when i write to excel file account ids are truncated and last few digits turn zero.

please help. i need to write in excel format and in text to column format.

Upvotes: 0

Views: 2813

Answers (2)

Chris J
Chris J

Reputation: 7769

You can use ODS HTML to export the data to Excel, use a .xls extension instead of .htm - the content is still HTML, but Excel interprets it into a nice table. You can then apply the usual ODS styles, titles, etc. should you wish.

Example :

data dummy ;
  do d = 1 to 20 ;
    longnum = put(ranuni(0)*(10**20),z20.) ;
    output ;
  end ;
run ;

ods html body="c:\temp\LongNumbers.xls" ;
proc report data=dummy nowd ;
  define longnum / style={tagattr="style='mso-number-format:""\@""'"} ;
run ;
ods html close ;

There are a lot more mso-number-format values which can be used :

  • Plain Text: mso-number-format:\@
  • Format a number to 2 decimal places: mso-number-format:"0\.00"
  • Comma separators with 2 decimal places: mso-number-format:\#\,\#\#0\.00
  • Date \ Time Formating:
    • American date: mso-number-format:mm\/dd\/yy
    • Month name: mso-number-format:d\\-mmm\\-yyyy
    • Date and Time: mso-number-format:d\/m\/yyyy\ h\:mm\ AM\/PM
    • Short Date: mso-number-format:"Short Date" (05/06/2011)
    • Medium Date: mso-number-format:"Medium Date" (10-jan-2011)
    • Short Time: mso-number-format:"Short Time" (8:67)
    • Medium Time: mso-number-format:"Medium Time" (8:67 AM)
    • Long Time: mso-number-format:"Long Time" (8:67:25:00)
  • Percentage: mso-number-format:Percent (To two decimal places)
  • Scientific Notation: mso-number-format:"0\.E+00"
  • Fractions - up to 3 digits: mso-number-format:"\#\ ???\/???"
  • Currency (£12.76): mso-number-format:"\0022£\0022\#\,\#\#0\.00"
  • 2 decimals, negative numbers in red and signed: mso-number-format:"\#\,\#\#0\.00_ \;\[Red\]\-\#\,\#\#0\.00\ " (1.86-1.66)
  • Accounting Format –5,(5): mso-number-format:”\\#\\,\\#\\#0\\.00_\\)\\;\\[Black\\]\\\\(\\#\\,\\#\\#0\\.00\\\\)”

Upvotes: 1

mjsqu
mjsqu

Reputation: 5452

This is down to how Excel represents numeric data. Export the file from SAS as a CSV and then import it into Excel. When selecting the column type for your ID field, choose 'text'.

Upvotes: 0

Related Questions