Home » Enterprise Guide

Enterprise Guide: Zero My Hero – Using the Z Format

Submitted by on February 17, 2012 – 6:30 am 4 Comments

Working with account numbers can require adding a leading zero.  You can use the Z format but it can be a little tricky. Not so long ago I showed you how to use the Z format to create a new data item in Information Map Studio.  However, I noticed many people are still searching for this information and when I was using it myself I was not able to find much on it. This post explains how to create a new column in Enterprise Guide following by the actual coding steps in PROC SQL:  

Creating a New Column in SAS Enterprise Guide Query Builder

To add the leading zeros, you have to use the Z format and a numeric variable.  Using the Query Builder, you can create a new column in your data. In this example, you’ll see how to change the numeric ACCNO to a character variable with the leading zeros.  This example starts with the ACCNO_TWO dataset.  

To create the new column, do this:

  1. From SAS Enterprise Guide, open the Query Builder with the ACCNO_TWO dataset.  
    Select Computer Columns button. In the Computer Columns window, select the New button (not shown).
  2.  From the New Computed Column window, select Advanced Expression and Next button.
  3. In the Enter an expression field, type put(accno, Z12.) to convert the ACCNO column using the Z format.  Click the Validate button to ensure it’s correct. Complete the windows to create the new column.
  4. The newly created column appears in the Select Data pane.  Run the query and review the results.

sas create new EG column with z format

 

Using the Z Format in PROC SQL

I have to confess that the above method took me a while to figure out, so here is how you can do the same thing within PROC SQL code.  The example below shows how to convert the ACCNO and the ACCNO_CHAR variables into a Z format.  If your ACCNO has any characters, this will not work.

The example shows how to code the following:

  • Transform ACCNO numeric variable to character (same as above)
  • Format the ACCNO numeric variable with as a Z format   
  • Transform the ACCNO_CHAR variable so that it has leading zeros.  Note I had to transform it to numeric using the INPUT function and then use the PUT function to format it.
  • Last example fails – just so you can see that it does not work  because there is no $Z character format.   

sas z format

 The Z format is a little tricky.  You have to understand the starting variable (character or numeric) and then the requirement for the ending variable.

<

Never miss a post!

Get the latest BI Notes post in your Inbox when a new post is released! Click here for free subscription. You email address is kept private and you can unsubscribe anytime. Go ahead ... join us!

The following two tabs change content below.
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC. She has written several books about SAS, presented papers at many SAS conferences, and has over 10 years of SAS programming experience. Contact her for assistance with your next project.

Tags: ,

4 Comments »

  • Thanks! I am totally out of my league with all you guys that really use it!!
    Let me know if there is another topic I can cover.

  • Prem Kumar says:

    Good explanation of not using Z format VS the solution provided using translate function to convert any character string containing numeric digits to fill with leading zero to create a string of desired length n <= 32767.

  • Awesome comment. Thanks for taking the time to show an example and explain the finer points.
    I knew someone would have some more insight than me about this Z format.

    Again, thanks for sharing your knowledge Paul.

  • Paul Dorfman says:

    Thanks for a good post. I would differ a bit in that I do not think there is anything inherently tricky with the Z format under the circumstances, i.e. when the column in question is numeric and contains integers. It is a numeric format that merely writes (prints) numbers using leading zeros. And, by the way, a character format $Z does not exist, so the expression containing $Z is illegal. While SQL is forgiving and simply ignores the non-existing format (printing with the default $ format instead), the same expression in a DATA step would render a compiler’s error, i.e. the step would not run at all.

    A more practical situation, where Z format may indeed prove tricky (and thus should be used carefully if at all), is one when an account number is represented by a character variable containing contiguous digits, i.e. a digit string. In practice, identifiers like account numbers – even when they contain digits only – are safer to store as character columns, because a SAS numeric variable’s full integer precision is limited, roughly speaking, to full 15 digits, and in many cases identifiers are significantly longer than that. Besides, on EBCDIC SAS number’s integer precision (56-bit mantissa) is higher than on ASCII (53-bit mantissa), so some 16-digit integers stored accurately on the mainframe would lose precision if pushed to an ASCII platform. (If there existed a credit card, whose 16-digit number started with 9, it would be a perfect example.)

    So, if you have, say, a character account number ACC that may contain up to 21 digits and you want to create another character variable, now with leading zeros, what a SAS programmer to do? It is easy to see that the seemingly logical attempt

    put (input (acc, 21.), z21.)

    hides a caveat, because even though the expression would compile in both DATA step and SQL, the informat 21 may fail to store ACC in the intermediate numeric expression accurately, if an ACC value should happen to actually contain more 15 digits. Hence Z21 would print all right, but it could print a wrong – internally rounded – value. For example (16 digits only, Windows XPro):

    put (input (’9999999999999999′, 21.), z21.)

    would result in 0000010000000000000000. Yikes! Therefore, the Z-trick above, while sufficient if ACC should contain fewer than 16 digits, is potentially dangerous and had better be avoided. Instead, the expression

    translate (put (acc, $21.-R), “0″, “”)

    will produce correct zero-leading values as long as vlength(acc)<= 21. Of course, it is not limited to length 21 and will work for any character length (up to 32767).