topic Exporting Data with Leading Zeores in ODS and Base Reporting

Exporting Data with Leading Zeores

djbateman — Thu, 21 Sep 2017 17:54:50 GMT

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?

Re: Exporting Data with Leading Zeores

Reeza — Thu, 21 Sep 2017 19:15:52 GMT

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

Re: Exporting Data with Leading Zeores

Tom — Thu, 21 Sep 2017 21:26:51 GMT

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

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

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

Re: Exporting Data with Leading Zeores

Ksharp — Sat, 23 Sep 2017 12:42:38 GMT

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

Re: Exporting Data with Leading Zeores

djbateman — Mon, 25 Sep 2017 21:15:41 GMT

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.

Re: Exporting Data with Leading Zeores

Reeza — Tue, 26 Sep 2017 00:07:31 GMT

@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?

Re: Exporting Data with Leading Zeores

djbateman — Tue, 26 Sep 2017 19:44:19 GMT

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;

Re: Exporting Data with Leading Zeores

mkeintz — Wed, 27 Sep 2017 00:51:22 GMT

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

Re: Exporting Data with Leading Zeores

Vince_SAS — Wed, 27 Sep 2017 12:51:08 GMT

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

Re: Exporting Data with Leading Zeores

Tom — Wed, 27 Sep 2017 16:38:36 GMT

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.