Sas-Macro Variable

Question

I have a SAS code applied to a single alphanumeric column to remove the character ':' in the column, I would like to apply this code to all the alphanumeric columns of table1 automatically with macro variable

data p;
set  table1;
prenom1 = COMPRESS(VVALUE(prenom),':');
drop prenom;
rename prenom1=prenom;
run;

but the code doesn't work:

select name into :alpha_cols separated by ' ' 
from dictionary.columns
 where libname = 'work' and memname = 'table1' and type = 'char'; 
quit; 

data work.P; 
set table1; 
%do i = 1 %to %sysfunc(countw(&alpha_cols)); 
%let col_name = %scan(&alpha_cols, &i); 
&col_name._new = %sysfunc(compress(%sysfunc(vvalue(&col_name)), ':')) ; %sysfunc(rename) &col_name._new = &col_name.; 
%end; 
run;

Answer 1

Why to use macros at all?

Use array over character variables:

data have;
 set sashelp.class(obs=3);
 name_age = catx(":", name, age);
 name_sex = catx(":", name, sex);
run;
proc print data=have;
run;

data got;
  set  have;
  array C _character_;
  do over C;
    C = COMPRESS(C,':');
  end;
run;

proc print data=got;
run;

Answer 2

To make the list of variable names you need to query the metadata using only uppercase LIBNAME and MEMNAME values, since that is how the metadata appears in that view. Also I find it helps to always use NLITERAL() function when generating lists of variables just in case someone has accidentally set the VALIDVARNAME option to ANY.

proc sql noprint;
select nliteral(name)
     , nliteral(cats(name,'new'))
  into :alpha_cols separated by '|' 
     , :new_cols separated by '|'
  from dictionary.columns
  where libname = 'WORK'
    and memname = 'TABLE1'
    and type = 'char'
;
%let nvars=&sqlobs; 
quit;

Now it is easy to use a %DO loop to generate one assignment statement per variable in the list. If you want to re-use the name then you need to drop the old variable, you cannot have two variables with the same name. You don't need to add %SYSFUNC() calls around the SAS code you are using the macro logic to generate. That is only needed when you want the SAS functions to actually execute while the macro processor is working.

data P; 
  set table1; 
%do i = 1 %to &nvars;
  %let col_name = %scan(&alpha_cols,&i,|);
  %let new_name = %scan(&new_cols,&i,|); 
  &new_name = compress(vvalue(&col_name), ':'); 
  rename &new_name = &col_name ;
  drop &col_name ;
%end; 
run;

You cannot use a %DO Loop in "open code", only inside a macro. To use the %DO loop you must enclose this in a macro definition and then execute the macro.

So perhaps something like:

%macro convert(indata,outdata);
%local alpha_cols new_cols nvars i col_name new_name;

proc sql noprint;
select nliteral(name)
     , nliteral(cats(name,'new'))
  into :alpha_cols separated by '|' 
     , :new_cols separated by '|'
  from dictionary.columns
  where libname = 'WORK'
    and memname = %upcase("&indata")
    and type = 'char'
;
%let nvars=&sqlobs; 
quit;

data &outdata; 
  set &indata; 
%do i = 1 %to &nvars;
  %let col_name = %scan(&alpha_cols,&i,|);
  %let new_name = %scan(&new_cols,&i,|); 
  &new_name = compress(vvalue(&col_name)) ':'); 
  rename &new_name = &col_name ;
  drop &col_name ;
%end; 
run;
%mend convert;

Example call:

data table1;
  name='fred';
  reason='A:b';
run;
options mprint;
%convert(indata=table1,outdata=P);

Resulting SAS log:

817  %convert(indata=table1,outdata=want);
MPRINT(CONVERT):   proc sql noprint;
MPRINT(CONVERT):   select nliteral(name) , nliteral(cats(name,'new')) into :alpha_cols separated by '|' , :new_cols separated by
'|' from dictionary.columns where libname = 'WORK' and memname = "TABLE1" and type = 'char' ;
MPRINT(CONVERT):   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


MPRINT(CONVERT):   data want;
MPRINT(CONVERT):   set table1;
MPRINT(CONVERT):   namenew = compress(vvalue(name), ':');
MPRINT(CONVERT):   rename namenew = name ;
MPRINT(CONVERT):   drop name ;
MPRINT(CONVERT):   reasonnew = compress(vvalue(reason), ':');
MPRINT(CONVERT):   rename reasonnew = reason ;
MPRINT(CONVERT):   drop reason ;
MPRINT(CONVERT):   run;

NOTE: There were 1 observations read from the data set WORK.TABLE1.
NOTE: The data set WORK.WANT has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds