# SUGI 28: Using Formats And Other Techniques To Complete .

2y ago
15 Views
214.07 KB
6 Pages
Last View : 29d ago
Transcription

SUGI 28Data Presentation(DOLLARXw.d and COMMANw.d). SAS comes with a widevariety of formats. They are described in excruciating detail in theSAS Language Reference Manual. Some common standard SASformats are given below and show the same number displayed indifferent formats.SolutionThe solution is to change how the data is displayed and addcomments. Revenue and Expense have been rounded tothousands, missing values identified by a symbol, value roundedto zero identified, and data associated with 2 or fewer firmssuppressed. The meaning of the cell notes are explained in aspecific footnote at the bottom of the table.RegionfirmsREVENUE(000s)Display of Number -1234.56789 Using Different FormatsEXPENSE(000s) ƒƒƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒ37559(Z)-Rounded to Zero, (M)-Missing,(NM)-Not Meaningful1.350.09(M)(NM)ƒƒƒƒƒ0.62The solution to many of the normal problems of preparing andpresenting tables is to use a combination of formats (either SASdefined or user defined) and the PROC REPORT computestatement. Initial PROC REPORT tables present cell values thatare usually numbers. The final table many times requires a tablethat has a number, a number and a symbol, or just symbol.The cell is the basic object of most tables. A cell can contain: 123,000number and unit-indicator345,678 GBnumber and symbol2,234 rsymbol only(D)DescriptionDisplay12.2Standard numeric-12334.57COMMA12.2Write numeric values withcommas and decimal points-1,234.57COMMAX12.2Writes numeric values withcommas and periods.-1.234,57DOLLARr12.2Write numeric values withdollar signs, commas, anddecimal points -1,234.57NEGPAREN12.2Write negative numeric valuesin parentheses(1,234.57)E12.Scientific notation-1.23457E 03Formats allow the programmer to customize a table to differentaudiences and make numbers more readable (dollar signs,comma separators and parentheses for negative numbers).CELL NOTESnumber onlyFormatTruncating and RoundingRounding is usually expressed in terms such as “rounding to twodecimal places” or “rounding to thousands” . Truncation refers toeither dropping left most digits of a number or replacing the digitswith zeros. In general formats can not be used for rounding;however, they can be used to round the decimal parts of anumber. Formats don’t work for the integer part. If you need toround results in PROC REPORT table, the solution is use acompute statement. This is illustrated in the table below.While the purpose of a statistical table is to display numeric datain table cells, special situations require comments be added to thedata or in place of the data. These comments are called cellnotes are needed because data is missing, has been change insome nonstandard way, are not meaningful, or the sampling erroror imputation rate is too high.proc report data example nowd;columnrow original truncate format;definerow/ order;defineoriginal / display'Original';definetruncate / display format comma18.0'Truncated';defineformat/ display'Rounded';compute truncate ;truncate int (truncate / 100) *100;endcomp;CELLS WITH ONLY NUMBERSMost cells in statistical tables contain only numbers. Changes tocalculated values occur because you want to present the datamore meaningfully by simplifying the numbers thru truncation,rounding, or significant digits. Truncation refers to either droppingsome of the right most digits (123897 becomes 123) or replacingthe dropped numbers with zeros; rounding refers to changing theright most digit displayed depending on whether the first drop digitwas greater than or equal to 5 or less than 5 (123897 rounded tothousands becomes 124000); and significant digits refers tokeeping only the first two or three digits regardless of the positionin the number and changing the others to zero (123897 to twosignificant digits is 120000).compute format;format int (( format 50)/ 100) *100;endcomp;run;The table produced by the PROC REPORT code above is givenbelow.row123456789SAS FormatsSAS supplied formats provide an easy and simple way to increasethe readability and understandability of data presented in a PROCREPORT table. Use of SAS defined formats allow thepresentation of understandable standard numbers. CommonSAS supplied defined formats used in tables are the COMMA andDOLLAR formats. European versions for these also 920099200992009920099200

