Hi. I have a numeric variable (length 6) and I would like to place zeros in front of the existing variable (i.e. if the value is 150, I would like it to become 000150). I know that the the z. format will do this, but when I convert this to a character, the zeros are dropped (I need this in character format as I want to export it to excel and keep the zeros). Any tricks?
If you search the forum listings for ExcelXP and the TAGATTR style attribute, there is a way with ODS to create output files for Excel and basically send a Microsoft leading zero format to Excel.
Otherwise, if you MAKE a character variable with the Z format, SAS will keep the leading zeroes, but as you discovered, Microsoft does not respect the zeroes. The other thing you can do is make a character format and then tell Excel that your number with leading zeroes is a character variable, again, by using the TAGATTR style attribute with TAGSETS.EXCELXP in order to send a Type instruction to Excel.
I don't know of a way with PROC EXPORT to do what you want to do. However, if you search, you should find methods for doing this with ODS.
EG4 and Excel are working together for me today, because Excel is honoring the leading zeros when I put SAS data into a character format.
If Excel still isn't working for you and you don't mind a little extra work, try result2 below where it concatenates your number with the text 'drop' so your cell reads drop000150. A quick find replace in Excel corrects the added text around and keeps the leading zeros.
data test; set data;
length result $10 ;