ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE

[ijmoorejr]

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;

[Reeza]

Try explicitly specifying the format on the DEFINE statement as well.

[ijmoorejr]

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?

[Reeza]

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. 

https://support.sas.com/rnd/base/ods/odsmarkup/

See the solution here otherwise

http://support.sas.com/resources/papers/proceedings11/266-2011.pdf

[Cynthia_sas]

Hi,

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.

Cynthia

[Vince_SAS]

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';
run;

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

SAS R&D