topic Re: ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE in ODS and Base Reporting

ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE

ijmoorejr — Wed, 14 Nov 2018 20:15:32 GMT

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;

Re: ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Reeza — Wed, 14 Nov 2018 21:07:22 GMT

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

Re: ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE

ijmoorejr — Wed, 14 Nov 2018 22:34:50 GMT

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?

Re: ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Reeza — Wed, 14 Nov 2018 23:48:14 GMT

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

Re: ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Cynthia_sas — Wed, 14 Nov 2018 23:59:28 GMT

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

Re: ODS TAGSETS EXCELXP DROPPING LEADING ZEROES ON CHARACTER VARIABLE

Vince_SAS — Sat, 17 Nov 2018 20:34:45 GMT

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