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

[ilikesas]

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

[FriedEgg]

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

'greatest score'n would correct this specific issue.

[ilikesas]

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

[FriedEgg]

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.

[ilikesas]

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

[FriedEgg]

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;

[ilikesas]

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

[Reeza]

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;

[ilikesas]

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

[Reeza]

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.

[ilikesas]

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

[Patrick · Accepted solution]

If you want to simply use the data as-is, make sure the VALIDVARNAME option is set to ANY:

options validvarname=any;

proc sort data=uc;
  by 'greatest score'n;
run;

If you would rather rename to "traditional" SAS variable names, read on.

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

[Reeza]

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.

[slchen]

Try to add:

options validvarname=any;

[Reeza]

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.