SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 441
Accepted Solution

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

Hi,

I have a file which is already in sas7bdat form, but the variable names are written in a usual way, for example I have "greatest score" instead of "greatest_score".

Is it possible to change the space " " with a "_" ONLY for the variable names like this I can do my analysis because when I try to I am getting a message that variables greatest and score can't be found because SAS treats them as separate.

Thank you


Accepted Solutions
Solution
‎01-08-2015 02:19 AM
Respected Advisor
Posts: 4,173

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

To answer for

In the first data step the rename is only there to create some variables with an underscore - so if you have already such variables in your actual data then you don't need to do this of course. The other bits will then work as expected also with your table. You only need to replace the Libref "WORK" and the table name "CLASS" in Reeza's code with your actual names.

May be below macro will make it easier for you (basically the same than what Reeza already posted).

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

View solution in original post


All Replies
Trusted Advisor
Posts: 1,301

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

You do have the option to use name literals, if you wish.

'greatest score'n would correct this specific issue.

Super Contributor
Posts: 441

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

Hi FriedEgg and thanks for the very quick reply!

I wrote like you said but got the message that this is not a valid name.

Here is what my code is like:

proc sort data=uc;

by 'greatest score'n;

run;

its pretty basic, but am I missing something?

Thank you

Trusted Advisor
Posts: 1,301

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

Here is code to do the renames.  It is very limited, will only cover the single case you mention where a name contains a space...

data class;

set sashelp.class;

'greatest score'n = weight;

'foo bar'n = height;

run;

data _null_;

length rename $ 32767;

rename='rename';

do until(done);

set sashelp.vcolumn end=done;

where libname='WORK' and memname='CLASS'

and not nvalid(name, 'v7')

;

rename = cat(strip(rename), ' ', quote(strip(name)),'n=',translate(strip(name), '_', ' '));

end;

call symputx('rename', rename);

run;

proc datasets lib=work;

modify class;

&rename;

run;

quit;

Given your new issue, I would ask that you do the following:

proc contents data=uc out=cnts noprint; run;

data _null_;

set cnts;

put name= ' -- ' name $hex64.;

run;

Post the output here, my assumption is that your current variable names contain some other character that not a v7 valid name literal.

Super Contributor
Posts: 441

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

Hi FriedEgg, sorry if I bother you too much with this but when I did the following:

data class;

set sashelp.class;

'greatest score'n = weight;

'foo bar'n = height;

run;

I still got a message that these are not valid names...

And when I did this:

data _null_;

length rename $ 32767;

rename='rename';

do until(done);

set sashelp.vcolumn end=done;

where libname='WORK' and memname='CLASS'

and not nvalid(name, 'v7')

;

rename = cat(strip(rename), ' ', quote(strip(name)),'n=',translate(strip(name), '_', ' '));

end;

call symputx('rename', rename);

run;

I got a message that I have 0 observations...

Thank you

Trusted Advisor
Posts: 1,301

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

It's no problem.  But, it is strange you have issues with name literals.  What version of SAS are you using and what interface are you using to submit your code?

Please run the following and provide the log information:

proc options group=sasfiles; run;

proc contents data=uc /*or whatever data set you are having issue with*/ out=cnts noprint; run;

data _null_;

set cnts;

put name= ' -- ' name $hex64.;

run;

Super Contributor
Posts: 441

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

I ran the code and there's a lot of output so I didn't want to paste it all, but when I did the proc contents I saw that the variable names are actually ok, and that the names which I reported as having a space bw them are  labels... and that's probably why SAS was telling me that I couldn't use in the code 'greatest score'n ...

But out of pure interest, I created a small data set on Excel simulating my apparent initial problem, i.e a variable named "greatest score".

I successfully imported it into SAS and when I did its proc contents I saw that SAS actually called it "greatest_score", so it seems that SAS automatically inserts the "_" .

But suppose now that what I wanted to do is to get rid of all the "_" and instead of having for ex "greatest_score" to have "greatestscore"

Thank you

Super User
Posts: 19,870

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

Use the rename statement.

1) Manually

data want;

set have;

rename greatest_score = greatestscore;

run;

2) Automatically (All variables with underscores)

data class;

set sashelp.class;

rename age= age_student sex = gender_student;

run;

proc sql noprint;

select catx("=", name, compress(name, "_")) into :rename_list

separated by " "

from sashelp.vcolumn

where upper(libname)="WORK"

and upper(memname)="CLASS"

and find(name, "_")>0;

quit;

%put &rename_list;

data want;

set class;

rename &rename_list;

run;

Super Contributor
Posts: 441

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

Hi Reeza,

thanks for the code it worked, but what if I have my own data and not the one given from sashelp, for ex:

data x;

input age_s g_by;

datalines;

1 2

3 4

;

run;

in such a case how can the "_" be automatically removed?

Thank you

Super User
Posts: 19,870

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

Replace the dataset name in the memname in the SQL section of the code.

In the second step replace your data set names where applicable.

Open the SASHELP.VCOLUMN dataset to take a look at the structure and see how it lines up with the code.

Super Contributor
Posts: 441

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

Hi Reeza,

In your code you had the following statement:

select catx("=", name, compress(name, "_")) into :rename_list

because if I understand correctly you did:

rename age= age_student sex = gender_student;

but if in my case I didn't do the rename, but just had:

input age_s g_by;


that is, there are no "=", so is it possible to put that into the catx?


Its just that I still can't figure it out and was guessing if that's what I am doing wrong...


Thank you

Solution
‎01-08-2015 02:19 AM
Respected Advisor
Posts: 4,173

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

To answer for

In the first data step the rename is only there to create some variables with an underscore - so if you have already such variables in your actual data then you don't need to do this of course. The other bits will then work as expected also with your table. You only need to replace the Libref "WORK" and the table name "CLASS" in Reeza's code with your actual names.

May be below macro will make it easier for you (basically the same than what Reeza already posted).

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

Super User
Posts: 19,870

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

It sounds like you want to fix that in your import before your data set is created.

One way is to import the data as is and then run the script I've suggested, modified to reference your data of course.

OR

Highlight the line, hit CTRL+F and Find/Replace all _ using the "IN SELECTION" option.

Super Contributor
Posts: 275

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

Try to add:

options validvarname=any;

Super User
Posts: 19,870

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

Run a proc contents on your data set to examine your dataset.

SAS has both variable names and labels, the labels will appear when you view the data set in the viewers, with spaces.

The variable name may already have an underscore.

If this isn't your issue consider posting the output of your proc contents to the forum for more help.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 1123 views
  • 6 likes
  • 6 in conversation