The SAS Output Delivery System and reporting techniques

Exporting Data with Leading Zeores

Accepted Solution Solved
Reply
Regular Contributor
Posts: 239
Accepted Solution

Exporting Data with Leading Zeores

I have an issue where I cannot get values with leading zeroes to appear in my output.  I am creating a table that compares SITEID with SUBJID.  The middle 3 digits of SUBJID should be the same as SITEID.  If this is not the case, it should output the discrepant cases.  My SAS dataset looks like this:

 

SITEID     SUBJID

009

                 102-002-001

                 102-008-002

8               102-008-008

002           102-02-002

 

 

However, when I export the data into an Excel file (I've done both xls and xml using ods tagsets.excelxp), the leading zeroes for SITEID disappear.  SITEID is a character variable with length 3.  I can't use the z. format because I don't want to force "8" to become "008".  Instead, I have used the following code:

 

proc print data=check&check. (drop=checkmsg) noobs label;
    var _all_ / style(data)={tagattr='format:@'};
run;

I have run reports where adding the "style(data)={tagattr='format:@'}" portion to the VAR statement has fixed leading zeroes, date format issues, and any other format problems I have run into when exporting.

 

Does anyone have any idea how I might be able to resolve this?

 


Accepted Solutions
Solution
‎09-26-2017 10:26 PM
Regular Contributor
Posts: 239

Re: Exporting Data with Leading Zeores

Posted in reply to djbateman

Thank you, everyone, for your suggestions.  I found my own issue.  I should have shared my full code.  Maybe then someone would have found my problem sooner.

 

It turns out that I had additional code in the VAR statement that was apparently causing the issue.  I took out "style={tagattr='WRAP:YES'} and reran.  Things came out properly.  I then moved it to the front, and it also ran fine.  I guess the order of style(data) vs. style is a problem.

 

In short, this is what I originally had:

 

proc print data=check&check. (drop=checkmsg) noobs label;
	var _all_ / style(data)={tagattr='format:@'} style={tagattr='WRAP:YES'};
run;

 

and this is what I have that works:

  

proc print data=check&check. (drop=checkmsg) noobs label;
	var _all_ / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
run;

 

View solution in original post


All Replies
Super User
Posts: 20,731

Re: Exporting Data with Leading Zeores

Posted in reply to djbateman

Convert the data before hand, create a character variable that is exactly what you want and try ODS EXCEL if you're on SAS 9.4

Super User
Super User
Posts: 7,260

Re: Exporting Data with Leading Zeores

[ Edited ]
Posted in reply to djbateman

Seems like a bug to me. At least with 9.4 (TS1M2) running on Windows.

image.png

If you attach the SAS format $CHAR to the variable then it seems to work.  

image.png

 

%let path=C:\Downloads ;

data have ;
  length SITEID $5 SUBJID $20 ;
  infile cards dsd truncover ;
  input siteid subjid ;
cards;
009,
,102-002-001
,102-008-002
8,102-008-008
002,102-02-002
;
ods excel file="&path\test1.xlsx" ;

proc print data=have noobs label;
  var _all_ / style={tagattr='format:text'} ;
  format siteid $char5. ;
run;

ods excel close ;

 

Regular Contributor
Posts: 239

Re: Exporting Data with Leading Zeores

I am using SAS 9.4 (TS1M2).  I changed from ods tagsets.excelxp to ods excel.  I also added the $char. format.  Neither of those is working for me.

Super User
Posts: 20,731

Re: Exporting Data with Leading Zeores

@Tom ODS Excel isn't production until M3? 

 

@djbateman What happens if you don't add any TAGATTR and use a Z or CHAR format? 

Super User
Posts: 10,210

Re: Exporting Data with Leading Zeores

Posted in reply to djbateman
%let path=C:\temp ;

data have ;
  length SITEID $5 SUBJID $20 ;
  infile cards dsd truncover ;
  input siteid subjid ;
  siteid=cats("09"x,siteid);
cards;
009,
,102-002-001
,102-008-002
8,102-008-008
002,102-02-002
;
ods excel file="&path\test1.xlsx" ;

proc print data=have noobs label;
  var _all_ / style={tagattr='format:text'} ;
run;

ods excel close ;
Solution
‎09-26-2017 10:26 PM
Regular Contributor
Posts: 239

Re: Exporting Data with Leading Zeores

Posted in reply to djbateman

Thank you, everyone, for your suggestions.  I found my own issue.  I should have shared my full code.  Maybe then someone would have found my problem sooner.

 

It turns out that I had additional code in the VAR statement that was apparently causing the issue.  I took out "style={tagattr='WRAP:YES'} and reran.  Things came out properly.  I then moved it to the front, and it also ran fine.  I guess the order of style(data) vs. style is a problem.

 

In short, this is what I originally had:

 

proc print data=check&check. (drop=checkmsg) noobs label;
	var _all_ / style(data)={tagattr='format:@'} style={tagattr='WRAP:YES'};
run;

 

and this is what I have that works:

  

proc print data=check&check. (drop=checkmsg) noobs label;
	var _all_ / style={tagattr='WRAP:YES'} style(data)={tagattr='format:@'};
run;

 

Trusted Advisor
Posts: 1,123

Re: Exporting Data with Leading Zeores

Posted in reply to djbateman

@djbateman:   Please mark your diagnosis and prescription as the solution.  It's useful information.

SAS Super FREQ
Posts: 322

Re: Exporting Data with Leading Zeores

Posted in reply to djbateman

The reason why the rearranged code "worked" for you is that the last style override takes precendence.  In that case, the Excel number format is applied, but wrap text isn't.  Also, you are missing the "location" in your "wrap" override.

 

Run this code to see what's happening with the overrides in your original code:

 

data work.test;
length siteid $3;
siteid = '009'; output;
siteid = '8';   output;
siteid = '002'; output;
run;

ods _all_ close;

ods tagsets.ExcelXP file='C:\temp\test.xml' style=Printer;
proc print data=work.test;
  var _all_ / style(data)={background=red tagattr='format:@'} style={background=yellow tagattr='wrap:yes'};
run; quit;
ods tagsets.ExcelXP close;

 

The background color of the data cells is yellow instead of red, indicating that the last override took precedence, causing you to lose the Excel number format.  The background color of the column heading is also yellow.  It is the missing "location" in the "wrap" style override is what caused the yellow background in the heading.

 

To get around these issues, always specify a "location" for the style override, and combine all attributes into a single override. 

 

Does this give you want you want?

 

var _all_ / style(data)={tagattr='format:@ wrap:yes'};


Vince DelGobbo
SAS R&D

Super User
Super User
Posts: 7,260

Re: Exporting Data with Leading Zeores

Posted in reply to djbateman

SAS already has a knowledge base entry for this issue.  http://support.sas.com/kb/57/620.html  

Problem Note 57620: The text format is ignored when it is passed using the TAGATTR= attribute with the ODS Destination for Excel

 

The solution is the use to set the Excel TYPE and not the FORMAT.

style(data)={tagattr="type:String"}

The example in Problem Note 57620 is a numeric variable with Z. format, but the same solution works for character variables.

Why you need to tell SAS that you character variables should be type string in Excel I don't know.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 544 views
  • 1 like
  • 6 in conversation