PROC FORMAT     


 Training Videos:

Dress up your data values and summary tables with SAS formats  


Formats serve as gateways between mapped values, ie. reading, storing and displaying values, working with dates, sort order and format display of values, converting between character and numeric variables and grouping values.  

PROC FORMAT is useful to control the display of variables without changing how it is stored in the dataset.  Generally, any value not listed in the PROC FORMAT syntax will be displayed as is.  As an alternative, the OTHER reserved keyword maybe applied to assign unexpected values.  Both character and numeric variables may use SAS® supplied or user-defined formats.

Note that to access all permanent format catalogs, you MUST use the following statement.  This example uses a custom libname and format catalog file name instead of the default LIBRARY and FORMATS file.  OPTIONS FMTSEARCH=(MYLIB.MYFORMAT);  


Top Ten Common Reasons for using PROC FORMAT

 
1. Display numbers as character values for storing codes but displaying labels, ex. store value as 1 but display as 'Yes'.

 2. Combine numeric values or dates into groups of categories to use in PROC FREQ for example, ex. contrct format is added to the library.  

PROC FORMAT LIBRARY=library;

 VALUE contrct

  LOW-‘31dec2005’d="INVALID"

  ‘01JAN2006’D-’31DEC2006’d= "ARBITRATION"

  ‘01JAN2007’d - high=[MONYY7.]; /* INSTRUCTS SAS TO USE THE 

  MONYY7. FORMAT FOR VALUES BEYOND 2006*/

RUN;


 3. Create new variables by mapping from an existing variable using PUT() for example to convert 'Y' to 'Yes'.

 4. Display date and date time variables using SAS supplied formats instead of the numeric value, ex. yymmdd10 to display as 20150101.  See PROC EXPAND and SAS Dates.

 5. Use user defined informats to correctly read character, numeric and date values, ex. $west informat.

 6. Combine character values into groups of categories to use in PROC FREQ for example.

 7. Override a permanent format with a temporary format or cancel the format in SAS procedures for example.

 8. Apply temporary formats in SAS procedures such as PROC FREQ or PROC PRINT.
 9. Apply as traffic lighting using ODS.  See SAS paper.

proc format;

 value cfore low - 21000 = 'white'

 21000< - 25000 = 'black'

 75000 - high = 'white';

 value cback low - 21000 = 'red'

 21000< - 25000 = 'yellow'

 75000 - high = 'green';

run;

# alternative exclusive real number format

VALUE age

LOW - <13 = 'Child'

13 - <20 = 'Teenager'

20 - <65 = 'Adult'

65 - HIGH = 'Senior';

 

Top Ten Uncommon Reasons for using PROC FORMAT


 1. Picture formats to display special characters such as (xxx)-xxx-xxxx or x%.  Helpful to display professional tables.

 2. Control CLASS levels in desired order with (NOTSORTED) in PROC TABULATE ORDER=DATA PRELOADFMT for example.

 3. Nesting formats by defining a format calling another format, such as missf., to prevent redundancy.  

VALUE sex (NOTSORTED) 2 = 'Female' 1 = 'Male' other = [missf.] ; 

See SAS paper.


 4. Putting style information into a format to control the background color, bold or indent for example.  

VALUE sex (NOTSORTED) 2 = '^\~^\b Female' 1 = '^\~^\b Male' .M = 'Unknown';


 5. Putting unicode characters into a format to display special symbols such as the symbols for male and females for example.   See Unicode Reference and Reference 2.

VALUE sex 1 = '^{Unicode 2642} Male' 2 = '^{Unicode 2640} Female'

.M = 'Unknown';


 6. Multilabel formats to allow overlap of ranges in year end reports such as 1-21 = '0-21', 22-40 = '22-40' and 1-40 = '1-40' with PROC FORMAT / MLF PRELOADFMT ORDER=DATA for example.

 7. Converting a dataset to a format (cntlin=option) for my dynamic creation of format catalog.

 8. Using a format for data cleaning by grouping valid and invalid values and using HLO for high, low and then other to assign 'Invalid' label.

 9. Creating a permanent format library to save time from recreating temporary format catalogs.

 10. Give your format library a unique name using two-level name instead of the default FORMATS and assign helpful descriptions with full labels.  Use FMTSEARCH to search for multiple format catalogs and FMTLIB to display the format catalog.
 11. Missing report

