Transcript
®
Modernizing Your SAS Code: Defining and Applying Formats
Transcript
Modernizing Your SAS® Code: Defining and Applying Formats Transcript was developed by Warren Repole. Additional contributions were made by Linda Jolley. Editing and production support was provided by the Curriculum Development and Support Department. SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. Other brand and product names are trademarks of their respective companies. Modernizing Your SAS® Code: Defining and Applying Formats Transcript Copyright © 2011 SAS Institute Inc. Cary, NC, USA. All rights reserved. Printed in the United States of America. No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by any means, electronic, mechanical, photocopying, or otherwise, without the prior written permission of the publisher, SAS Institute Inc. Book code E1860, course code RLSPFMTS, prepared date 13Jun2011.
RLSPFMTS_001
ISBN 978-1-60764-824-6
For Your Information
Table of Contents Lecture Description ...................................................................................................................... v Prerequisites ................................................................................................................................ vi Accessibility Tips ........................................................................................................................ vi Modernizing Your SAS® Code: Defining and Applying Formats ................................ 1 1.
Displaying Negative Values in Parentheses ........................................................................ 7
2.
Displaying Decimal Values as Percentages ...................................................................... 13
3.
Specifying a Nested Format Definition ............................................................................ 19
4.
Displaying File Sizes as KB, MB, and GB ....................................................................... 25
5.
Creating a Custom Date Format ....................................................................................... 30
6.
Changing the Delimiter in Common Date Formats .......................................................... 35
7.
Displaying Datetime Values as AM or PM ....................................................................... 41
8.
Referencing Permanent User-Defined Formats ................................................................ 46
Appendix A
Demonstration Programs .................................................................. A-1
1.
Displaying Negative Values in Parentheses .................................................................... A-3
2.
Displaying Decimal Values as Percentages .................................................................... A-4
3.
Specifying a Nested Format Definition .......................................................................... A-6
4.
Displaying File Sizes as KB, MB, and GB ..................................................................... A-8
5.
Creating a Custom Date Format ..................................................................................... A-9
6.
Changing the Delimiter in Common Date Formats ...................................................... A-10
7.
Displaying Datetime Values as AM or PM ................................................................... A-12
8.
Referencing Permanent User-Defined Formats ............................................................ A-13
iii
iv
For Your Information
For Your Information
v
Lecture Description This e-lecture shows how to apply recent enhancements and overlooked features of the SAS programming language that are related to formats. The resulting code solutions are designed to be easy to understand, simple to maintain, more robust for a variety of uses, and, in some cases, more efficient during execution.
To learn more… For information on other courses in the curriculum, contact the SAS Education Division at 1-800-333-7660, or send e-mail to
[email protected]. You can also find this information on the Web at support.sas.com/training/ as well as in the Training Course Catalog.
For a list of other SAS books that relate to the topics covered in this Course Notes, USA customers can contact our SAS Publishing Department at 1-800-727-3228 or send e-mail to
[email protected]. Customers outside the USA, please contact your local SAS office. Also, see the Publications Catalog on the Web at support.sas.com/pubs for a complete list of books and a convenient order form.
vi
For Your Information
Prerequisites Before listening to this lecture, you should be familiar with the FORMAT statement and FORMAT procedure. You can gain this knowledge by completing the SAS® Programming 1: Essentials course.
Accessibility Tips If you are using a screen reader, such as Freedom Scientific’s JAWS, you may want to configure your punctuation settings so that characters used in code samples (comma, ampersand, semicolon, percent) are announced. Typically, the screen reader default for the character & is to read “and.” For clarity in code samples, you may want to configure your screen reader to read & as “ampersand.” In addition, depending on your verbosity options, the character & might be omitted. The same is true for some commas before a code variable. To confirm code lines, you may choose to read some lines character by character. When testing this scenario with Adobe Acrobat Reader 9.1 and JAWS 10, ampersands before SAS macro names were announced only when in character-reading mode.
Modernizing Your SAS® Code: Defining and Applying Formats 1.
Displaying Negative Values in Parentheses .................................................................. 7
2.
Displaying Decimal Values as Percentages ................................................................. 13
3.
Specifying a Nested Format Definition......................................................................... 19
4.
Displaying File Sizes as KB, MB, and GB .................................................................... 25
5.
Creating a Custom Date Format ................................................................................... 30
6.
Changing the Delimiter in Common Date Formats ...................................................... 35
7.
Displaying Datetime Values as AM or PM .................................................................... 41
8.
Referencing Permanent User-Defined Formats ........................................................... 46
2
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses
3
Modernizing Your SAS® Code: Defining and Applying Formats
Welcome to the SAS e-Lecture Modernizing Your SAS® Code: Defining and Applying Formats. My name is Warren and I’ll be your instructor for this lecture. This material has been adapted from my publication "Don't Be a SAS® Dinosaur: Modernize Your SAS® Programs" that inspired a number of SAS Users Group presentations. In this lecture you’ll learn how to implement solutions using recent enhancements and frequently overlooked features related to creating and using SAS formats. The result should be code that is simpler to understand and easier to maintain, as well as more robust and, in some cases, more efficent. You should be familiar with basic SAS programming using the FORMAT statement to apply a format to a variable for display purposes and the FORMAT procedure to create a user-defined format. You should also be familiar with formats that pertain to SAS date values and the OPTIONS statement to set SAS system option settings. It is helpful if you have experience with user-defined PICTURE formats, but most of the examples should lead you away from that approach, so it's not a problem if you haven't seen them before.
4
Modernizing Your SAS® Code: Defining and Applying Formats
Reference Materials
To access the transcript for this lecture, click the Transcript button in the bottom right corner of the browser.
2
Before we begin the lecture, let me mention that we have included a transcript so that you can print all of the technical information provided in this lecture. To access the transcript, please select Transcript as in the example shown here. You can print this transcript now for use when viewing the lecture or print it later to keep as a reference. Also, note that Appendix A in the transcript contains copies of the programs used for the demonstrations in this lecture.
1. Displaying Negative Values in Parentheses
Navigation Help For information about how to navigate this lecture, complete the following: 1. Click the Help and Support button in the bottom right corner of the browser. 2. Select Help and Troubleshooting.
3
If you need help with the navigation of this lecture, please select Help as shown here.
5
6
Modernizing Your SAS® Code: Defining and Applying Formats
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition
4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 4
The first two sections deal with displaying numeric values with special characters that designate negative or percentage values. Sections 3 and 4 relate to using formats that display data differently based on the nature of the underlying value. The next three sections discuss formatting date and datetime values. The final section shows an alternate method of accessing permanent user-defined formats.
1. Displaying Negative Values in Parentheses
1.
Displaying Negative Values in Parentheses
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition
4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 5
Section 1 covers displaying negative values in parentheses.
7
8
Modernizing Your SAS® Code: Defining and Applying Formats
Objectives
Review the PICTURE statement approach to displaying negative values surrounded by parentheses.
Use the NEGPAREN format to display negative values surrounded by parentheses.
6
We will review the PICTURE statement approach to displaying negative values surrounded by parentheses. Then we'll see how the NEGPAREN format, available since SAS 6, simplifies the task.
1. Displaying Negative Values in Parentheses
Using the PICTURE Statement Challenging elements of a picture format definition:
Zero versus nonzero digit selectors
Message characters (non-digits)
Prefix characters
Value multipliers
Right-alignment of template
7
There are many challenging elements of defining a PICTURE format.
You must indicate the proper digit selectors and message characters within the picture template. You might need a leading (or prefix) character in front of the displayed value. A multiplier must be applied in some cimcumstances. The picture template is always right-aligned when the display is rendered.
9
10
Modernizing Your SAS® Code: Defining and Applying Formats
PICTURE Format for Negative Values in Parentheses
picture negative low -< 0 = "000,009.99)" (prefix="(") 0 - high = "000,009.99 " ;
Zero must be excluded from the range of negative values
Trailing parenthesis is message character
Leading parenthesis is prefix character
Trailing blank for positive values supports consistent alignment
Fixed number of digits and decimal places
Comma used as digit separator
8
The definition of a PICTURE format to display negative values with parentheses involves several major elements. The range of negative values must not include zero. The < character is required in the associated range declaration. The trailing parenthesis is provided as a message character in the picture itself while the leading parenthesis is specified through the PREFIX option. A trailing blank must be part of the positive value picture to assure that positive values are properly aligned with negative values. Otherwise, the final digit of a positive value will be right-aligned with the closing parenthesis of a negative value. The number of digits and decimal places is fixed, determined by the picture definition. The comma is explicitly specified as the separator character.
1. Displaying Negative Values in Parentheses
11
The NEGPARENw.d Format Features of the NEGPARENw.d format:
Negative values enclosed in parentheses, if width is sufficient
If width is insufficient, minus sign is used
Leading and trailing blank for nonnegative values
Digit separator is comma
9
The NEGPAREN format is designed to display negative values within parentheses. As with most numeric formats, you can choose both the desired width and number of decimal places when applying the format. When choosing a width, be sure to account for the parentheses. If the width is sufficient, parentheses will surround negative values. Otherwise, a leading minus sign is used. To align positive and negative values consistently, a leading and trailing blank are displayed with nonnegative values. Commas are used as digit separators by the NEGPAREN format.
12
Modernizing Your SAS® Code: Defining and Applying Formats
Displaying Negative Values in Parentheses This demonstration illustrates how to use the NEGPAREN format to display a negative value surrounded by parentheses.
10
Let's see how to display negative values with parentheses in a demonstration. The PICTURE format approach defines the NEGATIVE format as shown earlier, with six digits before the decimal point and two decimal places plus a comma separator. When viewing the PROC PRINT output, notice that the heading for the corresponding variable is leftaligned even though the data values are right-aligned. With the NEGPAREN format, the number of digits and decimal places can be controlled on a case-bycase basis through the w.d portion of the format specification. The separator is always a comma. When using NEGPAREN, the PROC PRINT column heading is right-aligned as with all other numeric variables. Now let's turn our attention to percentage values.
2. Displaying Decimal Values as Percentages
2.
Displaying Decimal Values as Percentages
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses
2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition
4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 11
In Section 2, decimal values will be displayed as percentages with trailing percent signs.
13
14
Modernizing Your SAS® Code: Defining and Applying Formats
Objectives
Review the PICTURE statement approach to displaying percentages with a trailing percent sign.
Use the PERCENT format to display percentages with a trailing percent sign.
Use the PERCENTN format to display negative percentages with a leading minus sign.
12
We will review the PICTURE statement approach to displaying percentages with a trailing percent sign. Then we'll see how the PERCENT format, available since SAS 6, simplifies the task but displays negative percentages surrounded by parentheses. Finally we'll use the PERCENTN format, new in SAS 9.2, to display negative percentages with a leading minus sign.
2. Displaying Decimal Values as Percentages
15
PICTURE Format for Percentages with Trailing %
picture pcntpar (round) low-<0="009.9%)" (mult=1000 prefix="(") 0-high="009.9% " (mult=1000) ;
Trailing % is message character
Trailing parenthesis is message character
Leading parenthesis is prefix character
Trailing blank supports consistent alignment
Fixed digits and decimal places
MULT= adjusts values to picture digit positions
ROUND option rounds final digit
0.345 345 34.5%
0.3456 345.6 346 34.6%
13
The definition of a PICTURE format to display percentages with percent signs involves several major elements. The percent sign is a message character in all ranges. As before, the trailing parenthesis is provided as a message character, the leading parenthesis is specified through the PREFIX option, a trailing blank for positive values assures consistent alignment, and the number of digits and decimal places is fixed. The MULTIPLIER= option (abbreviated as MULT=) converts the decimal representation of a percentage into the corresponding integer value. However, because we are displaying one decimal place as well, we must multiply by 1000 rather than 100. For example, the value 0.345 must be converted into the digit sequence 345 so that the decimal point can be placed in front of the final digit, resulting in 34.5%. The ROUND option rounds the final digit after the multiplier conversion rather than truncating extra digits. As a result, the value 0.3456 is displayed as 34.6% rather than 34.5%.
16
Modernizing Your SAS® Code: Defining and Applying Formats
The PERCENTw.d Format Features of the PERCENTw.d format:
Values automatically multiplied by 100
BESTw.d format applied to result
Trailing % is displayed
Negative values enclosed in parentheses
Leading and trailing blank for nonnegative values
14
The PERCENT format is designed to display percentages with trailing percent signs. As with most numeric formats, you can choose both the desired width and number of decimal places when applying the format. Negative percentages are surrounded by parentheses. When choosing a width, be sure to account for the percent sign and potential parentheses, even if all of your values are positive. Each value is multiplied by 100 and then formatted as with the BEST format before the percent sign and parentheses are added. To align positive and negative percentages consistently, a leading and trailing blank are displayed with nonnegative values.
2. Displaying Decimal Values as Percentages
17
The PERCENTNw.d Format Features of the PERCENTNw.d format:
Values automatically multiplied by 100
BESTw.d format applied to result
Trailing % is displayed
Negative values use minus sign
Trailing blank for all values
Same as PERCENTw.d
15
The PERCENTN format is similar to PERCENT but displays negative percentages with a leading minus sign rather than parentheses. When choosing a width, be sure to account for the percent sign and potential minus sign, even if all of your values are positive. Be aware that the PERCENTN format also inserts a trailing blank for all values, so you must account for that in the width, too.
18
Modernizing Your SAS® Code: Defining and Applying Formats
Displaying Decimal Values as Percentages This demonstration illustrates how to use the PERCENT and PERCENTN formats to display a decimal value as a percentage with a trailing percent sign.
16
Let's see how to apply formats to display percentages. The PICTURE format approach shown earlier defines one decimal place prior to the percent sign. As before, the heading for the picture format variable is left aligned. Let's try the standard PERCENT format. We are using PERCENT8.1 to allow three digits in front of the decimal point, the decimal point itself, one decimal digit, the percent sign, and possibly the two parentheses for negative values. When using PERCENT, the PROC PRINT column heading is right aligned as with all other numeric variables. To display negative percentages using minus signs, the trailing characters are removed from the picture format definition and the prefix character is changed to the hyphen. The format width can be reduced by 1. With the standard PERCENTN format, a trailing blank is always displayed, so the same width must be used as with the PERCENT format. Now back to the slides…
3. Specifying a Nested Format Definition
3.
Specifying a Nested Format Definition
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages
3. Specifying a Nested Format Definition 4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 17
In Section 3, a nested format definition is created to handle data ranges that require distinctly different handling.
19
20
Modernizing Your SAS® Code: Defining and Applying Formats
Objectives
Review the DATA step logic that creates a formatted character string to simulate conditional formatting.
Use a nested format definition in the VALUE statement of the FORMAT procedure.
18
We will review DATA step logic that creates a formatted character string, simulating conditional formatting. Then we'll use a nested format definition in the VALUE statement of PROC FORMAT, available since SAS 6, to accomplish the same functionality.
3. Specifying a Nested Format Definition
Displaying Dates at Various Levels of Detail
Date Range
Format
Displayed Values
Prior to current year
YEAR4.
2007 2008 2009
Current year, prior to current month
MONYY7.
JAN2010 FEB2010 MAR2010
Current month
DATE9.
18APR2010 19APR2010 21APR2010
(assume April 2010)
19
Perhaps we want to display dates in different ways based on how close they are to the current date. Dates in the current month need to be fully displayed, with the day included. The DATE9. format will accomplish this. Other dates in the current year do not require the day number. The MONYY7. format is sufficient. Dates in prior years do not require the month either. We can apply the YEAR4. format.
21
22
Modernizing Your SAS® Code: Defining and Applying Formats
DATA Step Logic for Conditional Formatting if Order_Date ge "01apr2010"d then DateString=put(Order_Date,date9.); else if Order_Date ge "01jan2010"d then DateString=put(Order_Date,monyy7.); else DateString=put(Order_Date,year4.);
18APR2010 19APR2010 2007 2008 2009 21APR2010 FEB2010 JAN2010 MAR2010 20
To convert each date based on a different format, we could use the PUT function in a DATA step to create a character variable. The format applied for the date-to-string conversion is controlled with IFTHEN conditional logic. The dates can be displayed as desired. Unfortunately, when we attempt to analyze by date, the strings are organized alphabetically rather than chronologically.
3. Specifying a Nested Format Definition
Nested Formats in the VALUE Statement value daterang "01apr2010"d-high=[date9.] "01jan2010"d-"31mar2010"d=[monyy7.] other=[year4.] ;
2007 2008 2009 JAN2010 FEB2010 MAR2010 18APR2010 19APR2010 21APR2010 21
In the VALUE statement of PROC FORMAT, we can build a user-defined format and associate other formats with each range. This is accomplished by specifying the desired format in brackets instead of supplying a text label. Standard SAS formats and other user-defined formats can be referenced in a nested format definition. When a nested format involving dates is used during analysis, the date ranges are processed chronologically.
23
24
Modernizing Your SAS® Code: Defining and Applying Formats
Specifying a Nested Format Definition This demonstration illustrates how to use a nested format definition in the VALUE statement of PROC FORMAT.
22
Let's see a demonstration of nested format definitions. For the data used in this demonstration, we assume that the current month is December 2007. Dates in that month should be fully displayed. Other dates in 2007 are displayed without the day number. Dates prior to 2007 are displayed as just the year. The DATA step creates the correct formatted dates as strings, but procedures such as PROC FREQ handle the strings as character values and group the values alphabetically. The nested format approach permits us to define a single display format named DATERANG that can be applied directly to the date values. Because our user-defined format involves date-based formats, PROC FREQ interprets the dates in the desired chronological sequence. With some application of the macro language, a format can be defined that always displays dates based on a scenario such as current month / current year / prior year. Please review the code shown in Appendix A. Now back to the slides…
4. Displaying File Sizes as KB, MB, and GB
4.
25
Displaying File Sizes as KB, MB, and GB
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition
4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 23
Let's see how to display file size values with different suffixes depending on the magnitude of the value.
26
Modernizing Your SAS® Code: Defining and Applying Formats
Objectives
Review the PICTURE statement approach to displaying file sizes with a KB, MB, or GB suffix.
Use the SIZEKMG format to display file sizes in terms of KB, MB, or GB.
24
We will review the PICTURE statement approach to displaying file sizes in terms of kilobytes, megabytes, and gigabytes. Then we'll see how the SIZEKMG format, new in SAS 9.2, simplifies the task.
4. Displaying File Sizes as KB, MB, and GB
27
PICTURE Format for File Size Magnitudes proc format; picture mykmg 0 -< 1048576 = '009.9KB' (mult= 0.009765625) 1048576 -< 1073741824 = '009.9MB' (mult= 9.5367431640625E-6) 1073741824 - high = '000,009.9GB' (mult= 9.3132257461548E-9) ; run;
Unit
Number of Bytes
Multiplier (to display 1 decimal place)
Kilobyte (KB)
1,024
0.009765625
Megabyte (MB)
1,048,576
0.0000095367431640625
Gigabyte (GB)
1,073,741,824
0.0000000093132257461548
25
We can define a picture format that attaches a suffix of KB, MB, or GB to an adjusted file size measured in bytes. The number of bytes in a megabyte and gigabyte must be specified in the ranges. The corresponding multipler must be used as well. All of these values are derived from binary figures, not powers of 10. File sizes at or beyond 1 terabyte are displayed in gigabytes.
28
Modernizing Your SAS® Code: Defining and Applying Formats
The SIZEKMGw.d Format Features of the SIZEKMGw.d format:
Best suffix (KB, MB, GB) is automatically determined
Numeric value divided by bytes per unit Related Format
Suffix
Original Value Divided by
SIZEKBw.d
KB
1,024
SIZEKw.d
K
1,024
26
The SIZEKMG format determines the best suffix based on the magnitude of the data value and then divides the value by the corresponding bytes per unit. The related SIZEKB and SIZEK formats display numeric values with a KB and K suffix, respectively, after dividing by 1024, the number of bytes in a kilobyte. There is a SIZEKMG informat that can read numeric values in the equivalent form with a suffix of KB, MB, or GB.
4. Displaying File Sizes as KB, MB, and GB
29
Displaying File Sizes as KB, MB, and GB This demonstration illustrates how to use the SIZEKMG format to display file size values in terms of kilobytes, megabytes, and gigabytes.
27
Let's begin the demo for the SIZEKMG format. The PICTURE statement in this demo program is generated using macro language code. Instead of hardcoding the figures for bytes per megabyte and gigabyte, exponential expressions are used. The multipliers are computed, too. Because of the size and required precision of the results, the %SYSEVALF function was used to perform the computations. In the subsequent SQL query using DICTIONARY.TABLES, the new format is applied to the FileSize column that represents the size (in bytes) of a SAS data file. Only the data sets stored in the demonstration data library Orion are included. A similar query report is created using the SIZEKMG format. Now back to the slides for our next task…
30
Modernizing Your SAS® Code: Defining and Applying Formats
5.
Creating a Custom Date Format
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition
4. Displaying File Sizes as KB, MB, and GB
5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 28
In Section 5, we will address how to create a custom date format if none of the standard SAS formats meets our needs.
5. Creating a Custom Date Format
Objectives
Review the DATA step logic that creates a custom character string that represents a date value.
Use date directives in the PICTURE statement.
29
We will review the DATA step logic that creates a custom character string that represents a date value. Then we'll use date directives in a PICTURE statement, available since SAS 8, to build a custom date format.
31
32
Modernizing Your SAS® Code: Defining and Applying Formats
DATA Step Logic for Custom Date Formatting Required form:
01-January-1960
DateString=put(day(Employee_Hire_Date),z2.)|| "-"|| trim(left(put(Employee_Hire_Date,monname.)))|| "-"|| put(Employee_Hire_Date,year4.);
DAY function to extract day number – PUT function with Z format for leading zero with day number
PUT function with MONNAME format to obtain month name
– Remove leading blanks from formatted month name
PUT function with YEAR format to obtain year number
Concatenation operator to combine elements with dash separators
30
Suppose that we need to display dates in the form day number, dash, month name, dash, four-digit year. There is no standard SAS format that displays dates in that way. As illustrated with the nested format task, a DATA step expression can create a character string equivalent of a date in any text form. This example uses numerous functions and concatenation operations to construct such a string. The DAY function extracts the day number from a date. The PUT function can convert a date into one of its component elements, such as month name and four-digit year. The || concatenation operator combines the elements while inserting dashes as separators. Other functions manipulate the text to create the exact string for our date structure.
5. Creating a Custom Date Format
33
PICTURE Formats with Date Directives picture mydate (default=17) .="Missing date" other='%0d-%B-%Y' (datatype=date) ; Date directive
Definition
Sample result
%d
Day of the month
5
%m
Month number
11
%b
Month abbreviation
NOV
%B
Month name
November
%y
Year without century
10
%Y
Year with century
2010
31
It is much easier to accomplish the same thing by creating a PICTURE format using date directives. Such a format can be used anywhere that a date must be displayed in the desired structure. Date directives are case-sensitive letter codes preceded by a percent sign. The DATATYPE=DATE option activates the use of date directives. For example, the day of the month is requested with %d, and the month number is %m. Leading zeros are not included, but you can request them by inserting a zero between the percent sign and the letter. %b represents an abbreviated month name while %B represents the full month name. The year can include the century or omit the century portion, depending on whether you specify an uppercase or lowercase Y. You must request leading zeros if a year without the century portion falls between 00 and 09. Message characters are inserted as separators. Use the DEFAULT= option to provide a sufficient length for the expanded date display. Otherwise, the display will be limited to the number of characters in the directive string itself. Missing date values, if not handled explicitly, are displayed as the word ERROR when using date directives, so this example contains an explicit missing value range.
34
Modernizing Your SAS® Code: Defining and Applying Formats
Creating a Custom Date Format This demonstration illustrates how to use date directives in the PICTURE statement to create a custom date format.
32
Let's see how date directives work. First, here is the DATA step code to create the custom date strings. The correct text values can be displayed in subsequent steps, but the value is a character string and cannot be analyzed as a date. Now let's see the PICTURE statement that was discussed in the slides. The directive string is only 9 characters, not enough to display all parts of the date as desired, so the default width is set to 17 characters. That is sufficient for a two-digit day, a four-digit-year, two dash separators, and the longest full month name, September, which is nine letters. Missing date values will be displayed as the string "Missing date" (which fits within the default width) while all nonmissing dates will be formatted as requested. This user-defined format can be used anywhere that SAS date values are referenced. Let's return to the slides for another date formatting task.
6. Changing the Delimiter in Common Date Formats
6.
35
Changing the Delimiter in Common Date Formats
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition 4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format
6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 33
What if a standard SAS date format is close to what we need, but the delimiters used to separate the day, month, and year are undesirable? We will address that task in Section 6.
36
Modernizing Your SAS® Code: Defining and Applying Formats
Objectives
Review date directives as a method of constructing a custom date format.
Review the DATA step logic that controls the separator character for a formatted date string.
Use variations of common date formats that permit control of the separator character.
34
We will start with the previously discussed PICTURE format technique using date directives. Then we will review DATA step logic that controls the separator character inserted into a formatted date value stored as a character string. Finally we'll use variations of common date formats, most of them available since SAS 8, that permit control of the separator character.
6. Changing the Delimiter in Common Date Formats
37
PICTURE Format to Insert Dashes within DATE. Form picture mydate (default=12) .="Missing date" other='%0d-%b-%Y' (datatype=date) ;
Date
DATE9. format
January 1, 1960
01JAN1960
missing value
.
MYDATE. format 01-JAN-1960 Missing date
35
In some cases we want to insert separators in a date format where no separators normally appear. The DATE9. format displays a two-digit day, three-character month name abbreviation, and a four-digit year. We would like to apply a similar format that inserts dashes between the elements. By starting with the PICTURE format from the previous section and changing the %B directive to %b to reduce the month name to an abbreviation, we can obtain the desired result.
38
Modernizing Your SAS® Code: Defining and Applying Formats
DATA Step Logic for Custom Date Delimiters DateSlashes=put(Employee_Hire_Date,mmddyy10.); DateDots=translate(DateSlashes,".","/"); Date November 16, 2010
MMDDYY10. form 11/16/2010
After TRANSLATE 11.16.2010
%let NoDelim=%sysfunc(today(),yymmdd10.); %let NoDelim=%sysfunc(compress(&NoDelim,-)); ods html file="Report_&NoDelim..html"; Date
November 16, 2010
YYMMDD10. form
2010-11-16
After COMPRESS
20101116
36
In other situations the default separator is undesirable. For example, MMDDYY uses slashes, and YYMMDD uses dashes. Using the DATA step, we can create a string with the default delimiters and then modify the string to contain different delimiters by applying the TRANSLATE function. We could even eliminate the delimiters completely with the COMPRESS function. This might be a useful approach if the date string represents part of a date-based filename that we are constructing through SAS macro language techniques.
6. Changing the Delimiter in Common Date Formats
39
Variations of Common Date Formats
Date
DATE9. format
DATE11. format
January 1, 1960
01JAN1960
01-JAN-1960
.
.
missing value
Suffixes used with MMDDYYx, YYMMDDx, and DDMMYYx: Format suffix
Definition
Character
B
Blank
C
Colon
:
D
Dash
-
N
None
P
Period
.
S
Slash
/
YYMMDDN8. MMDDYYP10.
37
Starting with SAS 9.2, we can request a width of 11 for the DATE format. With DATE11., the two extra characters are dashes inserted as separators. Missing values are displayed in the same manner as the DATE9. format. Other common date formats have variations that were implemented (starting in SAS 8) by adding a character to the traditional format name. These variations control the separator used (or omitted) when displaying a date value. To locate the documentation for these format variations, add an X to the traditional format name. The suffixes available for the MMDDYYx, YYMMDDx, and DDMMYYx formats, and the corresponding separator characters, are B for blanks C for colons D for dashes N for no separator P for periods S for slashes. Examples corresponding to the previous slide are MMDDYYP10. to display dates as month, day, and year numbers but with a period separator, and YYMMDDN8. to display year, month, and day numbers with no separators.
40
Modernizing Your SAS® Code: Defining and Applying Formats
Changing the Delimiter in Common Date Formats This demonstration illustrates how to use variations of common date formats that enable selection of the character that separates the day, month, and year.
38
Let's look at ways to alter the delimiter used with common SAS date formats. Here is the PICTURE format that inserts dashes within a date that is displayed like the DATE format. This is the technique required prior to SAS 9.2. Simply specifying DATE11. in SAS 9.2 produces the same results. Here is the DATA step approach that converts the delimiters in a standard date format to the desired character by building a text string. Part of the file name of an HTML report is built using similar techniques applied through macro variables. The format variations are used directly in the final part of the demo program. Let's return to the slides for the next task.
7. Displaying Datetime Values as AM or PM
7.
Displaying Datetime Values as AM or PM
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition
4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats
7. Displaying Datetime Values as AM or PM 8. Referencing Permanent User-Defined Formats 39
Ways to display a SAS datetime value with a designation of AM or PM is the subject of Section 7.
41
42
Modernizing Your SAS® Code: Defining and Applying Formats
Objectives
Review the PICTURE statement approach to displaying the elements of a SAS datetime value.
Use the MDYAMPM format to display a SAS datetime value.
40
We will review the PICTURE statement approach to displaying the elements of a SAS datetime value. Then we'll see how the MDYAMPM format, new in SAS 9.2, simplifies the task.
7. Displaying Datetime Values as AM or PM
43
PICTURE Format for Datetime Values with AM or PM picture custampm (default=19) .="Missing datetime" other='%m/%d/%Y %I:%0M %p' (datatype=datetime) ; Time directive
Definition
Sample result
%H
Hour of the day (24 hour clock)
13
%I
Hour of the day (12 hour clock)
1
%M
Minutes
5
%S
Seconds
30
%p
a.m. or p.m.
PM
41
There are PICTURE statement directives that involve components of SAS time values. They can be used with date directives by specifying DATATYPE=DATETIME. As with numeric date directives, leading zeros can be requested for hours, minutes, and seconds by inserting a zero between the percent sign and the letter. All of the listed time directives use uppercase letters except for the a.m./p.m. designation, which uses a lowercase p even though the result is an uppercase two-character value.
44
Modernizing Your SAS® Code: Defining and Applying Formats
The MDYAMPMw. Format The MDYAMPMw. format writes SAS datetime values as mm/dd/yy
hh:mm Comparison of MDYAMPMw. and DATETIMEw.d formats: MDYAMPMw.
DATETIMEw.d
Date format
mm/dd/yyyy
ddMONyyyy
Time format
hh:mm AM | PM (12-hour clock)
hh:mm (24-hour clock)
Seconds displayed?
No
Yes, with decimal places if requested
Separator between date and time
Blank
Colon ( : )
42
The MDYAMPM format displays SAS datetime values with the AM/PM designation. MDYAMPM displays the date portion using the equivalent of the MMDDYY format. This differs from the DATETIME format, which displays the date using the DATE format. MDYAMPM uses a 12-hour clock for the time, adding the AM or PM designation. DATETIME uses a 24-hour clock. MDYAMPM does not display seconds. DATETIME can display seconds and uses decimal places for partial seconds. MDYAMPM separates the date and time with a blank. DATETIME uses a colon between the date and time.
7. Displaying Datetime Values as AM or PM
45
Displaying Datetime Values as AM or PM This demonstration illustrates how to use the MDYAMPM format to display a datetime value with the AM or PM designation.
43
Let's see how the MDYAMPM format works through a demonstration. Here is the PICTURE format definition required prior to SAS 9.2. The DATATYPE= option is set to DATETIME to enable both date directives and time directives. Notice the misalignment of the dates due to a mix of single-digit hours and two-digit hours. To line up the dates using the PICTURE statement approach, a leading zero must be forced in front of each single-digit hour. The MDYAMPM format can be used directly, starting in SAS 9.2. Notice the extra space between the date and time in the MDYAMPM output for single-digit hours. The dates are aligned as if the time portions contained two-digit hours. Now back to the slides for one final section…
46
Modernizing Your SAS® Code: Defining and Applying Formats
8.
Referencing Permanent User-Defined Formats
Modernizing Your SAS® Code: Defining and Applying Formats
1. Displaying Negative Values in Parentheses 2. Displaying Decimal Values as Percentages 3. Specifying a Nested Format Definition
4. Displaying File Sizes as KB, MB, and GB 5. Creating a Custom Date Format 6. Changing the Delimiter in Common Date Formats 7. Displaying Datetime Values as AM or PM
8. Referencing Permanent User-Defined Formats 44
After you store user-defined formats in a permanent SAS library, how do you make them available in a SAS program? We will see a couple of techniques in our final section.
8. Referencing Permanent User-Defined Formats
Objectives
Review the FMTSEARCH= system option syntax to access permanent user-defined formats.
Use the INSERT= system option to modify the setting for the FMTSEARCH= option.
45
We will review the role of the system option FMTSEARCH= (often pronounced "format-search") in accessing permanent user-defined format definitions. Then we'll see how the INSERT= system option, new in SAS 9.2, simplifies the task.
47
48
Modernizing Your SAS® Code: Defining and Applying Formats
Updating the FMTSEARCH= Option Directly Steps to access a new format catalog: 1.Obtain current FMTSEARCH= setting %let OldFMTSEARCH=%qsysfunc(getoption(fmtsearch)); (WORK LIBRARY) 2.Remove the parentheses %let OldFMTSEARCH=%qsysfunc(compress(&OldFMTSEARCH,()));
WORK LIBRARY 3.Reassign FMTSEARCH= option options fmtsearch=(orion orion.MyFmts &OldFMTSEARCH); 46
The steps required to add a permanent format catalog to the FMTSEARCH= option search path involve some macro programming and the manipulation of SAS system options. Step 1. Obtain the current setting of the FMTSEARCH= option. Step 2. Remove the parentheses from the text of the reported setting. Step 3. Reassign the FMTSEARCH= option using an OPTIONS statement.
8. Referencing Permanent User-Defined Formats
49
Updating FMTSEARCH= Using the INSERT= Option options insert=(fmtsearch=(orion orion.MyFmts));
OPTIONS statement from previous solution: options fmtsearch=(orion orion.MyFmts &OldFMTSEARCH);
47
With the INSERT= option, you do not need to know the current setting of the FMTSEARCH= option. The items specified are placed at the start of the FMTSEARCH= search path. The value of the INSERT= option reflects what the FMTSEARCH= assignment would be if the existing setting is ignored. The INSERT= option can be used to update other SAS system options such as SASAUTOS=.
50
Modernizing Your SAS® Code: Defining and Applying Formats
Referencing Permanent User-Defined Formats This demonstration illustrates how to use the INSERT= system option to add format catalogs to the search path for permanent formats.
48
Let's complete a final demo to see how the INSERT= option makes updating the format search path much easier. Let's begin by establishing a value for the FMTSEARCH= option. The combination of WORK and LIBRARY reflects the default setting for the format search path, as if FMTSEARCH= was set to its default value of null. We confirm the setting by running a PROC OPTIONS step. Using the GETOPTION function (through the %QSYSFUNC macro function), we extract the current setting into a macro variable named OldFMTSEARCH and look at the result through a %PUT statement. Before we can use this setting in another OPTIONS statement, we must remove the parentheses. The COMPRESS function (also invoked through %QSYSFUNC) removes left and right parentheses. The result is included at the end of a new FMTSEARCH= declaration in an OPTIONS statement. Running PROC OPTIONS confirms the updated setting. The permanent format locations, the implied FORMATS catalog in the ORION library and the ORION.MYFMTS catalog, precede the original locations in the updated search path for formats. Now we'll reestablish the WORK and LIBRARY value for FMTSEARCH= before trying the SAS 9.2 approach.
8. Referencing Permanent User-Defined Formats
The INSERT= option adds the additional format locations. This is confirmed by running PROC OPTIONS after the OPTIONS statement. The demo program switches FMTSEARCH= back to its default null value.
51
52
Modernizing Your SAS® Code: Defining and Applying Formats
Additional References Topic
Reference
URL at support.sas.com
Displaying percentages with percent signs in PROC TABULATE reports
Sample 38001
support.sas.com/kb/38/001.html
Defining a self-adjusting date range format
Sample 24830
support.sas.com/kb/24/830.html
Defining a picture format using date directives
Usage note 22990
support.sas.com/kb/22/990.html
Using permanent formats in the SAS Platform for Business Analytics
Usage note 40103
support.sas.com/kb/40/103.html
49
The Samples and SAS Notes section of support.sas.com is a valuable resource for learning more about techniques such as those illustrated in this lecture. For instance, sample 38001 describes how to define a format to display percent signs with percentages in PROC TABULATE output. Sample 24830 illustrates how to create a self-adjusting date range format that acts much like a nested format. Usage note 22990 shows the directives available when creating a custom date picture format. Making permanent formats available through the SAS Platform for Business Analytics is covered in usage note 40103.
8. Referencing Permanent User-Defined Formats
Summary
SAS programs should be – simple to understand
– easy to maintain – robust – efficient.
Each release of SAS contains enhancements to the programming language.
Earlier releases contain overlooked but valuable features.
Enhancements and overlooked features often support those goals.
50
In this lecture we’ve addressed several key points. As a SAS programmer, you should aim to write the most appropriate code to complete your task. Ideally, our goals should include writing code that is simple to understand, easy to maintain, robust, and efficent. Each release of SAS software contains numerous enhancements to the SAS programming language. Earlier releases of SAS contain overlooked yet very valuable features as well. These enhancements and overlooked features often support the creation of SAS programs that achieve those goals.
53
54
Modernizing Your SAS® Code: Defining and Applying Formats
Related Training
The following courses provide additional information on creating and using SAS formats:
SAS® Report Writing 1: Using Procedures and ODS
SAS® Programming 3: Advanced Techniques and Efficiencies
This lecture might also be of interest to you:
The FORMAT Procedure: Creating Custom Informats and Date Formats and Other Special Techniques
For a complete list of available e-lectures and other SAS training products, visit: support.sas.com/training 51
Listed here are other courses or lectures that might also interest you. For a complete list of available e-lectures and other SAS training products, please visit the SAS Web site at support.sas.com/training.
8. Referencing Permanent User-Defined Formats
55
Credits Modernizing Your SAS® Code: Defining and Applying Formats was developed by Warren Repole. Additional contributions were made by Linda Jolley.
52
This concludes the SAS e-Lecture Modernizing Your SAS® Code: Defining and Applying Formats. I hope you found the material in this lecture to be helpful to your work tasks. Thank you to everyone who contributed to the creation of this e-lecture.
56
Modernizing Your SAS® Code: Defining and Applying Formats
Comments? We would like to hear what you think.
Do you have any comments about this lecture?
Did you find the information in this lecture useful?
What other e-lectures would you like SAS to develop in the future?
Please e-mail your comments to [email protected]. Or click the Help and Support button and select Send Us Feedback to complete a short evaluation.
53
If you have any comments about this lecture or e-lectures in general, we would appreciate receiving your input. You can use the e-mail address listed here to provide that feedback, or you can complete the short evaluation form.
8. Referencing Permanent User-Defined Formats
Copyright SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. Other brand and product names are trademarks of their respective companies. Copyright © 2011 by SAS Institute Inc., Cary, NC 27513, USA. All rights reserved.
54
Thank you for your time.
57
58
Modernizing Your SAS® Code: Defining and Applying Formats
Appendix A Demonstration Programs 1.
Displaying Negative Values in Parentheses .............................................................. A-3
2.
Displaying Decimal Values as Percentages ............................................................... A-4
3.
Specifying a Nested Format Definition....................................................................... A-6
4.
Displaying File Sizes as KB, MB, and GB .................................................................. A-8
5.
Creating a Custom Date Format ................................................................................. A-9
6.
Changing the Delimiter in Common Date Formats .................................................. A-10
7.
Displaying Datetime Values as AM or PM ................................................................ A-12
8.
Referencing Permanent User-Defined Formats ....................................................... A-13
A-2
Appendix A Demonstration Programs
1. Displaying Negative Values in Parentheses
1.
Displaying Negative Values in Parentheses /**********************************************/ /* DISPLAYING NEGATIVE VALUES IN PARENTHESES */ /**********************************************/
/***********************************/ /* Older technique: PICTURE format */ /***********************************/ proc format; picture negative low-<0 = "000,009.99)" (prefix="(") 0-high = "000,009.99 " ; run; proc print data=orion.Year2Year; id Year; format RevDiff negative12.; title 'Year-to-Year Changes with PICTURE Format'; run; /************************************/ /* Newer technique: NEGPAREN format */ /************************************/ /* Main benefit: Simplicity */ /************************************/ proc print data=orion.Year2Year; id Year; format RevDiff negparen12.2; title 'Year-to-Year Changes with Built-In Format'; run;
A-3
A-4
Appendix A Demonstration Programs
2.
Displaying Decimal Values as Percentages /********************************************/ /* DISPLAYING DECIMAL VALUES AS PERCENTAGES */ /********************************************/
/************************************/ /* Percent with % Sign, Parentheses */ /************************************/ /* Older technique: PICTURE format */ /************************************/ proc format; picture pcntpar (round) low-<0="009.9%)" (mult=1000 prefix="(") 0-high="009.9% " (mult=1000) ; run; proc print data=orion.Year2Year; id Year; format PctDiff pcntpar8.; title1 "Year-to-Year Changes with PICTURE format"; title2 "[Negative Percents in Parentheses]"; run; /***********************************/ /* Newer technique: PERCENT format */ /***********************************/ /* Main benefit: Simplicity */ /***********************************/ proc print data=orion.Year2Year; id Year; format PctDiff percent8.1; title1 "Year-to-Year Changes with Built-In format"; title2 "[Negative Percents in Parentheses]"; run;
2. Displaying Decimal Values as Percentages
/************************************/ /* Percent with % Sign, Minus Sign */ /************************************/ /* Pre-9.2 approach: PICTURE format */ /************************************/ proc format; picture pcntneg (round) low-<0="009.9%" (mult=1000 prefix="-") 0-high="009.9%" (mult=1000) ; run; proc print data=orion.Year2Year; id Year; format PctDiff pcntneg7.; title1 "Year-to-Year Changes with PICTURE format"; title2 "[Negative Percents with Minus Sign]"; run; /************************************/ /* 9.2 approach: PERCENTN format */ /************************************/ /* Main benefit: Simplicity */ /************************************/ proc print data=orion.Year2Year; id Year; format PctDiff percentn8.1; title1 "Year-to-Year Changes with Built-In format"; title2 "[Negative Percents with Minus Sign]"; run;
A-5
A-6
3.
Appendix A Demonstration Programs
Specifying a Nested Format Definition /******************************************/ /* SPECIFYING A NESTED FORMAT DEFINITION */ /******************************************/
/**********************************************/ /* Older technique: Define character strings */ /**********************************************/ data DateRanges; set orion.Order_Fact; if Order_Date ge "01dec2007"d then DateString=put(Order_Date,date9.); else if Order_Date ge "01jan2007"d then DateString=put(Order_Date,monyy7.); else DateString=put(Order_Date,year4.); run; proc freq data=DateRanges; tables DateString; title1 "Distribution of Order Dates"; run; /***************************************/ /* Newer technique: Use nested formats */ /***************************************/ /* Main benefit: Flexibility */ /***************************************/ proc format; value daterang "01dec2007"d-high=[date9.] "01jan2007"d-"30nov2007"d=[monyy7.] other=[year4.] ; run; proc freq data=orion.Order_Fact; tables Order_Date; format Order_Date daterang.; title1 "Distribution of Order Dates"; run;
3. Specifying a Nested Format Definition
A-7
/*****************************************/ /* Dynamic format based on current month */ /*****************************************/ %let CurrentMonth=01%substr(&sysdate9,3); %let LastMonth=%sysfunc(intnx(month,"&CurrentMonth"d,-1,end),date9.); %let YearStart=%sysfunc(intnx(year,"&LastMonth"d,0),date9.); %put Key Dates: &CurrentMonth &YearStart &LastMonth &sysdate9 (SAS date %sysfunc(today())); proc format; value daterang "&CurrentMonth"d - high = [date9.] "&YearStart"d - "&LastMonth"d = [monyy7.] other = [year4.] ; run;
A-8
Appendix A Demonstration Programs
4.
Displaying File Sizes as KB, MB, and GB /********************************************/ /* DISPLAYING FILE SIZES AS KB, MB, AND GB */ /********************************************/
/******************************************/ /* Pre-9.2 approach: Custom PICTURE format */ /******************************************/ %macro makefmt; proc format; picture mykmg 0-<%sysevalf(1024**2)='009.9KB' (mult=%sysevalf(10/(1024**1))) %sysevalf(1024**2)-<%sysevalf(1024**3)='009.9MB' (mult=%sysevalf(10/(1024**2))) %sysevalf(1024**3)-high='000,009.9GB' (mult=%sysevalf(10/(1024**3))) ; run; %mend makefmt; options mprint; %makefmt options nomprint; proc sql; title1 "File Sizes in the ORION library (user-defined format)"; select MemName, FileSize as Bytes label='Bytes' format=comma12., FileSize format=mykmg8. from dictionary.tables where libname='ORION' order by FileSize desc; quit; /********************************/ /* 9.2 approach: SIZEKMG format */ /********************************/ /* Main benefit: Simplicity */ /********************************/ proc sql; title1 "File Sizes in the ORION library (SIZEKMG format)"; select MemName, FileSize as Bytes label='Bytes' format=comma12., FileSize format=sizekmg8.1 from dictionary.tables where libname='ORION' order by FileSize desc; quit;
5. Creating a Custom Date Format
5.
Creating a Custom Date Format /*********************************/ /* CREATING A CUSTOM DATE FORMAT */ /*********************************/
/**********************************************/ /* Older technique: Create character variable */ /**********************************************/ data CustomDate; set orion.Employees; DateString=put(day(Employee_Hire_Date),z2.)||"-"|| trim(left(put(Employee_Hire_Date,monname.)))||"-"|| put(Employee_Hire_Date,year4.); run; proc print data=CustomDate(obs=20) label; id Employee_ID; var Employee_Name Employee_Hire_Date DateString; label DateString="Date String"; format Employee_Hire_Date date9.; title1 "Dates as Character Strings"; run; /***********************************/ /* Newer technique: PICTURE format */ /***********************************/ /* Main benefits: Flexibility */ /* Calendar logic */ /***********************************/ proc format; picture mydate (default=17) .="Missing date" other='%0d-%B-%Y' (datatype=date) ; run; proc print data=orion.Employees(obs=20) label; id Employee_ID; var Employee_Name Employee_Hire_Date; format Employee_Hire_Date mydate.; title1 "Dates with Custom Format"; run;
A-9
A-10
Appendix A Demonstration Programs
6.
Changing the Delimiter in Common Date Formats /*************************************************/ /* CHANGING THE DELIMITER IN COMMON DATE FORMATS */ /*************************************************/
/********************************************/ /* Insert dashes into ddMONyyyy date format */ /********************************************/ /* Pre-9.2 approach: Custom PICTURE format */ /********************************************/ proc format; picture mydate (default=12) .="Missing date" other='%0d-%b-%Y' (datatype=date) ; run; proc print data=orion.Employees(obs=20) label; id Employee_ID; var Employee_Name Employee_Hire_Date; format Employee_Hire_Date mydate.; title1 "Custom date format"; run; /*********************************/ /* 9.2 approach: DATE11. format */ /*********************************/ /* Main benefit: Simplicity */ /*********************************/ proc print data=orion.Employees(obs=20) label; id Employee_ID; var Employee_Name Employee_Hire_Date; format Employee_Hire_Date date11.; title1 "DATE11. format"; run;
6. Changing the Delimiter in Common Date Formats
/*****************************************/ /* Change or remove delimiters in format */ /*****************************************/ /* Older technique: Create date strings */ /*****************************************/ data PeriodDelims; set orion.Employees; DateSlashes=put(Employee_Hire_Date,mmddyy10.); DateDots=translate(DateSlashes,".","/"); format Employee_Hire_Date Date9.; run; ods listing close; %let NoDelim=%sysfunc(today(),yymmdd10.); %let NoDelim=%sysfunc(compress(&NoDelim,-)); ods html file="Older_&NoDelim..html"; proc print data=PeriodDelims(obs=20) label; id Employee_ID; var Employee_Name Employee_Hire_Date DateSlashes DateDots; title1 "Dates with Different Delimiters [DATA step]"; title2 "Can use &NoDelim as part of a file name"; run; ods html close; ods listing; /************************************************/ /* Newer technique: Choose delimiter in format */ /************************************************/ /* Main benefits: Simplicity and flexibility */ /************************************************/ ods listing close; %let NoDelim=%sysfunc(today(),yymmddN8.); ods html file="Newer_&NoDelim..html"; proc print data=orion.Employees(obs=20) label; id Employee_ID; var Employee_Name Employee_Hire_Date; format Employee_Hire_Date mmddyyP10.; title1 "Dates with Different Delimiters [formats]"; title2 "Can use &NoDelim as part of a file name"; run; ods html close; ods listing;
A-11
A-12
Appendix A Demonstration Programs
7.
Displaying Datetime Values as AM or PM /*******************************************/ /* DISPLAYING DATETIME VALUES AS AM OR PM */ /*******************************************/
/********************************************/ /* Pre-9.2 approach - Custom PICTURE format */ /********************************************/ proc format; picture custampm (default=19) .="Missing datetime" other='%m/%d/%Y %I:%0M %p' (datatype=datetime) ; run; proc sql; title1 "Custom datetime format"; select MemName, MoDate format=custampm. from dictionary.tables where libname='ORION' ; quit; /**********************************/ /* 9.2 approach - MDYAMPM format */ /**********************************/ /* Main benefit: Simplicity */ /**********************************/ proc sql; title1 "MDYAMPM. format"; select MemName, MoDate format=mdyampm. from dictionary.tables where libname='ORION' ; quit;
8. Referencing Permanent User-Defined Formats
8.
Referencing Permanent User-Defined Formats /**********************************************/ /* REFERENCING PERMANENT USER-DEFINED FORMATS */ /**********************************************/
/***************************************************/ /* Pre-9.2 approach - Obtain and modify FMTSEARCH= */ /***************************************************/ options fmtsearch=(work library); proc options option=fmtsearch; run; %let OldFMTSEARCH=%qsysfunc(getoption(fmtsearch)); %put NOTE: Old FMTSEARCH= value was &OldFMTSEARCH; /* Must remove parentheses from FMTSEARCH= value */ %let OldFMTSEARCH=%qsysfunc(compress(&OldFMTSEARCH,())); options fmtsearch=(orion orion.MyFmts &OldFMTSEARCH); proc options option=fmtsearch; run; /*************************************/ /* 9.2 approach - Use INSERT= to add */ /*************************************/ /* Main benefit: Simpler code */ /* No macro code */ /*************************************/ options fmtsearch=(work library); options insert=(fmtsearch=(orion orion.MyFmts)); proc options option=fmtsearch; run;
/* Change back to default setting */ options fmtsearch=();
A-13
A-14
Appendix A Demonstration Programs