SUGI 28Data PresentationTruncation can also be done with a user defined format based ona picture statement. A format to truncate numbers to thousandsis given below;PROC FORMAT with Picture STATEMENT – This is the mostlogical approach; but, it is suffers from the problem that you needto substitute the capital letter “O” for the number “0" to displaytrailing “zeros”. This is needed because of a limitation of PROCFORMAT. To use this you need to know the maximum value youwill encounter. A user defined format to do this is given below.proc format;picture trunc0 '0'1-999 'Z'1000-999999 '009'(mult .001)1000000-999999999 '000,009'(mult .001)1000000000-999999999999 '000,000,009' (mult .001);run;PROC FORMAT ;PICTURE DIGIT0 91-9 '09'10-99 '99'100-999 '99O'1000 - 9999 ‘9,9OO’10000 - 99999 '09,OOO'100000 - 999999 '09O,OOO'1000000 - 9999999 '0,9OO,OOO'10000000 - 99999999 '09,OOO,OOO'RUN;Effect of Using the “trunc12.”FormatOriginalNumberTruncated tothousands usingtrunc12. 12345.678912123456.7891231234567.891,234(MULT .1)(MULT .01)(MULT .001)(MULT .0001)(MULT .00001)(MULT .000001);;PICTURE FORMAT Using Value Statement – This approach isbased on creating a format using an SAS data with the “cntlin”option. The trick is to write data step code to generate a SASdata set that will be read in the “cntlin” option. The SAS data stepcode is given below. A portion of the SAS data set produce bythat code is given below. It creates a SAS format 110000012000001234.5780.0012345678One Digit Significant10000.001000.0000.001Two Digits Significant12000.001200.0000.0012Three Digits ebrutebrutebrutebrutedata fmt brute;do i 0 to 9 ;j 0 ;start i ;end I ;y i ;x put ( y, comma10.);label x ;fmtname 'brute';output;end;Examples of One, Two, and Three Significant ,100,0001,200,000This SAS data set can be create use the following code.This filecan be used to convert any number from zero to 1013 to a numberwith two significant digits. A new file would be needed if youwanted to expressed number to three significant digits or forbigger numbers.SIGNIFICANT DIGITSSome reports are for the record and data must be expressedprecisely. The purpose of other reports is to show relationshipsand patterns in the data. To do this, some table makers arguethat data should be shown with only two significant digits.Displaying data with a lot of digits they argue suggests spuriousaccuracy and makes meaningful comparisons difficult. Theexample below shows a table with the original data displayed inone row and the data displayed to one, two and three significantdigits in the other rows.Original Numberend979999989999999999109999911999991299999do jdo ant digits refer to the digits of a number from left to rightstarting with the first nonzero digit. Some examples are givenbelow. Numbers can be expressed in a PROC REPORT tablebased on significant digits three different ways: user definedformat using a value statement, user defined format using picturestatement, and a compute statement. A PROC REPORT tableshowing the three different approaches is given below. 1 to 12; 10 to 99; i*10**(j-1) ; i*10**(j-1) (10**(j -1)-1 ); i*(10**(j-1) ); put ( y, comma10.); x; 'brute';proc formatrun;cntlin fmt brute;COMPUTE STATEMENT – The PROC REPORT code belowactually creates the table. It demonstrates calculating thesignificant digits in a compute statement. One advantage of thecompute statement is that you can express basic rows to twosignificant digits and the grand total to three significant digits.When you use formats, you are locked in to the same level ofprecision for a variable through out the table.FORMATSGROUP ORIGINAL PICTUREBRUTE FORCE ƒƒ1 1,757,239 70,0003186,94518O,OOO180,000180,0004 1,921,500 90,000ƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒ13,502,022 13,OOO,OOO 13,000,000 13,500,000DATA example;DO I 1 TO 14;X ( UNIFORM(0)Y int( X );z int( X );w int(x);OUTPUT;3) *10**6;

