Chapter 12: Formatting SAS Data Values

1 Proc Format1 Proc Format11 PROC FORMAT Softco WITH FORMATTED ValuesObs Name Division Years Sales Expense State1 CHRIS HARDWARE Under 5 WI2 MARK HARDWARE 5-10 WI3 SARAH SOFTWARE 5-10 MN4 PAT HARDWARE Under 5 IL5 JOHN HARDWARE 5-10 WI6 WILLIAM HARDWARE Over 10 MN7 ANDREW SOFTWARE Over 10 MN8 BENJAMIN SOFTWARE Under 5 IL9 JANET SOFTWARE Under 5 WI10 STEVE HARDWARE Over 10 WI11 JENNIFER SOFTWARE Under 5 IL12 JOY SOFTWARE Over 10 WI13 MARY SOFTWARE Over 10 WI14 TOM SOFTWARE 5-10 MN15 BETH HARDWARE Over 10 WIProc Format2 Proc Format22 Welcome to Systems Seminar Consultants, Seminar Consultants, Inc.

2 Is a SAS Alliance Quality Partner of SAS. Our team of SAS software experts has a broad base of knowledge and experience working with a variety of complex systems in a number of diverse industry settings. This knowledge and experience is leveraged to help you effectively achieve your business SAS NewsletterOur popular publication, The Missing Semicolon , shares SAS software solutions developed by our staff and provides additional technical assistance to our Training ServicesFor over 1,000 students each year, we make SAS software easier to understand, use, and support. Public training schedules are posted on our web site. Private on-site training options are also Format3 Proc Format33 Additional SAS Services SAS Consulting ServicesOur staff of SAS consultants is well-versed in a variety of business areas. Our specialty areas include: data systems development Decision support and business consultation Market research and analysisSAS Help Desk ServicesOur SSC team of experts is available to solve your company's daily SAS problems.

3 Call us to develop a customized support plan that meets your company's More InformationTo receive additional information about our services or discuss a specificcost-effective solution for your company: Call us at: (608) 278-9964 Check our Website at: Contact us at: 2997 Yarmouth Greenway Drive, Madison, WI 53711 Proc Format4 Presenter44 Steven J. First, President Over 30 years of SAS experience, including hundreds of manufacturing, retail, government, marketing, and financial applications. Over 25 years as President and Founder of SSC Founder of WISAS and WISUG Invited speaker at local, regional, and international user groupsProc Format5 Proc Format5 Objectives Participants completing this session will: Understand what SAS formats are. Use SAS written formats to display Values . Be able to build user written formats. Use user written formats to make Values look better Use formats for grouping and aggregating Values .

4 Create formats from a SAS dataset. Create, store and retrieve user written/permanent Format6 Dataset Used in This PresentationThe SOFTCO, INC. company has gathered information about their salespeople so that they can create reports and statistics on sales and is used to create the SAS dataset H 2 WIMARK H 5 WISARAH S 6 MNPAT H 4 ILJOHN H 7 WIWILLIAM H 11 MNANDREW S 24 MNBENJAMIN S 3 ILJANET S 1 WISTEVE H 21 WIJENNIFER S 1 ILJOY S 12 WIMARY S 14 WITOM S 5 MNBETH H 12 WI Proc Format7 Dataset Used in This Presentation -InputThe file contains:NameEmployee name DivisionDivision employed (Hardware or Software) YearsYears with the company SalesSales for the week ExpenseExpenses for the week StateEmployee locationProc Format8 PROC PRINT of the Softsale DatasetProc Print of the Softsale DatasetObs Name Division Years Sales Expense State1 CHRIS H 2 WI2 MARK H 5 WI3 SARAH S 6 MN4 PAT H 4 IL5 JOHN H 7 WI6 WILLIAM H 11 MN7 ANDREW S 24 MN8 BENJAMIN S 3 IL9 JANET S 1 WI10 STEVE H 21 WI11 JENNIFER S 1 IL12 JOY S

5 12 WI13 MARY S 14 WI14 TOM S 5 MN15 BETH H 12 WINotes: What do the division codes mean? Instead of a string of numbers, could Sales and Expense be displayed with commas and dollar signs? Proc Format9 Proc Format9 Displaying SAS data Values When we print or display the information in a SAS dataset, by default, SASdetermines the best way to display the Values . FORMATS allow us tochange the way the data Values appear and can make them more presentable and easier to understand. FORMATS can be specified in the data step. FORMATS can be overridden in the PROC step. You can use a SAS-written format. You can create user-written formats. Notes: **FORMATS do not change the Values in the SAS dataset, they only change the way the Values are printed/displayed.

