topic Re: How to add prefix to variable name? in SAS Data Management

How to add prefix to variable name?

jing2000yr — Wed, 06 May 2015 15:38:27 GMT

Hi all,

Need some help in rename the variables.

I am going to add prefix ahead of each variable,eg (listed below the sample dataset): rename C2 to SC2; C10_1 to SC10_1...; I have 618 variables in my dataset,all of them need to add the same prefix ahead the original variable name.

The name of my dataset is 'ru2014';

Thanks.

Jing Yuan

id C2 C10_1 C11_1 C12_1 C13_1 C14_1

011039 2 1 2 2 2 2

011040 2 2 2 2 2 2

011042 2 2 2 2 2 2

011044 2 2 2 2 2 2

Re: How to add prefix to variable name?

ballardw — Wed, 06 May 2015 16:00:12 GMT

Do all of the variables you want to rename start with C? If so, are there any variables that start with C that you do not wish to rename?

If you have variables other than those that start with C, how do we determine which ones need to be renamed?

Re: How to add prefix to variable name?

user24feb — Wed, 06 May 2015 16:59:44 GMT

Maybe this approach is sufficient (or tells us what's missing):

Data ru2014;
Input id C2 C10_1 C11_1 C12_1 C13_1 C14_1;
Datalines;
                    011039           2           1           2           2           2           2
                    011040           2           2           2           2           2           2
                    011042           2           2           2           2           2           2
                    011042           2           2           2           2           2           2
                    011044           2           2           2           2           2           2
;
Run;

Proc SQL NoPrint;
Select Catt(Name,'=S',Name) Into :Rename_List Separated By ' ' From
(Select Name From Dictionary.Columns Where UpCase(Libname) eq "SAS_WORK" AND UpCase(MemName) eq "RU2014" And UpCase(Substr(Name,1,1)) eq "C"); * IMPORTANT: check your libname!!!;
Quit;
%Put **&Rename_List.**;

Data Want;
Set ru2014;
Rename &Rename_List.;
Run;

Re: How to add prefix to variable name?

Steelers_In_DC — Wed, 06 May 2015 17:01:32 GMT

I can't test it but this should work:

data have;

infile cards dsd;

input id $ C2 C10_1 C11_1 C12_1 C13_1 C14_1;

cards;

011039,2,1,2,2,2,2

011040,2,2,2,2,2,2

011042,2,2,2,2,2,2

011044,2,2,2,2,2,2

;

run;

proc sql;

CREATE TABLE TEST1 AS

select trim(name) || '=' || 'S' || name

into :renamelist separated by ' ' from

(select name

from dictionary.columns

where libname='WORK' and memname='HAVE' and upcase(name) in

(select upcase(name)

from dictionary.columns

where libname='WORK' and memname='HAVE' and upcase(name) ne 'ID'));

quit;

%PUT &renamelist;

data have;

set merge have test1(rename=(&renamelist));

by id;

run;

Re: How to add prefix to variable name?

Patrick — Wed, 06 May 2015 23:12:56 GMT

For renaming variables can I suggest to use Proc Datasets instead of a data step approach (which unnecessarily processes all the data and re-creates the target table )

Re: How to add prefix to variable name?

Patrick — Wed, 13 Dec 2017 15:17:07 GMT

How to rename variables is a question regularly asked. Searching the communities brought up quite a few hits for me. Below code is based on what suggested here

data work.have;
  set sashelp.class;
run;
 
proc sql noprint ;
select cats(name,'=S',name) into :renames separated by ' '
  from dictionary.columns
  where libname='WORK' and memname='HAVE'
  ;
quit;
 
proc datasets nolist library=work;
  modify have;
    rename &renames;
  run;
quit;

Another solution by @Steelers_In_DC:

I can't test it but this should work:

data have;
infile cards dsd;
input id $ C2 C10_1 C11_1 C12_1 C13_1 C14_1;
cards;
011039,2,1,2,2,2,2
011040,2,2,2,2,2,2
011042,2,2,2,2,2,2
011042,2,2,2,2,2,2
011044,2,2,2,2,2,2
;
run;
 
proc sql;
CREATE TABLE TEST1 AS
select trim(name) || '=' || 'S' || name
into :renamelist separated by ' ' from
(select name
from dictionary.columns
where libname='WORK' and memname='HAVE' and upcase(name) in
(select upcase(name)
from dictionary.columns
where libname='WORK' and memname='HAVE' and upcase(name) ne 'ID'));
quit;
 
%PUT &renamelist;
 
data have;
set merge have test1(rename=(&renamelist));
by id;
run;

Re: How to add prefix to variable name?

RW9 — Thu, 07 May 2015 10:20:38 GMT

Whilst the above posts give some excellent answers to the problem, I would ask why you have a dataset with columns that look like that. Would it not make your life easier to have column Names to be be of the same form and array-able, and then have the column Label have any display information, e.g.:

COL1 COL2 COL3 ...

"C2" "C10_1" "C11_1" ...

In this method you can then use array processing, and simple logic across variables without having to "know" the specific variable setup.