The SAS Output Delivery System and reporting techniques

Losing formats using an ODS to Excel

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Losing formats using an ODS to Excel

Hello,

Hoping to find some help with the following code:

data x;

set y;

SNum=(put(input(SNum,6.),Z6.));

run;

ods excel file="path.xls";

proc print data=x label noobs;

format DVisit date.;

run;

ods excel close;

When I run the code, SAS in the Output window shows the six digit "SNum", but when it's exported to Excel "SNum" is no longer padded to 6 digits. What gives?

When I try to format the SNum in the PROC PRINT step, SAS gives me the following error:

ERROR: You are trying to use the numeric format Z with the character variable SNum in data set WORK.X.

I thought by having this in the DATA step, the format becomes permanent.

How do I fix this?

Thank you!


Accepted Solutions
Solution
‎01-12-2015 07:03 PM
SAS Super FREQ
Posts: 8,864

Re: Losing formats using an ODS to Excel

Hi: TAGATTR will only work with TAGSETS.EXCELXP. I do not believe it works with ODS EXCEL. The original post showed ODS EXCEL. I understand that by later versions of SAS 9.4 (not M0), the SAS formats (such as Z6.) would be automatically used by ODS EXCEL. But the original post did not indicate the version of SAS or whether ODS EXCEL was a typo or whether he meant ODS TAGSETS.EXCELXP. So if he did mean the new ODS EXCEL, then he should be able to use the format directly without creating the dataset at all. The code shown in the attached screen shots was run by someone in Tech Support who had access to multiple versions of SAS 9.4 and even though there are minor differences, the pictures show the Z format being used, without the need for a DATA step program and explicit conversion.

cynthia


code_for_new_M1_xlsx.pngcode_for_new_xlsx.pngpic_of_new_m1_xlsx.pngpic_of_new_xlsx.png

View solution in original post


All Replies
Super User
Posts: 3,252

Re: Losing formats using an ODS to Excel

Posted in reply to dereck255

Open a brand-new Excel spreadsheet and in any cell type in the numbers 000111, then press enter. What you will see is not 000111 but just 111. This is the default Excel behaviour. This is exactly what Excel is doing when you open your spreadsheet created from PROC PRINT. It  does not matter if your SAS column is numeric or character or what SAS format you apply you will get the same result.

To fix the problem you have provide Excel with some further formatting instructions to avoid the default behaviour.

Try adding the following VAR statement to your PROC PRINT:

var snum / style(column)={TAGATTR="Format:000000"};

Contributor
Posts: 25

Re: Losing formats using an ODS to Excel

Thank you for your reply, though It didn't work. Instead, I got a second column labeled "snum" with the same unpadded digits. Any other ideas?

Contributor
Posts: 45

Re: Losing formats using an ODS to Excel

Posted in reply to dereck255

Hi,

What version of SAS are you using? There is a pre-prodution tagset in 9.4 that should automatically convert SAS formats to Excel formats without the TAGATTR= option.

And maybe I don't understand what you are trying to do. It looks like you are explcitily converting a character field to a numeric field with the w.d format - input(SNum,6.) - and then do another explicit conversion back to character using Zw.d function in the PUT statement.

Does your the original value for SNum already have leading zeroes? Maybe a few lines are data would help so we can test the logic.

Super User
Posts: 3,252

Re: Losing formats using an ODS to Excel

Posted in reply to dereck255

Try: var snum / style(column)={TAGATTR="Format:@"};

Solution
‎01-12-2015 07:03 PM
SAS Super FREQ
Posts: 8,864

Re: Losing formats using an ODS to Excel

Hi: TAGATTR will only work with TAGSETS.EXCELXP. I do not believe it works with ODS EXCEL. The original post showed ODS EXCEL. I understand that by later versions of SAS 9.4 (not M0), the SAS formats (such as Z6.) would be automatically used by ODS EXCEL. But the original post did not indicate the version of SAS or whether ODS EXCEL was a typo or whether he meant ODS TAGSETS.EXCELXP. So if he did mean the new ODS EXCEL, then he should be able to use the format directly without creating the dataset at all. The code shown in the attached screen shots was run by someone in Tech Support who had access to multiple versions of SAS 9.4 and even though there are minor differences, the pictures show the Z format being used, without the need for a DATA step program and explicit conversion.

cynthia


code_for_new_M1_xlsx.pngcode_for_new_xlsx.pngpic_of_new_m1_xlsx.pngpic_of_new_xlsx.png
Super User
Posts: 3,252

Re: Losing formats using an ODS to Excel

Posted in reply to Cynthia_sas

Oops. Cynthia is quite correct. I should have looked more closely at the original post...

Contributor
Posts: 45

Re: Losing formats using an ODS to Excel

Hi,

Thank you, Cynthia. Sadly, I still work on SAS 9.2 and I noticed the original post used the ODS Excel destination but I couldn't find anything in the documentation about TAGATTR and ODS Excel, although I didn't look that hard, but I did recently have a conversation with Chevell Parker and he had mentioned the new functionality for the forthcoming 9.4M4 release.

Thanks,

M.

SAS Super FREQ
Posts: 8,864

Re: Losing formats using an ODS to Excel

Posted in reply to MaikH_Schutze

Hi:

  TAGATTR style override method of passing a Microsoft format to Excel was explicitly designed for use with the TAGSETS.EXCELXP destination, which creates a Spreadsheet Markup Language XML file that Excel knows how to open and render. This is entirely a different kind of file than the XLSX zip archive file created by the new (SAS 9.4) ODS Excel. Many of the format issues that required the use of TAGATTR with TAGSETS.EXCELXP will not be an issue with ODS EXCEL. Since the original posting showed ODS EXCEL, then I wanted to clarify that if the original post was indeed about ODS EXCEL in SAS 9.4, then a conversion to get leading zeroes should not be necessary (as shown in the screen shots that I attached).

  But whether the original poster is using ODS TAGSETS.EXCELXP or ODS EXCEL, there is no need to have a DATA step do the conversion to get leading zeroes, since both destinations have a way to handle that situation -- ODS TAGSETS.EXCELXP has an "external" style override method and ODS EXCEL has an internal method.

cynthia

Contributor
Posts: 25

Re: Losing formats using an ODS to Excel

Posted in reply to Cynthia_sas

This works perfectly! Thank you for your help!

🔒 This topic is solved and locked.

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

Discussion stats
  • 9 replies
  • 1466 views
  • 2 likes
  • 4 in conversation