/****************************************************************/ /*          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     101213   Joe       2  Virginia Beach  salesrep    201214   Jeff      1  Virginia Beach  salesrep    201215   Wanda    10  Ocean City      salesrep    201216   Fred      6  Ocean City      salesrep    201301   Sally     9  Wilmington      manager     101314   Marvin    5  Wilmington      salesrep    301318   Nick      1  Myrtle Beach    salesrep    301401   Chuck    12  Charleston      manager     101417   Sam       7  Charleston      salesrep    401;run;  /*----------------------------------------------------------*/  /*-- and use the macro.                                   --*/  /*----------------------------------------------------------*/proc sql;  select %comsep(empname empnum empboss)    from employee;  quit;