BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jing2000yr
Calcite | Level 5

   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

                    011042           2           2           2           2           2           2

                    011044           2           2           2           2           2           2

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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:

 

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;

 

View solution in original post

6 REPLIES 6
ballardw
Super User

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?

user24feb
Barite | Level 11

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;

Steelers_In_DC
Barite | Level 11

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;

Patrick
Opal | Level 21

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 )

Patrick
Opal | Level 21

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:

 

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;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 19767 views
  • 5 likes
  • 6 in conversation