BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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?
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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
Calcite | Level 5
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 3913 views
  • 0 likes
  • 3 in conversation