CODEWITHSUNDEEP
sas
blue and grey
blue and grey

Reputation: 401

Rename nonsequential variable names to sequential names in sas

I am working with survey data where the variable names in our database are descriptive, and not sequentially numbered. They are sequential in the database (moving from left to right). I would like to work in my programs with numbered variables, and I have been unsuccessful in trying to rename them programmatically without having to write out every change by hand (there are 87 total variables).

I have tried to use array, but that has not worked since they are not named sequentially nor do they have a common structure (no common prefix or suffix).

Example data is below:

data svy;
  input id relationship outburst checkwork goodideas ;
cards;
101 3 4 5 6
102 4 5 6 6
103 1 1 8 1
104 2 3 2 4
;
run;

*****  does not work  ;
data svy_1; set svy;
rename relationship--goodideas = var01--var04;
run;
quit;

The above code returns the following error in the log:

ERROR: Missing numeric suffix on a numbered variable list (relationship-goodideas).

I would like to rename the variables to something like: var01, var02, etc...

Any help is greatly appreciated.

Upvotes: 0

Views: 3174

Answers (2)

blue and grey
blue and grey

Reputation: 401

A colleague came up with the best approach:

*****  does work ;

data svy_1;
set svy;
array old { 4 } relationship--goodideas;
array var { 4 } ;

do i = 1 to 4;
var[i] = old[i];
end;
drop i;
run;

Upvotes: 1

Joe
Joe

Reputation: 63424

A few things:

Your data step #2 isn't right - it doesn't have a set statement. Also, it doesn't require 'quit' - quit is only for certain PROCs that generally are 'programming environments', such as PROC SQL, PROC FORMAT, PROC DATASETS. It doesn't do any harm but it looks odd :)

Sequential-in-the-dataset variable lists are double dash. So, you could trivially create an array with these:

array myvars relationship--goodideas;

So if that's good enough for you (no rename), then, go for it. If you really want to rename them (a bit of a bad idea IMO since it takes away some meaning of the variable name, making code harder to read, though I understand the reasoning why you'd want to), you can't use this unfortunately - while it's correct, the RENAME statement does not support it.

82   *****  does not work  ;
83   data svy_1;
84   rename relationship--goodideas = var01-var04;
            ------------
            47
ERROR 47-185: Given form of variable list is not supported by RENAME. Statement is ignored.

85   run;

You cannot use an array to perform rename statements, unfortunately; so you'll have to do something else. Here's one answer.

proc contents data=svy out=svy_vars(keep=name varnum) noprint;
run;

proc sort data=svy_vars; 
by varnum;
run;

data for_rename;
set svy_vars;
if name in ('relationship' 'outburst' 'checkwork' 'goodideas') then do;
  namectr+1;
  new_name=cats(name,'=','var',put(namectr,z2.));
  output;
end;
run;

proc sql;
select new_name into :renlist separated by ' ' from for_rename;
quit;

proc datasets nolist;
modify svy;
rename &renlist;
quit;

You can do something similar in a shorter fashion using PROC SQL and the DICTIONARY.COLUMNS table, or a data step and SASHELP.VCOLUMN, but the proc contents method is somewhat more transparent as to what's happening. If you have more than four variables, you may want to change that IN statement into a negative statement (if name not in (list of things to not change)) if that's easier, or even use the VARNUM variable itself to determine which variables you want to change (if varnum in (2:5) would work there).

Upvotes: 1

Related Questions