BI Tools

Tips and tricks for building information maps, OLAP cubes, reports, and dashboards

BI Admin

Learn your way around a SAS BI installation.

Visual Analytics

Learn your way around the SAS Visual Analytics tool

Coding & Data

Extract, transform, and load your data into the SAS BI toolset

Stored Processes

Create and design stored processes like a rock star

Home » BI Tools

SAS Info Map: Need leading zeros on the account number?

Submitted by on 2011-11-23 – 7:00 AM 4 Comments

When creating new variables, the SAS Information Map Studio opens the entire SAS format library to you.  These formats can be key to creating lots of new data items.  SAS format control the way data items appear.  In past articles I showed you how to create new data item using SAS functions and date formats. Check the SAS documentation to learn more about all the different formats available.

Creating new Data Items in SAS Information Map Studio

When data has been merged from various companies, such as when one company acquires another one, their data may be in different formats.  For instance, account numbers might vary from 8 characters in one company and 12 characters wide in the other. When this occurs one option is to use the widest account number and add zeros to the front of the smaller one to integrate the fields better.  You can do this easily within the information map using the Z format.  

From SAS Information Map Studio, you can create or modify the existing variable.  In this case I have two account numbers – 1 where there are only 3 characters and the other with 5 characters. The end result should be a 6 character long account number where the 0 are used to complete the blanks. 

  1. Create or modify the data item. In the Classifications, Aggregations, and Formats pane, select the Numeric format type and then navigate to the Z format.
  2. Adjust the total width of the field. This value should match the width of the account number after the zeros have been added.  In this example, the value is set to 6.
  3. For the result you can see that the zeros complete the field.  Australia account numbers have a default value of 5 so 1 zero was added, while Austria has a default value of 3, so 3 zeros were added.

sas bi information map studio using the Z format

 

Learn all about SAS BI

You can learn more about SAS Business Intelligence from the "SAS BI Bible." Take a peek inside the Building Business Intelligience with SAS book.

The following two tabs change content below.

Tricia Aanderud

Director of Data Visualization at Zencos Consulting
Tricia Aanderud is a SAS Business Intelligence and Visual Analytics consultant based in Raleigh, NC who works for Zencos Consulting. She has written several books about SAS, presented papers at many SAS conferences, and has been using SAS since 2001. Contact her for assistance with your next project.

Tags: ,

4 Comments »

  • Harsh Gajjar says:

    @dave..thanks .. it was really helpful.

  • Rodney P. Eady says:

    Just wanted to say you have a great site and thanks for posting!…

  • daveG says:

    Yes.

    You could for example do a proc sql query to get the max(length(charvar)) or max(length(put(VAR, best23.)) or max(log10(VAR)) for the dataset.
    From Proc SQL it is easy to pop that into a macro variable to use in the above code.

    If you are doing other summary stats you could add a var to hold the Max and use that.

  • Harsh Gajjar says:

    @Tricia.. Nice Post.. but I would really like to know if One can create an expression which decides.. on the basis of data which should be the longest account number. Is that Possible in SAs Info Studio