SUGI 28Data PresentationOUTPUT;END;RUN;options ls 64;proc report data example nowd HEADLINEHEADSKIP ;COLUMN I x (" FORMATS " y z)wW ;define i/ group "GROUP";DEFINE X/ ANALYSIS"ORIGINAL"FORMAT COMMA10.;DEFINE Y/ ANALYSIS"PICTURE"FORMAT DIGIT10.;DEFINE Z/ ANALYSIS"BRUTE FORCE"FORMAT BRUTE10.;DEFINE WW/ COMPUTED"COMPUTED"FORMAT COMMA10.;RBREAKAFTER / OL SUMMARIZE ;proc report data example nowd;column row (" Distance " distance xx );define row / display;define distance /analysis format comma12."(feet)";define xx /computed "(Recoded)";compute xx /char length 16 ;if distance.sum 0 then xx 0;if distance.sum 0 and distance.sum 3then xx (right(put(int((distance.sum/1)*10) /10, comma6.1) ' feet';if distance.sum 3 and distance.sum 16.5then xx (right(put( int((distance.sum/1 )*10) /10, comma6.1) ' yards'));if distance.sum 16.5 and distance.sum 66then xx (right(put( int((distance.sum/16.5)*10) /10, comma6.1) ' rods'));if distance.sum 66 and distance.sum 5280then xx (right(put(int((distance.sum/66)*10)/10, comma6.1) ' chains'));if distance.sum 5280thenxx ) ' miles');endcomp;rbreak after /ol;run;COMPUTE WW ;DIGITS 2;BREAKNE " RBREAK " THEN DO;DIGITS 2;IF LOG10(X.SUM) 0 THENWW INT ( X.SUM /(10**(INT(LOG10(X.SUM))-(DIGITS-1))))*10**( INT(LOG10(X.SUM))-(DIGITS-1)));IF X.SUM 10 THEN WW X.SUM;END;IFIFBREAKEQ " RBREAK " THEN DO;WW X.SUM;DIGITS 3;IF X.SUM 10 THEN WW X.SUM;IF LOG10(X.SUM) 0 THENWW INT (X.SUM /(10**( INT(LOG10(X.SUM))-(DIGITS-1))))*(10**( INT(LOG10(X.SUM))-(DIGITS-1)));The table produced by this SAS code is given below. The cellsare expressed with different units of measurement.END;ENDCOMP;RUN;row123456789This approach takes the logarithm of the number to determinehow many digits the number has. The number of significant digitsis subtracted from the number of digits to determine how manyzeros are needed. The original number is divided by 10 raised tothe number of zeros wanted. The integer part of the number isthe number of significant digits.CELL WITH NUMBERS AND CHARACTERSTwo common situations where cells have both numbers andsymbols are when the figures displayed have units ofmeasurement ( 220 Acres) or a conditional flag ( 267 r ) that alertsthe reader to some special conditions.Units of MeasurementWhen all cells in a column have the same unit of measurement(e.g. Millions) or the same flag (e.g. r - Revised Data), theinformation is made part of the column or row heading explicitlyand referenced using a footnote symbol. Often, a column hascells that contain different units of measurement. An example istable where the rows represent foreign countries and the columnis sales expressed in different currencies or the column is diskspace expressed in bytes, megabytes(MB), gigabytes(GB), orterabytes(TB).More complex compute statements can produce cells withcombinations of units of measure such as 7 yards 2.1 feet.Warning FlagA common use of symbols in statistical tables is to alert readersto special situations or to warn users of the reliability problems ina cell. Some organizations require a warning flag when thesampling error or imputation rate is high. The cell symbol isconditional on the value of one or more other cells in the row. Forimpuation rate it could be the value of the number of records inthe cell and the number of those records imputed. Withconditional cell flags you almost always need to use a COMPUTEstatement. An example of this type of PROC REPORT table isgiven below. In the table below, a flag is displayed if theimputation rate was greater than 50 percent.Some units of measurement can be added to a cell with a pictureformat. This is particularly true when there is a metric unit ofmeasure. Other types of units of measurement require the use ofthe PROC REPORT COMPUTE statement.The user defined format below illustrate how to display a cell withboth numbers and symbols. In the example below, disk space inbytes is displayed depending on the value of the cell in eitherbytes, kilobytes, megabytes, or gigabytes.PROC FORMAT ;PICTURE BYTESLOW - 0 '.'0- 11- 9991000- 9999991000000- 9999999991000000000 - 999999999999RUN;proc report data sugi28.virginia pop nowd;column county pct imputed population pop ;definecounty/ group;definepopulation / analysisformat comma15. "Population";definepct imputed/ format percent9.3;definePop/ computed “Population”;compute pop /char length 23;if percent imputed.sum .5 thenpop (right(put(population.sum,comma15.)) ' !!' ) ;elsepop (right(put(population.sum,comma15.)) '' ) ;endcomp;rbreak after / summarize ol ;run; '0' '009 ' '009 KB' (MULT .001) '009 MB' (MULT .000001) '009 GB'Distance(feet)(Recoded).50.5 feet11.0 feet66.0 yards1212.0 yards623.7 rods1231.8 chains6179.3 chains6,1731.1 miles12,3452.3 milesƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒƒƒƒƒ(MULT .000000001);The compute statement is needed to display cells where you needto convert distance in feet to feet, yards, rods, chains, and miles.4

