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. Thanks, John Clarke
Note: The oracle= stuff is no longer valid and has been superceded by the more generic prefix=, nest= and suffix= parameters.
John: 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. delim=%str(,) 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(@xyz.com,)); %put %seplist (%str(A,B,C,D,E,), oldelim=%str(,), delim=%str(@xyz.com,)); /* 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); output; end; %do i = 1 %to &LEVELS; end; %end; run; %local byVars; %let byVars = LEVEL1 LEVEL2 LEVEL3; proc means noprint data=&DS; by &byVars; var x; output out=MEAN mean=mean; run; proc sql; create table means as select %seplist (&byvars), mean(x) as mean from &DS; group by %seplist (&byvars) ; quit; %mend; %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; quit; (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 FOOBAR."A", FOOBAR."C", FOOBAR."E" 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 @xyz.com example be totally correct.) **/ Hope that explains it. I'll probably link this explanation into the website some time later. Richard DeVenezia