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.

* 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;