Enterprise Guide: Zero My Hero – Using the Z Format
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.