Enterprise Guide: Zero My Hero – Using the Z Format

This post was kindly contributed by Business Intelligence Notes for SAS® BI Users - go there to comment and to read the full post.

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:

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).
From the New Computed Column window, select Advanced Expression and Next button.
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.
The newly created column appears in the Select Data pane. Run the query and review the results.

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.

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.

Related content: