sqlfun07.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLFUN07                                            */
 /*   TITLE: fun/interesting applications of PROC SQL. (fun07)   */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN SELECT WHERE ORDER BY CREATE TABLE       */
 /*   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.            */
 /*                                                              */
 /*          you can contribute your interesting samples.        */
 /*          send internet email to [e-mail] or          */
 /*          USmail to SAS Institute.                            */
 /*                                                              */
 /****************************************************************/

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

 /*
  * A User asked:
  *
  *  I have the following data set:
  *
  *          VAR             POSANS
  *          0001            010203041213
  *          0006            04012425
  *          0003            030608
  *
  *
  *  POSANS is a list of the possible responses to VAR. Each
  *  response has a length of 2.  TOTRESP is the total number
  *  of possible responses. What I need to do is to create another
  *  data set of all the possible combinations of responses to
  *  these three variables.
  *
  */


 /*
  * Here is one way to  tackle  the  problem.   The  first  step
  * breaks  out the substrings, as in your code, and creates one
  * observation for each (6+4+3=13 in total):
  *
  */

   data forsql;
     length posans $20.;
     input var $ posans $;
     keep var ans;
     do i = 1 to length(posans)-1 by 2;
        ans = substr(posans,i,2);
        output;
        end;
     cards;
0001  010203041213
0006  04012425
0003  030608
;



 /*
  * Now, use SQL to form the combinations:
  *
  * The three SELECT clauses in  parentheses  set  up  temporary
  * tables,  one  for  each  VAR value; these are then joined to
  * form the 6x4x3=72 combinations.
  *
  */

   proc sql;
   create table matrix as select * from
    (select ans as ans0001 from forsql where var='0001'),
    (select ans as ans0006 from forsql where var='0006'),
    (select ans as ans0003 from forsql where var='0003')
    order by ans0001, ans0006, ans0003
    ;


 /*
  * display the result
  *
  */

   title2 'All possible combinations';
   select * from matrix;
   quit;