topic Re: Losing formats using an ODS to Excel in ODS and Base Reporting

Losing formats using an ODS to Excel

dereck255 — Sat, 10 Jan 2015 01:17:28 GMT

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!

Re: Losing formats using an ODS to Excel

SASKiwi — Sat, 10 Jan 2015 04:27:48 GMT

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"};

Re: Losing formats using an ODS to Excel

dereck255 — Mon, 12 Jan 2015 17:45:01 GMT

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?

Re: Losing formats using an ODS to Excel

MaikH_Schutze — Mon, 12 Jan 2015 18:33:01 GMT

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.

Re: Losing formats using an ODS to Excel

SASKiwi — Mon, 12 Jan 2015 18:49:50 GMT

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

Re: Losing formats using an ODS to Excel

Cynthia_sas — Tue, 13 Jan 2015 00:03:54 GMT

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

Re: Losing formats using an ODS to Excel

SASKiwi — Tue, 13 Jan 2015 03:59:37 GMT

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

Re: Losing formats using an ODS to Excel

MaikH_Schutze — Tue, 13 Jan 2015 14:48:28 GMT

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,

Re: Losing formats using an ODS to Excel

Cynthia_sas — Tue, 13 Jan 2015 18:46:38 GMT

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

Re: Losing formats using an ODS to Excel

dereck255 — Thu, 22 Jan 2015 02:34:39 GMT

This works perfectly! Thank you for your help!