Replacing " " with a "_" for variable names in a given dataset

[fre]

Dear Community,

I'm refering to this topic:

https://communities.sas.com/t5/SAS-Data-Management/Replacing-quot-quot-with-a-quot-quot-for-variable...

This topic is closed (locked), so I have to start a new thread for my question:

The solution presented in this topic only removes the spaces in the variable names, it doesn't replace them with an underscore.

What should be adapted to the code in the solution so that a variablename "variable 1" will be replaced by "variable_1"?

This is the code:

%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,_ -);

[Kurt_Bremser]

Use the translate() function:

select cats("'",name,"'n =",translate(name,"&replace_char","&remove_char")) into :rename_list

and add the parameter to the macro definition:

%macro rename_vars(table,remove_char,replace_char);

[Astounding · Accepted solution]

Note that a slight modification is probably in order.  Variable names often contain trailing blanks, and those should probably be removed rather than replaced:

select cats("'",name,"'n =",translate(strip(name),"&replace_char","&remove_char")) into :rename_list

[fre]

Hi, thank you for your replies

I've tested both of your answers, but the space keeps existing between the variable names.

This is what I used to call the macro:

%rename_vars(my_dataset, ,_);

I've also tried to put the space between ' ' and " ", but that didn't help either.

Could it be that something is still missing in the code?

[Kurt_Bremser]

A blank on its own is nothing for the macro preprocessor. Use the %str macro function to force creation of a blank:

%rename_vars(my_dataset,%str( ),_);

[Astounding]

What does the first statement now look like (the %macro statement)?

What does the %PUT statement display?  (Just a portion of the result is fine.)

[fre]

Thank you Kurt, the %str( ) did the job.

[Kurt_Bremser]

Use the strip() or trim() function to get rid of leading and trailing blanks, or trailing blanks only. See @Astounding's previous post in correction of mine.