BookmarkSubscribeRSS Feed
ijmoorejr
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;
5 REPLIES 5
Reeza
Super User
Try explicitly specifying the format on the DEFINE statement as well.
ijmoorejr
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?

Reeza
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. 

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
SAS Super FREQ

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

sas-innovate-2024.png

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
  • 1785 views
  • 2 likes
  • 4 in conversation