SUGI 28Data PresentationThis code produces the following table.format below replaces all missing values with the symbol (M) andmost other cells with data having a “dollar12.” format. A simplisticbut often effective solution to missing values is to redefine themissing value symbol using the options statement. The defaultmissing value symbol of a number is “,”. The statement belowredefines the missing value symbol to an “M” and any table cellwith a missing value displays the value “M”.COUNTY12345Population9,1605,7621,9021,484 !!7,601ƒƒƒƒƒƒƒƒƒƒƒƒƒ25,909Percent Imputed greater than 50 percent!!options missing 'M';run;The missing value symbol can also be incorporate by creating asimple user defined format. The code below inserts an “(M)”when the value is missing.True Zero versus Round ZeroAn important distinction to many users is whether a cell ismissing, reported zero, or rounded to zero. A cell note iscommonly used to make the distinction. The cell note “(t)” is usedto represent values rounded to zero.Warning flags displayed either before or after the number areused to alert the table reader to reliability problems with respect tocomplete or item nonresponse error, coverage error, or elevatedsampling error. These conditional cell notes are similar toconditional notes used when a number is suppressed and only acell note displayed.Generic FormatA generic format that unconditionally creates a cell note formissing values, true zero, values rounded to zero, and all othervalues is given below.CELLS WITH ONLY CHARACTERSSymbols are displayed in cells of statistical table to explain thevalues of the cell. When the same explanation can be given forall cells in the table, or all cells in a row or column of cells, theexplanation can be put in the headhote, or the column or rowcaption. Often however, the symbols are specific to a given cell.When it is, a symbol is used to interpret the cell for the user. Thesymbol placed in the cell is explained in a specific footnote.These specific cell notes can be either conditional orunconditional cell notes. Unconditional means that the symboldisplayed depends only on the value of cell. Missing values androunding to zero are examples of this. Examples of unconditionalsymbols are symbols based on the value of the cell such as amissing value, an extremely small value, and units ofmeasurement based on the cell value. When the value of the cellis determined by the value of other associated cells is called aconditional cell note. The dividing line may get a little murky.Examples of unconditional cell notes are notes related to cellquality or reliability, whether they are meaningful, and otherqualities.PROC FORMATVALUE.0 0 - .5.5 - highRun;Conditional Cell NotesConditional cell notes depend on the values of other cells on thesame row. The table below uses conditional cell notes to identifycell with firms considered reliable and a ratio that is notmeaningful because the denominator is zero.RevenueExpenseCounty �ƒƒƒƒƒƒ12727,534,999 19,982,298 1.38212(S)(S)(S)33233,464,667(Z)(NM)43332,705,477 17,814,3551.8454333,248,724 15,279,8462.18Commonly Used Cell NotesCellNoteDescriptionsNMNot MeaningfulBBase less thanDData withheld to avoid DisclosureNSNot SignificantXNot ApplicableSData does not meet publication standards-Zero or rounds to zeroNANot AvailableRRevisedMMissingtLess than one-half unit of measurement(trace)REVENUE ‘(M)’ ‘0' ‘(t)’ [dollar12.];ƒƒƒƒƒƒƒƒƒƒƒƒ ƒƒƒƒƒƒƒƒƒƒ(Z) -- Zero(S) -- Suppressed because firms less than 15(NM)-- Not MeaningfulThe PROC REPORT code that producers the table is givenbelow.,PROC REPORT DATA EXAMPLE NOWDSPLIT "*" HEADSKIP HEADLINE ;COLUMN CTY FIRMS REVENUS EXPENSES R E RATIO ;DEFINE CTY/ group"County" ;DEFINE Firms/ ANALYSIS "Firms" ;DEFINE Revenue / ANALYSIS "Revenue"NOPRINT;DEFINE Expenses / ANALYSIS "Expenses"NOPRINT;DEFINE R/ COMPUTED "Revenue";DEFINE E/ COMPUTED "Expenses" ;DEFINE ratio/ COMPUTED "Revenue*Expense*RATIO";COMPUTE R/ CHAR LENGTH 13;R LEFT( PUT(REVENUE.SUM , COMMA13.0));IF FIRMS.SUM 15 THEN R '(S)';ENDCOMP;COMPUTE E / CHAR LENGTH 13 ;E LEFT( PUT(EXPENSES.SUM , COMMA13.0));IFEXPENSES.SUM 0 THEN E "(Z)";ELSE IF FIRMS.SUM 15 THEN E '(S)';ENDCOMP;A variety of symbols commonly appear in tables. Statisticalgroups that use many of these symbols in their tables include USDepartment of Agriculture, Census Bureau, Bureau of EconomicAnalysis, National Institute of Transportation Statistics, and theEnvironmental Protection Association.COMPUTE ratio /CHAR LENGTH 23 ;RATIO LEFT(PUT((REVENUE.SUM/EXPENSES.SUM),COMMA7.2));IF EXPENSES.SUM 0 THEN RATIO "(NM)" ;IF FIRMS.SUM 15 THEN RATIO '(S)';ENDCOMP;COMPUTE AFTER ;LINE '';LINE '(Z)--Zero';LINE '(S)--Suppressed because Firms less than 15';LINE '(NM)-- Not Meaningful';Missing ValuesA common situation is for cells to have missing values. Thegeneral solution is often to use a simple user defined format. The5

