sqlfun08.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLFUN08                                            */
 /*   TITLE: fun/interesting applications of PROC SQL. (fun08)   */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN SELECT EXCEPT DISTINCT                   */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: pmk                         UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC: this example was contributed by Howard Schreier     */
 /*          of the US Dept. of Commerce, via BITNET             */
 /*                                                              */
 /*          it demonstrates a case where the cartesian          */
 /*          product formed by an SQL join is useful -- it is    */
 /*          used to form the set of all state/county/category   */
 /*          and this set is then processed with the except      */
 /*          operator to find the missing ones.                  */
 /*                                                              */
 /*                                                              */
 /*          you can contribute your interesting samples.        */
 /*          send internet email to [e-mail] or          */
 /*          USmail to SAS Institute.                            */
 /*                                                              */
 /****************************************************************/

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

 /*
  *  A User asks: I want to compare a list
  *  of categories to a dataset containing, among other
  *  variables, STATE COUNTY CATEGORY. So the list of categories
  *  has been placed in a dataset sorted by category and used to
  *  come up with a list of categories missing *from the entire
  *  dataset*.  BUT the desired output is a list of expected
  *  categories missing by state and county, viz.:
  *
  *             EXPECTED CATEGORIES MISSING FROM DATASET
  *
  *                STATE   COUNTY    CATEGORY
  *                  1       1          12
  *                  1       1          15
  *                  1       2          27
  *                  .
  *                  .
  *                  .
  *                  etc.
  *
  *  Any idea how I can help this come to pass?   It has been
  *  suggested that a dataset containing all desired
  *  state-county-category combinations be merged on to the
  *  original, but I have been unable to advise how to do that,
  *  either.  All suggestions are welcome; Thanks!
  *
  */


 /*
  * I think you are on the right track.  You don't indicate your
  * SAS  version  and platform, so I'm not sure if you have PROC
  * SQL  available.   If  you  do,  a  solution   is   not   too
  * complicated:
  *
  */

 data s_c_c;
   input state county category other $ @@;
   cards;
   1 1 27 A    1 2 12 B    1 2 15 C
   ;
 data cats;
   input category @@;
   cards;
   12 15 27
   ;

 /*
  * The  expression  within  parentheses  generates  a  list  of
  * STATE-COUNTY  combinations.   Then,  the  first SELECT joins
  * (crosses) this with the category list to create the list  of
  * STATE-COUNTY-CATEGORY   combinations   you   identify  as  a
  * necessary intermediate step.  Finally, the  EXCEPT  operator
  * produces the list of omissions you are after.  The result:
  *
  */

 proc sql;
   title2 'EXPECTED CATEGORIES MISSING FROM DATASET';
   select *
     from (select distinct state, county from s_c_c), cats
   except
   select state, county, category
     from s_c_c;
 quit;