sqlfun04.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLFUN04                                            */
 /*   TITLE: fun/interesting applications of PROC SQL. (fun04)   */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN SIGN ABS LOG MOD EXP SUM PRINT           */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: pmk                         UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC: this example was contributed by Jack Hamilton       */
 /*          of Amdahl Corporation, via BITNET                   */
 /*                                                              */
 /*          a user asked "can you compute the product of        */
 /*          a column of numbers" on the SAS-L bitnet list.      */
 /*                                                              */
 /*          you can contribute your interesting samples.        */
 /*          send internet email to [e-mail] or          */
 /*          USmail to SAS Institute.                            */
 /*                                                              */
 /****************************************************************/

 title1 'SAS SAMPLE LIBRARY, MEMBER(SQLFUN04)';

 * Create a sample data set. ;
 * Notice that 3 of the numbers are negative. ;

data numbers;
infile cards;
input number @@;
cards;
2 3 -4 2.1 -2.2 6 -34 0
;;;;

proc sql;



 * Create a view with 3 additional variables:                   ;
 *   NEGATIVE  is 1 if NUMBER is negative, otherwise it's 0.    ;
 *   ZERO      is 1 if NUMBER is zero, otherwise it's 0.        ;
 *   LOG       is the log of the absolute value of NUMBER.      ;
 *                                                              ;
 * There will be one observation in WITHLOG for each one in     ;
 * NUMBERS.                                                     ;
 create view withlog as
  select numbers.number as number,
         (sign(number) = -1) as negative,
         (number=0) as zero,
         log(abs(number)) as log
  from numbers;



 * Create a new view PRODUCT from the WITHLOG view.  Because    ;
 * summary functions (SUM, in this case) are used, but no GROUP ;
 * BY is used, there will be only one output observation.       ;
 *                                                              ;
 * Several new variables are created to help show you what's    ;
 * happening, but only one (RESULT or RESULTNZ, depending on    ;
 * how wanted zeros treated) is needed.  You could add more     ;
 * code to handle missing values, and you could combine both    ;
 * of these views into one single (confusing) view.             ;
 *                                                              ;
 * LOGSUM is the sum of the logs in WITHLOG.                    ;
 * LOGPROD is the antilog of LOGSUM.  Also, I can't just reuse  ;
 *         LOGSUM - I have to recalculate it.                   ;
 * LOGPRODZ is LOGPROD, but set to zero if any input was zero.  ;
 * POS and NEG count the number of NEGATIVE values in WITHLOG   ;
 *         and take that value modulus 2.  If an odd number of  ;
 *         negatives occurred, the number is negative.          ;
 * RESULTNZ returns the product discounting any zeros.  It      ;
 *         takes the product and multiplies it -1 if the        ;
 *         result should be negative.  There's probably another ;
 *         way to do this (with a subquery, perhaps)            ;
 * RESULT is like RESULTNZ, but might return a zero.            ;


 create view product as
  select sum(log) as logsum,
         exp(sum(log)) as logprod,
         exp(sum(log))*(sum(zero)=0) as logprodz,
         mod(sum(negative),2)=0 as pos,
         mod(sum(negative),2)=1 as neg,
         exp(sum(log)) *
            ((mod(sum(negative),2)=0) +
             ((mod(sum(negative),2)=1)*-1)) as resultnz,
         exp(sum(log))*(sum(zero)=0) *
            ((mod(sum(negative),2)=0) +
             ((mod(sum(negative),2)=1)*-1)) as result
  from withlog;

 quit;




proc print data=numbers;
  title2 'NUMBERS dataset';
  run;


proc print data=withlog;
  title2 'WITHLOG dataset';
  run;


proc print data=product;
  title2 'PRODUCT dataset';
  run;