topic Re: Replace Space with underscore in Variable Name in SAS Data Management

Replace Space with underscore in Variable Name

LouiseG — Thu, 10 Oct 2019 00:24:24 GMT

I have large SAS dataset that has spaces in the variable name. I wish to replace the space with underscore. For Example Bus City = Bus_City.

I found this macro in the formun that removes the spaces only. Using the above example, this macro does this: Bus City =BusCity Can someone please help me modify this macro to replace the space with underscore.

data sample;
  set sashelp.class;
  age_student=age;
  'gender - student'n=sex;
run;

%macro rename_vars(table, remove_char);
  %let table=%upcase(&table);
  %let rename_list=;
  proc sql noprint;
    select cats("'",name,"'n =",compress(name, "&remove_char")) into :rename_list
      separated by " "
    from sashelp.vcolumn
      where upper(libname)="%scan(WORK.&table,-2)"
        and upper(memname)="%scan(&table,-1)"
        and findc(strip(name), "&remove_char")>0;
  quit;

  %put rename_list: %bquote(&rename_list);

  %if %bquote(&rename_list) ne %then
    %do;
      proc datasets lib=%scan(WORK.&table,-2);
        modify %scan(&table,-1);
          rename &rename_list;
        run;
      quit;
    %end;
%mend;

/*  call the macro. First parameter is the table name,
    second parameter the characters to be removed in variable names */
%rename_vars(sample,' ');

Re: Replace Space with underscore in Variable Name

Tom — Thu, 10 Oct 2019 00:35:31 GMT

Just change the function used to generate the new name.

translate(trim(name),'_',' ')

You can also simplify the whole macro.

%macro rename_vars(table);
  %local rename_list sqlobs;
  proc sql noprint;
    select catx('=',nliteral(name),translate(trim(name),'_',' '))
      into :rename_list separated by ' '
    from sashelp.vcolumn
      where libname=%upcase("%scan(work.&table,-2,.)")
        and memname=%upcase("%scan(&table,-1,.)")
        and indexc(trim(name),' ')
    ;
  quit;
  %if &sqlobs %then %do ;
      proc datasets lib=%scan(WORK.&table,-2);
        modify %scan(&table,-1);
          rename &rename_list;
        run;
      quit;
  %end;
%mend rename_vars;

Re: Replace Space with underscore in Variable Name

LouiseG — Thu, 10 Oct 2019 00:43:16 GMT

Thank you Tom. The simplify Macro works!!!