SUGI 28Data PresentationLINE 'ENDCOMP;RBREAK AFTER / OL ;RUN;';CONCLUSIONFormats and the PROC REPORT compute statements areexcellent tools for add the necessary detail to make tables morereadable, more usable, and more understandable.Conditional cell notes allows great flexibility in putting cell notes intables.REFERENCESGuide to Tabular Presentation, Example of Table Structure,Statistical Abstract of the United State 2000, US Census Bureau,Page ix-xFOOTNOTESA statement or note inserted at the bottom (foot) of a table is afootnote or source note. Specific footnotes define symbols (S) ornumbers (2) appearing usually in one or more cells. For tablesthat are entirely on one page, specific footnotes go at the bottomof the table. For tables that span multiple pages, a commonpractice is to put a standard message such as “See footnotes atthe end of the table.” at the bottom of each page except the lastand a complete listing of all footnotes at the bottom of the table onthe last page. An example table is given below.Ehrenberg, A.S.C. (1977), Rudiments of Numeracy, J. R.Statistical Society A, 140, Part 3, pp 277-297Enrenberg, A.S.C. Reading a Table: An Example, AppliedStatistician (1986) V 35, No 3, pp 237 - 244Ryder, K. (1986) Numerical Tables: Guidelines For Presentation,Journal of Applied Statistics ,Vol 13, No. 1, 1986, Page 77-78deptfirms See Notes at end of table------------- page break ------------deptfirms 22269294512206028792300314812861027662(Z) Zero(S) SuppressedEIA Publishing Style Guide, Chapter 5, Tables and Graphs,http:/www.eia.doe.gov/neic/pubstyle/ch5 1.htm, 8/12/2002SAS Institute Inc (1999) PROC FORMAT, Chapter 19, SASProcedures Guide Version 8, Cary NCSAS Institute Inc (1999) PROC REPORT, Chapter 32, SASProcedures Guide Version 8, Cary NC2001 Statistical Abstract of the United b/stat-ab01.html(12/12/02)2001 Statistical Abstract of the United States, Guide to TabularPresentation, Example of Table atab/tabular.pdf(12/12/02)DISCLAIMERThe code below shows the use of the COMPUTE and LINEstatement to add a specific footnote to a PROC REPORT table.The key is the use of the “after” and “ page options to theCOMPUTE statement. The “after” option executes at the end ofthe table; The “after page” executes at the end of each page ofthe table.This paper reports the results of research and analysisundertaken by Census Bureau staff. It has undergone a morelimited review by the Census Bureau than its official publications.This report is release to inform interested parties and toencourage discussion.ACKNOWLEDGMENTSproc report data example nowd;column deptfirms revenue expenses ;define dept/ group;define firms/ analysis sum ;define revenue / analysis sum format bdat7.;define expenses / analysis sum;rbreak after /summarize;compute before;hold ' ';endcomp;compute after ;LINE @2 "(Z) Zero ";LINE @2 "(S) Suppressed";LINE @2 "";Hold 'x';endcomp;compute after page / left ;line "See Notes at end of table ";endcomp;run;I would like to acknowledge the help of the NESUG 15 - 2002Code Clinic team (John Cohen, Elizabeth Axilrod, and Bob Virgil)in coming up with the idea of a one-to-one format to convertnumber to two significant digits.CONTACT INFORMATIONYour comments and questions are encouraged. Contact theauthor at:David D. Chapman, ChiefFrame Development StaffEconomic Statistical Methods and Programing DivisionU.S. Census BureauWashington, DC 20233-6400Work Phone: 301-457-4904Fax: 301-457-1382Email:david.d.chapman@census.gov6

