Need catx macro function - catx is limited to 200 characters in proc sql

Question

It can be kind a dummy question but I can not find example. The case is below:

proc sql;
   create table set1 as select catx('<', field1, field2 ....) as need_field
   from table;
quit;

With this code field need_field cuts up tо length 200, so its predictible as documentation says:

The CATX function returns a value to a variable, or returns a value in a temporary buffer. The value that is returned from the CATX function has the following length:

•up to 200 characters in WHERE clauses and in PROC SQL

•up to 32767 characters in the DATA step except in WHERE clauses

•up to 65534 characters when CATX is called from the macro processor

I do not want do use data step. Could you help me to built code using macro processor? (third way). Thank you!

Answer 1

It seems that catx can return more than 200 characters if length= is specified but all data must fit in column. Otherwise you will get empty value.

Proof:

data test;
   length a b c $400;
   a = repeat('A',300);
   b = repeat('B',300);
   c = repeat('C',300);
   output;
   a = repeat('A',350);
   b = repeat('B',350);
   c = repeat('C',350);
   output;
run;

proc sql;
   create table want as
   select catx('<', a, b, c) as temp_list length=1000
   from test;
quit;

data _null_;
   set want;
   a = length(temp_list);
   put a=;
run;

In documentation there is also this:

If CATX returns a value in a temporary buffer, the length of the buffer depends on the calling environment, and the value in the buffer can be truncated after CATX finishes processing. In this case, SAS does not write a message about the truncation to the log. If the length of the variable or the buffer is not large enough to contain the result of the concatenation, SAS does the following:

• changes the result to a blank value in the DATA step, and in PROC SQL

• writes a warning message to the log stating that the result was either truncated or set to a blank value, depending on the calling environment

• writes a note to the log that shows the location of the function call and lists the argument that caused the truncation
• sets _ERROR_ to 1 in the DATA step

Answer 2

You can create your own macro function %CATX. Using macros you can generate a code, in this case using %catx(<, name, sex, name) I generated strip(name)||"<"||strip(sex)||"<"||strip(name) which is very similar thing what is done by regular catx function. Macro with parmbuff option takes all aruments and put them in one macro variable which is call syspbuff.

%macro catx / parmbuff;
    %let comma = %eval(%index(&syspbuff., %str(,))+1);
    %let separator=%scan(&syspbuff., 1, %str(%(%),));
    %let syspbuff=%substr(&syspbuff., &comma., %eval(%length(&syspbuff.)-&comma.));
    %let result = strip(%sysfunc(tranwrd(%bquote(&syspbuff.), %str(,), %str(%)||"&separator."||strip%())));
    &result. 
%mend catx;

proc sql;
    create table a as
    select %catx(<, name, sex, name) as var
    from class;
quit;

Answer 3

there is a handy trick where you drop your cats column and call it further down as a calculated column.

See this example using sashelp.class

 proc sql;
  create table want(drop=temp_list) as
  select catx('<', repeat(name,200),repeat(name,200)) as temp_list length=1024,
      put((calculated temp_list),$400.) as need_field length=400
  from sashelp.class
  ;
quit;

Example 2:

data a; 
length a $1000; 
a = repeat("A", 1000);
run;

proc sql; 
create table b(drop=temp) as select catx('<', a, a) as temp length=2024,
   (calculated temp) as b length=5000 from a; 
quit;

hope it helps