How To Use Proc SQL select into for List Processing
RJF 2012-Jun-30 Using proc sql select catt('%MyMacro('...) into :Mvar is Deprecated.
Recommended Successor: Macro_CallMacr
http://tinyurl.com/36vu4rj for this page:
The SAS(R) macro language is simple, yet powerful. List Processing with Proc SQL is also simple, yet powerful. This Hands On Workshop paper provides programmers with knowledge to use the Proc SQL select into clause with the various SQL dictionaries to replace macro arrays and %do loops.
Audience: intermediate to advanced users, and macro programmers.
Keywords: dynamic programming, list processing, macro, SQL
This method is deprecated:
*too much text of called macro; PROC SQL; select catt('%MyMacro(data=',data,',var=',variable,')') into :List separated by ' ' from Libref.Data; quit;
because of either of the following errors:
- catt function max length is 200 in proc sql
- maximum length of macro variable is 2^16 -2
WARNING: In a call to the CATT function, the buffer allocated for the result was not long enough to contain the concatenation of all the arguments. The correct result would contain 217 characters, but the actual result may either be truncated to 200 character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most argument that caused truncation. NOTE: Invalid argument 11 to function CATT. Missing values may be generated. ERROR: The text expression length (65535) exceeds maximum length (65534). The text expression has been truncated to 65534 characters.
The following list of workarounds can be used for optimization.
- replace catt function with either double bang::exclamation points (!!)
or double vertical vars (||)
- from: catt('%MyMacro(',data,',var=',variable,')')
- to: '%MyMacro(' !! trim(data) !! ',var=' !! trim(variable) ')'
- reduce name of macro to single letter
- change named parameters, even though recommended, to positional parameters
- examine information passed, move to subroutine
- consider using macro arrays, or picking items from a delimited list
consider this example:
%Let LibName = SAShelp; %Let MemName = Class; *previous: Macro ProcVar(data=,name=); %Macro V(Name /* positional parameter */ /* global macro vars */ ,Data = &LibName..&Memname. ,Testing = 0); *todo: get information: type, format, etc.; ... PROC SQL &SQLprint.; select '%V(' !! trim(Name) !! ')' ...
- paper, 2007: SESUG
- paper, 2010: SESUG
- successor, paper: List_Processing_Basics_Creating_and_Using_Lists_of_Macro_Variables
- R. J. Fehd and Art Carpenter
- successor, macro: Macro_CallMacr
-- created by User:Rjf2 10:29, 30 August 2007 (EDT)