Truncating and Rounding Rounding is usually expressed in terms such as “rounding to two decimal places” or “rounding to thousands” . Truncation refers to either dropping left most digits of a number or replacing the digits with zeros. In general formats can not be used for rounding; however, they can be used to round the decimal parts .

Related Documents:

SUGI SAS Users Group International –SUGI ONE 1976 –Kissimmee, FL –SUGI 29 –Montreal, QC –SUGI 30 –Philadelphia, PA –SUGI 31 –San Francisco, CA SAS Global Forum –2007 to present –2007 –First Canadian Chair 16

DND 04 formats in Ms. Word Engineering (ENG) 03 formats in Ms. Word Marketing (MKT) 05 formats in Ms. Word Operation (OPN) 15 formats in Ms. Word Services (SER) 03 formats in Ms. Word System (SYS) 17 formats in Ms. Word & Ms. Excel Training (TRG) 07 formats in Ms. Word 5. Standard Operating Procedures (SOPs) 06 SOPs in Ms. word 6. Process Flow .

HACCP Manual 05 chapter and 04 annexure in MS Word 2. Procedures 14 procedures in MS Word 3. HACCP docs. 18 files in MS Word 4. Formats 52 formats in MS Word Engineering (ENG) 06 formats in MS Word Purchase (PUR) 05 formats in MS Word Despatch (DES) 03 formats in MS Word Housekeeping (HKC) 09 formats in MS Word .

One player will act as game master, or GM. The other players (like you) are hero players. Each of you controls one player character (PC)—your character is Hida Sugi. Hero players make decisions for their PCs, describing what they want their characters to do and how they react to the plot

A: Sawmill can handle all major log formats and many minor formats, and you can create your own custom formats. For full details, see Supported Log Formats. Q: How is Sawmill different from other log analysis tools? A: Among other things, Sawmill does not generate static reports -- it generates dynamic, interlined reports. For full

Data repositories often consist of text files in a wide variety of standard formats, ad-hoc formats, as well as mixtures of formats where data in one format is embedded into a different format. It is therefore a significant challenge to parse these files into a structured tabular form, which is important to enable any downstream data processing.

LibreOffice File Formats Native document format is Open Document format (ODT, ODS, ODP, ODG - Text, Spreadsheet, Presentation, Graph) Supports other formats including MS Office Formats - Compatibility good but less than 100% Recent MS Office versions support LO File Formats - Compatibility good but less than 100% iPad/iPhone display LibreOffice ODT & ODP format

Mata kuliah mengenai bencana sudah ada diberbagai program studi pendidikan tenaga kesehatan. Akan tetapi, belum ada pendidikan resmi atau pelatihan bersertifikat untuk pengelolaan bencana. Buku-buku dan artikel-artikel penelitian mengenai bencana belum banyak diterbitkan. Berpijak pada pengalaman ini sudah selayaknya ilmu manajemen dipergunakan untuk penanganan bencana di sektor kesehatan .