dBASE Statistical Functions (PC Magazine Vol 5 No 1 January 14, 1986 Power User) The program below calculates descriptive statistics from data stored in a numeric field in a dBASE III file. The formula separately sums the top and bottom one-sixth of a given range of figures, such as scores. The sum of the lowest scores is subtracted from the sum of the highest scores. The resulting value, divided by one-half of the total number of entries, is an estimate of the standard deviation. Editor's Note: One caution is in order. In the line "STORE str(INT(A/6),1) TO R the value and width of R can vary from 1 to 3 digits, depending on the result obtained by dividing A by 6. For greater accuracy in such cases, you should allow R to be larger by changing the 1 in the formula to 2 or 3. However, if you allow more than the exact number of digits for the variable R (by changing 1 to 2 or 3), the subsequent code "SKIP -&R" will not work properly. Try it as written first, and then change the width of R only if it appears that the answer for (A/6) requires more than a single digit for accuracy. SET HEADING OFF SET SAFETY OFF CLEAR SET TALK OFF ? 'DESCRIPTIVE STATISTICS PROGRAM' ? ACCEPT 'DATA FILE? ' TO FILE ACCEPT 'FIELD? ' TO FIELD USE &FILE ? ? 'STEP 1 OF 4: INDEXING RECORDS' INDEX ON &FIELD TO INDEXF USE &FILE INDEX INDEXF ? 'STEP 2 OF 4: COUNTING RECORDS' COUNT FOR &FIELD <> 0 TO A ? 'STEP 3 OF 4: ADDING VALUES' SUM &FIELD TO SUM STORE str(INT(A/6),1) TO R ? 'STEP 4 OF 4: FINDING HIGH/LOW VALUES, MEDIAN, MEAN' GO TOP STORE &FIELD TO LOW SUM &FIELD NEXT &R TO BSUM GO BOTTOM STORE &FIELD TO HIGH SKIP -&R SUM &FIELD NEXT &R TO TSUM STORE A/2 TO MM IF INT(MM)=MM GO TOP SKIP MM STORE &FIELD TO B SKIP -1 STORE &FIELD TO C STORE (B+C)/2 TO MEDIAN ELSE GO TOP SKIP INT(MM) STORE &FIELD TO MEDIAN ENDIF STORE HIGH-LOW TO RANGE STORE SUM/A TO MEAN STORE (TSUM-BSUM)/(A/2) TO SD STORE SD*SD TO VARIANCE @ 11,1 SAY 'RESULTS' @ 13,1 SAY "NO. OF ENTRIES' @ 13,19 SAY A @ 14,1 SAY 'HIGHEST VALUE' @ 14,19 SAY HIGH @ 15,1 SAY 'LOWEST VALUE' @ 15,19 SAY LOW @ 16,1 SAY 'RANGE' @ 16,19 SAY RANGE @ 17,1 SAY 'SUM' @ 17,19 SAY SUM @ 18,1 SAY 'MEDIAN' @ 18,19 SAY MEDIAN @ 19,1 SAY 'MEAN' @ 19,19 SAY MEAN @ 20,1 SAY 'STANDARD DEVIATION' @ 20,19 SAY SD @ 21,1 SAY 'VARIANCE' @ 21,19 SAY VARIANCE RELEASE FILE,FIELD,A,SUM,R,LOW,BSUM,HIGH,TSUM,MM,B,C RELEASE MEDIAN,MEAN,SD,VARIANCE,RANGE USE RETURN ----------------------------------------------------------------- Using Numeric Field in dBASE III Text (PC Magazine Vol 5 No 1 January 14, 1986 Power User) dBASE's PICTURE command works well for inputting numerically formatted data to be printed in tabular reports. However, it leaves blank spaces in the field when the number doesn't fill the field completely. For this reason, PICTURE doesn't work very well in applications where you need to drop the numbers into the middle of text. This difficulty can be overcome by converting the number to a trimmed character string. After doing so, however, you'll also need to add commas and retain decimal points before you put the converted numbers into form letters and other text. DOLLARS.PRG below can perform both the conversions and reformatting for you automatically. You can actually produce simple form letters entirely with dBASE III. By using the SET MARGIN TO command the TEXT/ENDTEXT statements, you can create a program that produces a convincing form letter without resorting to external word processing programs. Editor's Note: You can indeed use TEXT/ENDTEXT and SET MARGIN TO to create programs for form letters. However, any macros embedded within the text block defined by TEXT/ENDTEXT will not be expanded, since dBASE ignores everything within these bracketing statements. To insert the converted numbers created by DOLLARS.PRG in your dBASE form letters, then, you must use @ SAY statements instead, before or after the TEXT/ENDTEXT statements. * This program will convert AMOUNT (a numeric field or memory variable * with two decimal places) into the string variable MONEY for printing * in text material. Leading blanks are removed and commas are inserted. * This routine can not process numbers greater than 99,999,999.99. * Numbers less than .01 will be rounded to 2 places. * * Declare public memory variable MONEY so that results can be passed * back to the calling program PUBLIC MONEY * Convert numeric variable into string, insert commas and decimal point MONEY=SUBSTR(STR(AMOUNT*100),1,2)+","; +SUBSTR(STR(AMOUNT*100),3,3)+","; +SUBSTR(STR(AMOUNT*100),6,3)+"."; +SUBSTR(STR(AMOUNT*100),9) * Strip leading blanks and commas DO WHILE SUBSTR(MONEY,1,1)=" " .OR. SUBSTR(MONEY,1,1)="," MONEY=SUBSTR(MONEY,2) ENDDO * If money <.1 insert missing 0 IF SUBSTR(MONEY,1,2)=". " MONEY=SUBSTR(MONEY,1,1)+"0"+SUBSTR(MONEY,3) ENDIF ----------------------------------------------------------------- Square Roots in dBASE Revisited (PC Magazine Vol 5 No 3 Feb 11, 1986 Power User) A previous article (PC Mag Vol 4 No 23 Power User) outlined a dBASE program (SQRT.PRG) for finding square roots. The "exponentiation" operator can be used to find the square root (or any other root or power, including fractionals. For example: N = 3 ? N**1/2 (yields 1.73 as the square root) N = 3.00000 ? N**1/2 (Yields 1.73205 as the square root) Editor's Note: Part of the reason for showing SQRT.PRG earlier was that the program's origins were in Pascal. It thus served to illustrate the kind of cross-programming possible between dBASE and other programming languages. Use of the exponentiation operator is limited to dBASE III; dBASE II does not have these useful extra operators. Thus, for users of dBASE II, the only way to get square roots easily is through a program like SQRT.PRG. Finally, this example is wrong. To find a square root in dBASE III, you would normally use the program's SQRT (?) operator (? is the number whose root you're seeking). You can also use the ** operator followed by .5 to produce the same result. However, using the fraction 1/2 only gives you one-half the number stored in N, not its square root.