Need help dropping variables in SAS with same suffix

Question

NOTE: Invalid argument 2 to function SUBSTR. Missing values may be generated. NOTE: No rows were selected.

%let suffix=_mic;
proc sql noprint;
    select name into :_mic separated by ' ' 
    from dictionary.columns 
    where upcase(libname)=upcase('work') 
      and upcase(memname)=upcase('temp7')
      and upcase(substr(name,length(name)-(length("&suffix")-1),length("&suffix")))=upcase("&suffix");
quit;
%put &_mic;

data temp8 (compress=char);
    set temp7;
    if length("&_mic") ne 0 then do;
        drop &_mic;
    end;
run;

Answer 1

You can use the SUBSTRN() function instead of the SUBSTR() function. That will not generate an error when you use an invalid start position. (There is no need to pass the optional third parameter, length, to either one since you want the whole rest of the name.)

Note there is no need to UPCASE() the LIBNAME or MEMNAME variables from DICTIONARY.COLUMNS as they are always in uppercase.

The DROP statement is not conditional so you will need to use macro logic if you want to conditionally generate the DROP statement.

It is much easier to just test the automatic macro variable SQLOBS to tell if any variables where found.

You need to make sure the target macro variable is defined as it will not be defined when no observations match the WHERE condition.

%let suffix=_mic;

proc sql noprint;
%let _mic=;
  select name into :_mic separated by ' ' 
  from dictionary.columns 
  where libname=upcase('work') 
    and memname=upcase('temp7')
    and upcase(substrn(name,1+length(name)-length("&suffix")))=upcase("&suffix")
  ;
%let nvars=&sqlobs;
quit;
%put &=nvars &=_mic;

data temp8 (compress=char);
    set temp7;
%if &nvars %then %do;
    drop &_mic;
%end;
run;