Rename all variables.

[CHL0320]

Hi everyone,

I would like to rename all variables by adding a suffix _V1. I have the following codes using sashelp.clasa as an example. The codes have error messages.

ERROR 73-322: Expecting an =.
ERROR 76-322: Syntax error, statement will be ignored. 

Please suggest. Thank you. 

Data class;
set sashelp.class;
run;

proc contents data=class out=vars(keep=name);
run;

data vars (keep=newname);
set vars;
newname=trim(left(name))||"_V1";
run;

proc sql;
select trim(left(newname))
into :renam_list separated by " "
from vars;
quit;

proc datasets library = work nolist;
modify class;
rename &renam_list;
quit;

proc contents data =class;
run;

[SuryaKiran · Accepted solution]

Your rename has to be name=name_V1. Using dictionary table instead  is much easier. 

data class;
set sashelp.class;
run;
proc sql;
select name||"="||cats(name,'_V1') into:rename_list separated by " "
from dictionary.columns
where libname="WORK" and memname="CLASS";
quit; 

proc datasets library = work nolist;
modify class;
rename &rename_list;
quit;


proc contents data =class;
run;

[hiteshchauhan1]

Hi ,

While running the same code with a few tweaks here and there according to my requirements i am getting the following error:

ERROR: The following columns were not found in the contributing tables:

I am using a dataset which has been output to a folder and using that libname instead of 'work' in the code .below is the code that i am using:

libname H "Path of the library";

proc sql noprint;
select cats(class,'=','new_',class)
into :list
separated by ' '
from dictionary.columns
where libname = 'H' and memname = 'Data;
quit;

Please help me with this issue. Thanks.

[CHL0320]

where libname="H" and memname="DATA";Both libname and memname need to be capitalized. 

[hiteshchauhan1]

I tried that too ... btw my code is working now and the issue is really weird. i will try to explain the best i can. So, i was using an already created dataset. apparently you need to create a new variable using a data statement .... first create a new dataset and set it to your origional data and then create a new variable in the newly created dataset,  then use that new variable in select cats() statement then it will work. Happened with me and it is really weird.

[Tom]

There is no variable named CLASS in DICTIONARY.COLUMNS.

I assume you want the variable named NAME that has the variable names.

[hiteshchauhan1]

isn't dictionary.columns actually contains all the variables which we define in this statement below:
where libname = "" and memname="").

[Tom]

---

@hiteshchauhan1 wrote:

isn't dictionary.columns actually contains all the variables which we define in this statement below:
where libname = "" and memname="").

---

The DATA has the information for the datasets selected by that where clause but what observations you select won't change the structure of the DICTIONARY.COLUMNS dataset.

9925  proc sql;
9926  describe table dictionary.columns;
NOTE: SQL table DICTIONARY.COLUMNS was created like:

create table DICTIONARY.COLUMNS
  (
   libname char(8) label='Library Name',
   memname char(32) label='Member Name',
   memtype char(8) label='Member Type',
   name char(32) label='Column Name',
   type char(4) label='Column Type',
   length num label='Column Length',
   npos num label='Column Position',
   varnum num label='Column Number in Table',
   label char(256) label='Column Label',
   format char(49) label='Column Format',
   informat char(49) label='Column Informat',
   idxusage char(9) label='Column Index Type',
   sortedby num label='Order in Key Sequence',
   xtype char(12) label='Extended Type',
   notnull char(3) label='Not NULL?',
   precision num label='Precision',
   scale num label='Scale',
   transcode char(3) label='Transcoded?',
   diagnostic char(256) label='Diagnostic Message from File Open Attempt'
  );

[akosyan]

This is helpful thanks,

Can you let me know how to do the same for columns that have space in the variable name ?

[Tom]

Use the NLITERAL() function around the NAME variable in the SQL query.  If the value follows normal SAS naming rules then it is not changed. Otherwise it is enclosed in quotes and the letter N is appended.  So a name like A.B will become 'A.B'n.

[ballardw]

Since you are attempting to add 3 characters to the name do you have any variable names that currently have a length of 30 or greater? The result of adding _V1 would exceed the length of variables.