BookmarkSubscribeRSS Feed
Calcite | Level 5

I am using ods tagsets excelxp to create a .xls from a sas dataset. One of the fields from the input data source has length 16, format $16., and informat $16. This field has loan number values that contain a leading zero (i.e. 0123456). Ods tagsets excelxp is stripping off the leading zero resulting in (123456). I have tried several different variations of the define statement for that particular field, but nothing seems to work. Does anyone have any ideas? My goal is to keep the leading zero.

define LOAN_NUM  /  'LOAN_NUM' style={tagattr="format:@"} center;
Super User
Try explicitly specifying the format on the DEFINE statement as well.
Calcite | Level 5

Additionally, I walked through the code that leads up to the final dataset that gets used with the ods tagsets excelxp to make the .xls. There are several joins along the way and this particular loan number field has a length of $16. from one source and a length of $20. Could the varying lengths be causing the problem with the dropping of the leading zeroes?

Super User

Check your input data. If it has the leading zeros then no and I highly doubt that’s the issue


If you’re on SAS 9.4 M3+ ODS EXCEL is easier. 


This is commonly asked actually and I always thought that TAGATTR works. Can you verify your tagsets version is the latest? That’s also a common issue. The version is in the log after you use it. Should be 1.131 


Otherwise instructions on how to update it are here.


See the solution here otherwise



As Reeza suggests, Excel is notorious for not showing leading zeroes unless you explicitly use a TAGATTR style override in PRINT, REPORT or TABULATE. Just using a Z. format at the SAS end or padding your character variable with zeroes is not enough -- Excel treats any number -- whether character value or numeric value -- with the "general" format that is an Excel format without leading zeroes. I recommend checking all the things that Reeza suggests as far as TAGSETS.EXCELXP version and I'd also recommend fixing the length mismatch. But if your leading zeroes don't show, then look at the paper reference (Thanks, Reeza!)  and follow the TAGSETS.EXCELXP examples for TAGATTR. There is a leading zero example in the paper.



Rhodochrosite | Level 12

This code creates an XML file with the leading zero retained, so check your data and version of the ExcelXP tagset.


data work.test;
loan_num = '0123456';

ods _all_ close;

ods tagsets.ExcelXP path='C:\temp' file='temp.xml' style=HTMLBlue;

  proc report data=work.test;
    columns loan_num;
    define loan_num / 'LOAN_NUM' style={tagattr="format:@"} center;
  run; quit;
ods tagsets.ExcelXP close;
NOTE: This is the Excel XP tagset (Compatible with SAS 9.1.3 and above, v1.131, 04/23/2015). Add options(doc='help') to the ods statement for more information.


Vince DelGobbo



Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4 in conversation