sqlfun06.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLFUN06                                            */
 /*   TITLE: fun/interesting applications of PROC SQL. (fun06)   */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN MACRO SELECT %EVAL %SCAN COMMA           */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: pmk                         UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC: this example shows a macro that supplies the        */
 /*          commas needed between the variables in an           */
 /*          SQL select statement.                               */
 /*                                                              */
 /****************************************************************/

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



  /*------------------------------------------------------------*/
  /*-- define a macro that supplies the commas                --*/
  /*------------------------------------------------------------*/
  %macro comsep(vars);

    %local i var;

    /*----------------------------------------------------------*/
    /*-- deal with the first word specially  "VAR"            --*/
    /*----------------------------------------------------------*/
    %scan(&vars, 1)


    /*----------------------------------------------------------*/
    /*-- deal with the rest all in the same fashion  ", VARN" --*/
    /*----------------------------------------------------------*/
    %let   i = 2;
    %let var = %scan(&vars, &i);

    %do %while ( &var ne );
       , &var
       %let i = %eval(&i+1);
       %let var = %scan(&vars, &i);
       %end;

    %mend;




  /*----------------------------------------------------------*/
  /*-- make a sample dataset.                               --*/
  /*----------------------------------------------------------*/
data employee;
   input empnum empname $ empyears empcity $ 20-34
   emptitle $ 36-45 empboss;
   cards;
101   Herb     28  Ocean City      president     .
201   Betty     8  Ocean City      manager     101
213   Joe       2  Virginia Beach  salesrep    201
214   Jeff      1  Virginia Beach  salesrep    201
215   Wanda    10  Ocean City      salesrep    201
216   Fred      6  Ocean City      salesrep    201
301   Sally     9  Wilmington      manager     101
314   Marvin    5  Wilmington      salesrep    301
318   Nick      1  Myrtle Beach    salesrep    301
401   Chuck    12  Charleston      manager     101
417   Sam       7  Charleston      salesrep    401
;
run;


  /*----------------------------------------------------------*/
  /*-- and use the macro.                                   --*/
  /*----------------------------------------------------------*/
proc sql;

  select %comsep(empname empnum empboss)
    from employee;

  quit;