Enterprise Guide: Zero My Hero – Using the Z Format

February 17, 2012
By

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:

  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.

Know someone who would like this post? Share it:

Facebook facebook   Twitter twitter   Email email   Linkedin linkedin   Email email  

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

Tags: , , ,

Proc-x is looking for sponsors!

Dear readers, proc-x is looking for sponsors who would be willing to support the site in exchange for banner ads in the right sidebar of the site. If you are interested, please e-mail me at: [email protected]

Welcome!

SAS-X.com offers news and tutorials about the various SAS® software packages, contributed by bloggers. You are welcome to subscribe to e-mail updates, or add your SAS-blog to the site.

SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.