put zeros in front of variable

[deleted_user]

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?

[Cynthia_sas]

Hi:
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.

cynthia

[rab24]

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 ;
result=put(YourNumber,z6.);
result2='drop'||put(YourNumber,z6.);
run;