Learn everything about Analytics

Comprehensive guide to SAS PROC Format

SHARE
, / 4

I have spent a significant part of my career as a data visualization guy. I am very particular about the formatting and presentation of reports. So, when I started using SAS, I faced a few challenges in changing formats of numbers and characters, especially when dates were involved. Not so surprisingly, both Kunal and I receive a fair number of queries on this topic.

In SAS, there are various options to enhance the reporting layouts. In this article, we will particularly discuss about methods to play with format of data values. It should be noted that these changes are only applied while displaying the results. Changing format of output does not change the way, the data gets stored at the back end.

By default, SAS provides various built in formats to deal with various formats, but they are not sufficient to meet custom requirements your data might have. For example, we may have coded Male and Female as M and F (or 0 and 1), but while printing we would want to display the field as MALE and FEMALE only. Another common example is to display area codes in 10 digit telephone numbers (e.g. 123-3456-789).

P.S. This is a long article compared to what I usually write, so feel free to digest this in bits and pieces. 

The article is divided in 2 broad parts: First we understand the pre-defined formats & user defined formats in SAS. Next, we look at various applications and examples of these concepts.

1_Proc_Format_SAS

Built In SAS Formats

Below is a sample data containing agent performance details. We will use this dataset in examples and discussion in this article. Here DOJ is in numeric format, as SAS defines by default, starting from 01-Jan-1960.

2_Proc_Format_DataLet’s first look at the syntac of the FORMAT statement:-

Syntax :-  FORMAT  Variable(s) Format;

Let’s do some exercises with format statement:

Example 1: Display DOJ in DDMMYYYY format:

Code:

Proc print Data=Sales;
Format DOJ Date9. ;
Run;

Output:

3_Proc_Format

Here is the list of predefined date formats available to change the output format of variables:

4_Proc_Format 

Example 2: Display the Salesamount in Dollars with one decimal position

Code:

Proc print Data=Sales;
Format Salesamount Dollar9.1 ;
Run;

Output:

5_Proc_Format_SAS

List of predefined date formats available to change the output format of variables:

6_Proc_Format_SAS

Example 3: Display Salesamount in Dollar with two decimal position with comma and DOJ in Weekdate format.

Code:

Proc Print Data=Sales;
Format Salesamount Dollar12.2 DOJ WEEKDATE.;
Run;

Output:

 7_Proc_Format_SAS

Need for user defined Formats:

Till now we have seen, how to change the format of numbers and Dates with in built SAS formats. But there can be many occasions when SAS built-in formats do not suffice our needs. Like in current dataset, we want to:

  1. Display “MALE” and “FEMALE” instead of “M” and “F”
  2. Re-define categories A, B, C, D and E as Ultra, Super, Average, Low and Poor
  3. Display the frequency of Salesamount in three categories “< 8000”,”8000-12000” and “>=12000”

These all things can be done with Data step (or PROC SQL) with If-Else statement (or switch case), but it will require a new variable that will store these formatted values. Whereas, if we only want to only change the display (and not the values in the data set), then creating user defined format using PROC FORMAT is a more efficient way to make these changes.

User Defined Formats Using Proc FORMAT

PROC FORMAT is a procedure that creates a mapping of data values into data labels. The user defined FORMAT mapping is independent of a SAS DATASET and variables and must be explicitly assigned in a subsequent DATASTEP and/or PROC.

Syntax of PROC FORMAT: –

 8_Proc_Format_SAS

 Rules for defining FORMAT NAME:
  1. For character values, first character must be a dollar sign ($), and a letter or underscore as the second character. For numeric values, name must have a letter or underscore as the first character
  2. Name cannot end with a number
  3. Cannot be the name of an existing SAS format
  4. Should not end with a period in the VALUE statement

 

Let’s solve the above discussed problems using PROC Format:

Problem 1 (& 2): Display M and F as Male and Female.

Code:

Proc Format;
Value $Genderfmt ‘M’=’Male’
‘F’ =’Female’;
Run;
Proc Print Data = Sales ;
Format Gender $Genderfmt. ;
Run;

