Skip block of codes if macro variable is empty

Question

I am creating a macro variable with the SAS code below. It's storing a list of data names where I need to replace certain values in specific variables.

proc sql noprint;
select distinct data_name
into :data_repl separated by ' '
from TP_attribute_matching
where Country="&Country_Name" and Replace_this ne ' ';
quit;

I would like to skip the following 2 blocks if data_repl is empty. These 2 blocks go through each data set and variables in that data set, and then replaces x with y.

/*Block 1*/
%do i=1 %to %_count_(word=&data_repl);
proc sql noprint;
select var_name,
   Replace_this,
   Replace_with
into :var_list_repl_&i. separated by ' ',
     :repl_this_list_&i. separated by '@',
     :repl_with_list_&i. separated by '@'
     from TP_attribute_matching
where Replace_this ne ' ' and data_name="%scan(&data_repl,&i.)";
quit;

/* Block 2 */
%do i=1 %to %_count_(word=&data_repl);
data sasdata.%scan(&data_repl,&i);
set sasdata.%scan(&data_repl,&i);
 %do j=1 %to %_count_(word=&&var_list_repl_&i.);
   %let from=%scan("&&repl_this_list_&i.",&j,'@');
   %let to=%scan("&&repl_with_list_&i.",&j,'@');
   %scan(&&var_list_repl_&i.,&j)=translate(%scan(&&var_list_repl_&i.,&j),&to,&from); 
 %end;
run;
%end;

How shoould I do this? I was going through %SKIP and if then leave, but cannot figure this out yet.

Answer 1

%IF and %DO are macro statements that can only be used inside a macro:

%macro DoSomething;
%if "&data_repl" ne "" %then %do;
    /*Block 1*/
    %do i=1 %to %_count_(word=&data_repl);
    proc sql noprint;
    select var_name,
       Replace_this,
       Replace_with
    into :var_list_repl_&i. separated by ' ',
         :repl_this_list_&i. separated by '@',
         :repl_with_list_&i. separated by '@'
         from TP_attribute_matching
    where Replace_this ne ' ' and data_name="%scan(&data_repl,&i.)";
    quit;

    /* Block 2 */
    %do i=1 %to %_count_(word=&data_repl);
    data sasdata.%scan(&data_repl,&i);
    set sasdata.%scan(&data_repl,&i);
     %do j=1 %to %_count_(word=&&var_list_repl_&i.);
       %let from=%scan("&&repl_this_list_&i.",&j,'@');
       %let to=%scan("&&repl_with_list_&i.",&j,'@');
       %scan(&&var_list_repl_&i.,&j)=translate(%scan(&&var_list_repl_&i.,&j),&to,&from); 
     %end;
    run;
    %end;

%end;
%mend;

%DoSomething

EDIT: Instead of checking the string, you can use count from PROC SQL (&SQLOBS macro var)

%let SQLOBS=0; /* reset SQLOBS */
%let data_repl=; /* initialize data_repl,
                    would not be defined in case when no rows returned */

proc sql noprint;
select distinct data_name
into :data_repl separated by ' '
from TP_attribute_matching
where Country="&Country_Name" and Replace_this ne ' '
and not missing(data_name);
quit;

%let my_count = &SQLOBS; /* keep the record count from last PROC SQL */
...

%if &my_count gt 0 %then %do;
...
...
%end;

If you already have a main macro, no need to define new (I'm not sure what you're asking now).

Answer 2

First off, this is yet another good example where list processing basics would simplify the code to where you don't need to worry about your actual question. Will elaborate later.

Second off, the way these loops are usually coded is something like

%do ... %while &macrovar ne ;

which checks for empty and doesn't execute the loop at all if it's empty to start with. &macrovar there would be the result of the scan. IE:

%let scan_result = %scan(&Data_repl.,1);
%do i = 1 %to %_count_... while &scan_result ne ; *perhaps minus one, not sure what %_count_() does exactly;
  ... code
  %let scan_result=%scan(&data_Repl.,&i+1);
%end;

Going back to list processing, what you're ultimately doing is:

data &dataset.;
 set &dataset.;
 [for some set of &variables,&tos, &froms]
 &variable. = translate(&variable.,&to.,&from.);
 [/set of variables]
run;

So what you need is a couple of macros. Assuming you have a dataset with

<dataset> <varname> <to> <from>

You can call this pretty easily. Two ways:

Run it as a set of nested macros/calls. This is a bit messier, but might be a bit easier to understand.

 %macro do_dataset(data=);
  proc sql noprint;
    select cats('%convert_Var(var=',varname,',to=',to,',from=',from,')')
      into :convertlist separated by ' '
      from dataset_with_conversions
      where dataset="&data.";
  quit;

  data &data;
   set &data;
   &convertlist.;
  run;
 %mend do_dataset;

%macro convert_var(var=,to=,from=);
&var. = translate(&var.,"&to.","&from.");
%mend convert_var;

proc sql noprint;
select cats('%do_dataset(data=',dataset,')') 
 into :dslist separated by ' '
 from dataset_with_conversions;
quit;

&dslist;

Second, you can do all of that in one datastep using call execute (rather than having two different steps). IE, do a by dataset statement, then for first.dataset execute data <dataset>; (filling in that) and for last.dataset execute run, and otherwise execute the translates.

More complicated, but one pass solution - depends on your comfort level which you prefer, they should generally work similarly.