topic Replacing " " with a "_" for variable names in a given dataset in SAS Data Management

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

fre — Mon, 15 Oct 2018 12:10:49 GMT

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-names-in-a/td-p/174192

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

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

Kurt_Bremser — Mon, 15 Oct 2018 12:28:24 GMT

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);

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

Astounding — Mon, 15 Oct 2018 12:44:25 GMT

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

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

fre — Mon, 15 Oct 2018 13:15:13 GMT

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?

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

Kurt_Bremser — Mon, 15 Oct 2018 13:20:05 GMT

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

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

Astounding — Mon, 15 Oct 2018 14:05:32 GMT

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.)

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

fre — Mon, 15 Oct 2018 14:18:10 GMT

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

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

Kurt_Bremser — Mon, 15 Oct 2018 14:26:35 GMT

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.