Losing formats using an ODS to Excel

Reply
Contributor
Posts: 24

Losing formats using an ODS to Excel

Hello,

Hoping to find some help with the following code:

data Report;

set x;

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!

Grand Advisor
Posts: 10,210

Re: Losing formats using an ODS to Excel

What you are seeing is Excel stripping off the leading zeroes because the value looks numeric. If you check the cell format in Excel it is likely to be "general" which does the stripping. For example pick an empty cell in Excel and type 0001. When you tab out of the cell the remaining value will be 1.

You get the error message because the PUT statement used to if it created SNum would make SNum a character variable. But it was already character in dataset X. I am assuming that you also tried printing data=Report and had the same issues.

I don't have the ability to use ODS Excel output so don't have a suggestion for fixing this with that output.

Grand Advisor
Posts: 17,332

Re: Losing formats using an ODS to Excel

You create the data to a table called Report but then use data X in your ODS code?

Contributor
Posts: 24

Re: Losing formats using an ODS to Excel

You're right. It's correct in the original code, I just changed out the names for privacy reasons. It should be:

data x;

set y;

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

run;

Regardless, does anyone have a fix?

Grand Advisor
Posts: 17,332

Re: Losing formats using an ODS to Excel

You should post this in the ODS forum to have some of the ODS people chime in.

Ask a Question
Discussion stats
  • 4 replies
  • 269 views
  • 0 likes
  • 3 in conversation