/****************************************************************/ /*          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 KENT@UNX.SAS.COM 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;