BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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?

 

1 ACCEPTED SOLUTION

Accepted Solutions
djbateman
Lapis Lazuli | Level 10

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

9 REPLIES 9
Reeza
Super User

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

Tom
Super User Tom
Super User

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 ;

 

djbateman
Lapis Lazuli | Level 10

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.

Reeza
Super User

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

Ksharp
Super User
%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 ;
djbateman
Lapis Lazuli | Level 10

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;

 

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Vince_SAS
Rhodochrosite | Level 12

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

Tom
Super User Tom
Super User

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.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 9 replies
  • 4569 views
  • 3 likes
  • 6 in conversation