From: J. Clarke
To: Richard DeVenezia
Sent: Tuesday, March 06, 2001 3:31 PM
Subject: Regarding the website

First of all, let me thank you for posting theses macros. While I have not
as yet had a use for them, following their coding from beginning to end has
immeasurably helped me understand a lot about SAS. On the "seplist" macro, I
have two questions. First of all, is the "itemlist" the string that you are
looking at? If so, if it is delimited by commas, does this still work?
Secondly, I don't understand the "oracle". Could you give an example of
%seplist function, so I could run it and better understand how it operates?
I realize that you probably have a busy schedule, and I hope that I am not
too intrusive with my request.

John Clarke


Note: The oracle= stuff is no longer valid and has been superceded by the more generic prefix=, nest= and suffix= parameters.


I'm glad you find utility in the macros.  I don't use them everyday, but
they're part of my toolbox for larger projects.
You are lucky you caught me on this Nor'easter snow day.

%macro seplist (itemlist, oldelim=%str( ), delim=%str(,), oracle=);

oldelim=%str( )
This means a named argument, oldelim=, can be used when invoking the macro, the default
will be a blank if the named argument is not present.

The same goes for delim, the default will be a comma if the named argument
is not present.

----- START: This block is submittable -----

 * Here is an example using defaults
 * A,B,C,D,E should show in the log window;

%let list=A B C D E;
%put %seplist (&list);

 * Here is an example using non-default delimiters
 * A+B+C+D+E should show in the log window;

%let list=A/B/C/D/E;
%put %seplist (&list, oldelim=/, delim=+);

 * Commas in the list being passed is a little more tricky because commas
 * separate macro arguments.  The argument has to be wrapped in a macro %str
 * function or a %quote function to pass everything as 'plain old text' as
 * opposed to letting the SAS system try to interpret the comma delimits as
 * part of the macro call.
 * %quote() is necessary when passing a resolved macro variable that contains commas
 * %str() is sufficient when passing text that contains commas

%let list=A,B,C,D,E,;
%put %seplist (%quote(&list), oldelim=%str(,), delim=%str(,));
%put %seplist (%str(A,B,C,D,E,), oldelim=%str(,), delim=%str(,));

/* The defaults are handy if you have a macro that performs groupwise
 * processing with both SAS Data steps and Proc step and Proc SQL.

%macro Example (LEVELS=3, DS=EXAMPLE);

  %local i;
  data &DS;
    %do i = 1 %to &LEVELS; do LEVEL&i = 1 to 5; %end;
    do k = 1 to 5;
    X = ranuni (0);
    %do i = 1 %to &LEVELS; end; %end;

  %local byVars;
  %let byVars = LEVEL1 LEVEL2 LEVEL3;

  proc means noprint data=&DS;
    by &byVars;
    var x;
    output out=MEAN mean=mean;

  proc sql;
    create table means as
    select %seplist (&byvars), mean(x) as mean
    from &DS;    group by %seplist (&byvars)

%Example ()

----- END: This block is submittable -----

The SQL aspect is where the oracle= argument comes into play.  If you deal
with selects in Oracle via SQL pass-through, in some cases the Oracle column
names conflict with Oracle functions (I ran across a table with a column
named MODE, Oracle barked when I did a 'select MODE from table' in pass-through
sql)  Oracle will honor the column name explicitly if the column name is
delimited within double quotes.

So suppose you have an Oracle table named FOOBAR
with columns A, B, C, D, E, F, G
and in SAS sql pass through you want to do something like

proc sql;
  connect to oracle (...);

  create table SasSide as
  select *
  from connection to oracle
%* oracle side ;
    select FOOBAR."A", FOOBAR."C", FOOBAR."E" from (complicated subjoin)
FOOBAR where <OracleClause>

  disconnect from oracle;

(Note: Pass-through SQL Oracle access is much less necessary in SAS version 8+
if you use the new Oracle libname options.)

Any how, the seplist macro can create
with this invocation
%seplist (A B C, oracle=FOOBAR);

Thinking about SepList a little right now, I figure it could also be
improved by changing the Oracle= argument to a prefix= argument and adding a
suffix= argument (which would the earlier example be totally

Hope that explains it.  I'll probably link this explanation into the website
some time later.

Richard DeVenezia