Output:

9_Proc_Format_SASIn a similar way, we can solve it for the problem 2. Define the category A, B, C, D and E as Ultra, Super, Average, Low and Poor.

 

Problem 3: Want to display the frequency of Salesamount in three categories “< 8000”,”8000-12000” and “>=12000”

Code:

Proc format;
Value Salegrp low-8000 = ‘<8000’
8000 – < 12000 = ‘8000-12000’
12000-High =  ‘>=12000’;
Run;
Proc Freq Data=Sales;
Format Salesamount Salegrp.;
Table Salesamount;
Run;

Output:

10_Proc_Format_SAS

Above, I have used ranges to define the format. They can be used for both – character and numeric values.

Special keywords used to define the ranges are:-

a) Ranges can be multiple values separated by commas.

i) ‘A’, ’B’, ’C’
ii) 22, 44, 67

b) Ranges can include or exclude the bounding values, depending on the use of various keywords like:

i) 24 – 45: It includes values from 24 to 45 including 24 and 45.
ii) 24 < – 45: It includes values between 24 to 45 including 45 and excluding 24.
iii) 24 – < 45: It includes values between 24 to 45 including 24 and excluding 45.
iv) 24 < – < 45: It includes values between 24 to 45 excluding 24 and 45.

c) LOW refers to least available number, HIGH refers to the highest available number and OTHER includes all numbers not specified. Others also include missing values if it is not specified.

Few Important points / Applications to note about PROC FORMAT

  • PROC Format returns the original value if we have not included all data values while defining the format.

11_Proc_Format_SAS

  • We can create multiple formats in a single PROC FORMAT statement, by specifying multiple values under PROC Format

Code:

Proc Format;
Value Salegrp low-8000 = ‘<8000’
8000 – < 12000 = ‘8000-12000’
12000-High =  ‘>=12000’;
Value $Genderfmt ‘M’=’Male’
‘F’ =’Female’;
Run;
Proc Print Data=Sales;
Format Salesamount Salegrp. Gender $Genderfmt.;
Run;

Output:

12_Proc_Format_SAS

  • PROC FORMAT can be used for creating efficient Data merges

In PROC FORMAT, we have an option CNTLIN, that allows us to create a format from a data set rather than a VALUE statement. Before using this option we first look at the guidelines below:-

a) Input dataset must contain three variables required by PROC FORMAT – START, LABEL and FMTNAME.

b) Here START is the key field between these two tables and it must be unique in the input dataset (Dataset, we are using in CNTLIN option).

c) LABEL variable is the value we want to map it to another dataset.

d) The FMTNAME variable is the name of the format and it must be passed in single quotes.

e) After defining format, we can use put function to create variable in dataset based on key field and the format we have defined.

Example:

Let us say we want to map agent DOB from dataset Agent_DOB (Sample Data) to above dataset13_Proc_Format_SAS

Here we want to create a format based on Agent_DOB and apply it to Sales. In Agent_DOB, we would consider AGT_ID (key field, between both datasets) as START, DOB as Label and FMTNAME =’ $AGENT’. Now look at the statements (below).

Data Agent_DOB_Fmt;
Rename AGT_ID=START;
Set Agent_DOB;
Label=DOB;
FMTNAME=’$AGENT’;
Run;
Proc Format CNTLIN=Agent_DOB_Fmt;
Run;

14_Proc_Format_SAS

Above code has generated format using dataset Agent_DOB_Fmt and after that to merge with SALES dataset have written data steps (below).

Data Sales_DOB;
Set Sales;
Birth_Date=Put(Agt_ID, $Agent.);
Run;

15_Proc_Format_SAS

Limitation of PROC FORMAT as Merging

This is the best method when we want to merge one variable from another data set, but if we want to add five or multiple variables, then we have to repeat the PROC FORMAT statement that many times along with the multiple PUT function in data step. So, I prefer to use MERGE or PROC SQL in such cases.

  • We can save user defined SAS format for future use.

