01-08-2015 05:17 PM
Hoping to find some help with the following code:
ods excel file="path.xls";
proc print data=x label noobs;
format DVisit date.;
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?
01-08-2015 05:36 PM
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.
01-09-2015 08:01 PM
You're right. It's correct in the original code, I just changed out the names for privacy reasons. It should be:
Regardless, does anyone have a fix?