PROC FORMAT; VALUE MVALUE OTHER='NON-MISSING' .='MISSING'; VALUE $MVALUE OTHER='NON-MISSING' ''='MISSING'; RUN; PROC FREQ; TABLES _ALL_/MISSING; FORMAT _NUMERIC_ MVALUE. _CHARACTER_ $MVALUE; RUN;

Below is a collection of SAS® papers on PROC FORMAT. To create data-driven format catalog, enter format values and labels in excel file, use PROC IMPORT to create dataset and then apply the code below.  

See also SAS Functions, Informats and Formats and PRELOADFMT option for PROC TABULATE.  SAS Formats and the Format Procedure handout

* Access permanent format catalog using reserved LIBRARY name;

libname library 'C:\sd01\catalog'; /* Default name FORMATS.SAS7BCAT in folder */

proc format library = library;     /* Add sex format to format catalog */

 value $sex 'F' = 'Female'

            'M' = 'Male';

run;

* Access MY_RTF_FMTS permanent format catalog using another libname;

libname myformat 'C:\sd01\study1a';  /* Default name FORMATS.SAS7BCAT in folder */

proc format library = myformat.my_rtf_fmts;  /* Add sex format to format catalog */ 

  value $sex 'F' = 'Female'

             'M' = 'Male';

run;

options fmtsearch=(myformat.MY_RTF_FMTS);           /* required to access formats, useful to maintain and access MY_RTF_FMTS.SAS7BCAT files across multiple folders */

* Date formats with LOW and HIGH keywords with date constants;

proc format;

 value registration low - <'15Jul2005'd = 'Not Open'

 '15Jul2005'd - '31Dec2006'd = [mmddyy10.]

 '01Jan2007'd - high = 'Too Late';

run;

 

* Numeric cutoff format, useful with Proc Freq;

* Note that LOW does not include missing values, so you'd put them in a separate range.;
* The '600<-' exclusion indicator means "everything above 600, but not including 600".;
* Best to use FUZZ= whenever the exclusion indicators are used.;

proc format;
 value target_phe (fuzz=0)
  low-600='achieved'
  600<-high='not achieved'
  ._-.Z='missing value';
run;

data _null_;
 input x @@;
 put x= x=target_phe.;
datalines;
599 600 600.0001 601 1000 . .a .z
run;

* Picture format that works like a value, with labels, and picture format, with prefix value; * See SAS paper for more info;

proc format lib=library;
 picture visitwin (round)
   1 = 'Screening' (noedit)
   2 = 'Baseline' (noedit)
   3-6 = 9 (prefix='Day ')
   7-98 = 09 (prefix='Week ' mult=.142857)
   99.01 - 99.99 = 00.99 (prefix='Unscheduled Visit');
run;  

* Dataset structure to build format catalog;

data cntlin;

  length fmtname hlo start end label $10 type $1;

  * Numeric format;

  fmtname = 'gender';  /* format name */

  hlo = ' ';           /* operator */

  start = '1';         /* numeric value in quotes */

  end = '1';           /* same numeric value */

  label = 'Female';    /* format label */

  type = 'N';          /* format type */

  output;             /* write to dataset */

  fmtname = 'gender'; /* repeat for second gender value */

  hlo = ' ';

  start = '2';

  end = '2';

  label = 'Male';

  type = 'N';

  output;

  * Character format;

  fmtname = 'yes_no';

  hlo = ' ';

  start = 'Y';     /* character value in quotes */

  end = 'Y';       /* same character value */

  label = 'Yes';

  type = 'C';

  output;

  fmtname = 'yes_no';

  hlo = ' ';

  start = 'N';

  end = 'N';

  label = 'No';

  type = 'C';

  output;

run;

* Create and display format catalog;

proc format cntlin = cntlin fmtlib; run;

 

** Read in raw Format dataset **;

proc sort data=rawdata.pdsformats out=fmtlist(keep=FMTNAME LABEL START
TYPE) nodupkey;
   by FMTNAME START END;
run;

data numfmt;
   set fmtlist(rename=(start=stc));
   where type='N';
   start=input(strip(stc), ??best.);
   if start^=.;
   drop stc;
run;

***Numeric format created***;
proc format lib=fmtLib cntlin=numfmt;
quit;

***Charater format created***;
*Removed fmt name FMTNAME because it creates error*;
data charfmt;
  set fmtlist;
  where type='C' and FMTNAME^='ABNRMAL';
run;