All SAS formats are stored in a catalog (collection of formats).  When we create a Format, it gets stored in the catalog. If we don’t specify the catalog, then SAS stores formats in the WORK library in a catalog called FORMATS. Like other datasets of WORK library, they also get deleted at the end of the session.

Now to save User defined formats, we need to specify where to store the catalog and what to call it. This can be achieved by storing formats in a library other than WORK.

Step -1 First of all we have to define a library (Here I am using the SAS University edition)

Syntax: – LIBNAME Library_Name “Path”
LIBNAME STATDATA “/folders/myfolders/ECSTAT0”;

 Step-2 Use library option in PROC format and provide a library name with the format file name. The file name must be a valid SAS dataset name.

 Syntax: – PROC FORMAT LIBRARY=Library_Name.MYFILENAME;
Proc Format library=STATDATA.Gender_Fmt;
Value $Genderfmt ‘M’=’Male’
‘F’ =’Female’;
Run;

Above program has created a file called Gender_Fmt.sas7bcat in your directory location.

  • How to Use stored Format

Now, whenever we want to use stored format, we have to tell SAS to look for formats in that catalog file. This is done with the fmtsearch option. So before using it, we need to write a statement.

  Syntax: – Options fmtsearch = (Library_Name.MYFILENAME)
 Options fmtsearch = (STATDATA.Gender_Fmt);  /*After this we can use Gender_Fmt*/
Proc Print data=Sales;
Format Gender $Genderfmt.;
Run;
  • SAS PICTURE FORMAT

SAS picture format creates templates in which we define how the numbers are displayed. With use of PICTURE FORMAT, we can get over multiple display issues of numbers like:-

1. Decimal and Comma Placement

2. Embedding Characters with Numbers

3. Prefixes

 Example 1: – I want to display the sales amount ending with a % sign and preceded with $ sign.

Proc Format;
Picture New_fmt low-High=’000000%’ (Prefix=’$’);

Run;
Proc print data=Sales;
Format Salesamount New_fmt.;
Run;

Output:

 16_Proc_Format_SAS

 Example 2: – Let’s say, I have a 10 digit telephone number and I want to display it as 123-3456-789.

Data Telephone;
Input Contact;
datalines;
1111111111
222222222
3333333333
;
Run;
Proc Format;
Picture Tele low-High=’000-0000-000′;
Run;
Proc print data=Telephone;
Format Contact Tele.;
Run;

17_Proc_Format_SAS 

End Notes:-

In this article, we looked at various methods to display format of data values using built in and user defined formats. We have also looked at various techniques to define formats like ranges, picture, handle missing values and unmatched values using OTHER. We have also discussed efficient merging technique using PROC FORMAT. This should be all what you need to be a pro with SAS Formats.

We have not covered In-format while reading non-standard dataset. We will discuss that in one of our future post.

Hope you found this article useful? We have simplified this topic and have tried to present it in a very simple and lucid manner. If you need any more help with SAS formatting, please feel free to ask your questions through comments below.

If you like what you just read & want to continue your analytics learning, subscribe to our emailsfollow us on twitter or like our facebook page.

4 Comments

  • Amitha says:

    can please send some important topics related to interview purpose for freshers

  • Saurabh Kapoor says:

    Hi Sunil,

    That’s very well explained.

    However, I have a small doubt regarding the following statement used for merging by Proc Format-

    Data Sales_DOB;
    Set Sales;
    *************Birth_Date=Put(Agt_ID, $Agent.); *************
    Run;

    I am unable to interpret the statement – *************Birth_Date=Put(Agt_ID, $Agent.); *************

    Can you help me understand the use of PUT here. How is the actual merging taking place here.

    Thanks
    Saurabh Kapoor

  • Venkatesh Kulkarni says:

    Very deeply explained article.

  • Patrick says:

    The one thing I miss in this article is the inclusion of an example of how to use functions in formats (built in Proc FCMP).

    It’s not that common yet but such a powerful new addition to formats (since 9.3) that it should be included in an overview article for formats.

Leave A Reply

Your email address will not be published.

Join world’s fastest growing Analytics Community
Receive awesome tips, guides, infographics and become expert at: