How To Use Proc SQL select into for List Processing

From sasCommunity
Jump to: navigation, search

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:

http://www.sascommunity.org/wiki/How_To_Use_Proc_SQL_select_into_for_List_Processing

How To Use Proc SQL select into for List Processing

Author: Ronald_J._Fehd

Abstract

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

Successor: Macro_CallMacr

Deprecated

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) !! ')'
...

References

-- created by User:Rjf2 10:29, 30 August 2007 (EDT)

--Ronald_J._Fehd macro.maven == the radical programmer