6 **Proc Format10 Proc Format10 Displaying SAS data Values Using formats is a 4-step or identify the SAS dataset you want to use. the variables that could benefit from the name of the SAS format you want use or create your own. the variable with the format you want to use for displaying the :FORMAT variables format .. ;Proc Format11 Proc Format11 What is a FORMAT?A FORMAT is a routine that a variable value passes through when :1. Sales variable has a value of Sales with dollar signs and a total of 10 digits (including $ , .). two digits after the decimal Sales ;0009432 15900000^Logic to insert$ , .$9, formatProc Format12 Proc Format12 SAS Provided Formats (Partial list) There are many ways to print/display data Values . Numeric numeric with decimal chooses best notation in numbers dollar sign, commas notation hexadecimal binary decimal ROMANw.

7 Roman numerals SSNw. Social Security Numbers as words leading zeros decimal Proc Format13 Proc Format13 SAS Provided Formats (continued) Character Formats:$ character $CHARw. character with leading blanks $ hexadecimal $VARYINGw. variable length character Notes: specify the width to allow for output. For numerics, if you don't give a format, SAS uses If you don't specify a format, SAS chooses Format14 Proc Format14An Example Using SAS Provided FORMATs Formats can be coded in the data step. data Softsale;Infile Rawin;Input @1 Name $10.@12 Division $1.@15 Years 2.@19 Sales @28 Expense @36 State $2.;FORMAT Name $4. Sales Expense ; Run;Proc Print data =Softsale; Title 'Proc Print WITH FORMATS'; Run;CHRIS H 2 WIMARK H 5 WISARAH S 6 H 12 WIfilerefRawinProc Format15 Proc Format15 The Resulting OutputNotes: These formats are stored in the data descriptor.

8 All later steps will use these formats unless Print WITH FORMATSObs Name Division Years Sales Expense State1 CHRI H 2 WI2 MARK H 5 WI3 SARA S 6 MN4 PAT H 4 4, IL5 JOHN H 7 WI6 WILL H 11 3, MN7 ANDR S 24 1, MN8 BENJ S 3 IL9 JANE S 1 WI10 STEV H 21 6, 1, WI11 JENN S 1 IL12 JOY S 12 2, WI13 MARY S 14 5, 2, WI14 TOM S 5 5, MN15 BETH H 12 4, WIProc Format16 Proc Format16 Another FORMATE xample Formats can also be coded in the PROC step.

9 data Softsale;Infile Rawin;Input @1 Name $10.@12 Division $1.@15 Years 2.@19 Sales @28 Expense @36 State $2.;Run;Proc Print data =Softsale; Title 'Proc Print WITH FORMATS';FORMAT Name $4. Sales Expense ;Run; FilerefRawinCHRIS H 2 WIMARK H 5 WISARAH S 6 H 12 WIProc Format17 Proc Format17 The Resulting Output Notes: These FORMATS are used in this step only. Proc Print WITH FORMATSObs Name Division Years Sales Expense State1 CHRI H 2 WI2 MARK H 5 WI3 SARA S 6 MN4 PAT H 4 4, IL5 JOHN H 7 WI6 WILL H 11 3, MN7 ANDR S 24 1, MN8 BENJ S 3 IL9 JANE S 1 WI10 STEV H 21 6, 1, WI11 JENN S 1 IL12 JOY S 12 2, WI13 MARY S 14 5, 2, WI14 TOM S 5 5.

10 MN15 BETH H 12 4, WIProc Format18 Proc Format18 Creating Your Own FORMATS PROC FORMAT creates FORMATs using Picture and value statements. Syntax:PROC FORMAT options; VALUEName(options) Range1 = 'Label1' Range2 = 'Label2' ..; PICTUREName(options) Range1 = 'Picture1'(options) Range2 = 'Picture2'(options) ..; RUN:Notes: Keywords: LOW, HIGH, OTHERProc Format19 Proc Format19 Creating Your Own FORMATS (continued) Specifying FORMAT names: are 1-32 characters long (8 characters in V8 and earlier) must start with a letter or underscore must begin with $ for character variables cannot end with a number do not end in period when created DO end in period when usedProc Format20 Proc Format20 The Values tatementPrint labels corresponding to a range instead of an actual value . Ranges can be a single value :VALUERESPFMT1 = 'Agree' 2 = 'Disagree';Ranges can specify a range of Values :VALUEGRADEFMTLOW-69 = 'FAILING'70-HIGH = 'PASSING';Ranges can specify a string of Values :VALUEQLTYFMT1,10-20,30-HIGH = 'ACCEPT'OTHER = 'REJECT';Proc Format21 Proc Format21 The Values tatement (continued)Ranges can specify a combination: VALUEGRADEFMT 1-69 = 'FAILING'70-HIGH = 'PASSING'0 = 'INCOMPLETE';Character fields require a "$" format: value $STATFMT'AZ' = 'ARIZONA''TX' = 'TEXAS'; value $DIVFMT'H' = 'HARDWARE''S' = 'SOFTWARE'OTHER = 'INVALID'; Proc Format22 Proc Format22A PROC FORMATE xample Create some formats for Softsale;Infile Rawin;Input @1 Name $10.