proc format lib=fmtLib cntlin=charfmt;
quit;

______________________________________________

 Beginner SAS Programmer   




 Advanced SAS Programmer 



 Macro SAS Programmer       

SAS® Reference PROC FORMAT Syntax

SAS List of Informats and Formats

SAS® Course SAS Formats and the FORMAT Procedure

SAS® Course Understanding and Applying Multilabel Formats

SAS Blog Edit Existing Formats

SAS Blog 5 reasons to use PROC FORMAT to recode variables in SAS

______________________________________________

1. Ten Things You Should Know About PROC FORMAT, Jack Shoemaker

2. I Can Do That With PROC FORMAT, Jonas V. Bilenas [Char and Numeric range examples]

3. The Power of the FORMAT Procedure, Jonas V. Bilenas [Picture]

4. PROC FORMAT – Not Just Another Pretty Face, Lois Levin

5. Making the Most Out of Multilabel Formats, Tasha Chapman [MLF]

6. SAS Software Formats: Going Beneath the Surface, Roger Staum [INPUTN, INPUTC, Exclusive Real number format]

7. Missing Categories from categorical summary report? PRELOADFMT comes to your rescue, Sapan Raval, Niraj Pandya

8. 44 Tricks with the Format Procedure, Ben Cochran

9. SAS Formats: Effective and Efficient, Harry Droogendyk

10. Getting in to the Picture (Format), Andrew Karp

11. Picturing Your Character Variables with PROC FORMAT and SAS® 9.3, Brice Hart

12. PICTURE Perfect: In depth look at the PICTURE format., Carry Croghan

13. SAS Formats, Beyond the Basics, Andrew Karp [Presentation]

14. Building and Using User Defined Formats, Art Carpenter [Table Lookup, PUT(), Pictures]

15. SAS® Formats Top Ten, Christianna Williams

16. Yes, PROC FREQ does that!, AnnMaria De Mars

17. Best Practices: Subset Without Getting Upset, Mary F. O. Rosenbloom, Kirk Paul Lafler

18. Using Advanced Features of User-defined Formats and Informats, Ron Cody [Date Formats]

19. Creating a Format from Raw Data or a SAS® Dataset, Wendi L. Wright [HLO, CNTLIN]

20. Beyond FORMAT Basics, Mike Zdeb

21. SAS Formats: Making the Best of a Bad Situation, Michelle Pritchard [FORMAT _ALL_]

22. Yes, We Can... Save SAS® Formats, John Ladds

23. Proc Format - Tricks and Traps, Christof Binder

24. You Can Bet On It, The Missing Rows are Preserved with PRELOADFMT and COMPLETETYPES, Christopher Boniface, Janet Wysocki

25. More than Just Value: A Look Into the Depths of PROC FORMAT, Pete Lund [JUST, UPCASE, CNTLIN]

26. Multiple Facts about Multilabel Formats, Gwen Babcock

27. SAS Software Formats: Going Beneath the Surface, Roger Staum

28. My Friend the SAS® Format, Andrew Karp

29. Formats, Informats and How to Program with Them, Ian Whitlock

30. PROC FORMAT: An Analyst’s Buddy, Ben Kupronis [Proc Tabulate]

31. Table Look-Up Techniques: Is the FORMAT Procedure the Best Tool for the Job?, Andrew Kuligowski, Swati Agarwal

32. IN & OUT of CNTL with PROC FORMAT, Nancy Patton [CNTLOUT]

33. How Do We CNTL It?, Janet Stuelpner [CNTLIN, CNTLOUT, SEXCL, EEXCL, HLO]

34. Formatting Data in SAS – Easy and Powerful, Leanne Tang

35. PROC FORMAT is Our Friend, Erin Christen

36. Using the New Features in PROC FORMAT, Rick Langston [Proc FCMP]

37. New Tricks for an Old Tool: Using Custom Formats for Data Validation and Program Efficiency, David Riba [QC Tools]

38. Ordering PROC FREQ Around, Jonathan Kerman [Formatted Order]

39. Applying INVALUE STATEMENT in PROC FORMAT for Ordering Character Variables, Prajitha Nair

40. Formats and Informats – Concepts and Quick Reference Table, Emmy Pahmer [CNTLOUT]

41. Jazz it up a Little with Formats, Brian Bee [_SAME_]

42. Overview of SAS proc format and code for combining formats catalogs [Cancel formats]

Powered by Wild Apricot Membership Software