SAS Rename variables using a list of variables in a macro

Question

A novice in SAS here. I am trying to rename variables in a data set by using the new values I have in a list. Since I have multiple files with over 100 variables that need to be renamed, I created the following macro and I am trying to pass the list with the new names. However, I am not sure how to pass the list of variables and loop through it properly in the macro. Right now I am getting an error in the %do loop that says: "ERROR: The %TO value of the %DO I loop is invalid."

Any guidance will be greatly appreciated.

The list of new variables comes from another macro and it is saved in &newvars. The number of variables in the files are the same number in the list, and the order they should be replaced is the same.

%macro rename(lib,dsn,newname);

proc sql noprint;
 select nvar into :num_vars from dictionary.tables
 where libname="&LIB" and memname="&DSN";

select distinct(nliteral(name)) into:vars
 from dictionary.columns
 where libname="&LIB" and memname="&DSN";
 quit;
run;

proc datasets library = &LIB;
    modify &DSN;
    rename
    %do i = 1 %to &num_vars.;
     &&vars&i == &&newname&i.
    %end;
;
quit;
run;
%mend rename;

%rename(pga3,selRound,&newvars);

Thank you in advance.

Hi all, I am back on this same question. Now, I have my list of variables containing special characters. For the following statement, the list of variables vars has funky characters. I tried to use qscan() but it didn't work well. Any ideas? This is what I have now: — Betabr
%macro rename(LIB,DSN,newname); proc sql noprint; select nliteral(name) into:vars1 - from dictionary.columns where libname=%upcase("&LIB") and memname=%upcase("&DSN") order by VARNUM; %let num_vars=&sqlobs; run; proc datasets library = &LIB; modify &DSN; rename %do i = 1 %to &num_vars.; %qscan(&vars,&i) = %scan(&newname,&i,%str( )); %end; run; quit; %mend rename; — Betabr

Tom Tom · Accepted Answer · 2018-02-25T00:53:33

You are getting that error message because the macro variable NUM_VARS is not being set because no observations met your first where condition.

The LIBNAME and MEMNAME fields in the metadata tables are always uppercase and you called the macro with lowercase names.

You can use the %upcase() macro function to fix that. While you are at it you can eliminate the first query as SQL will count the number of variables for you in the second query. Also if you want that query to generate multiple macro variables with numeric suffixes you need to modify the into clause to say that. The DISTINCT keyword is not needed as a dataset cannot have two variables with the same name.

select nliteral(name)
  into :vars1 - 
  from dictionary.columns
  where libname=%upcase("&LIB") and memname=%upcase("&DSN")
;
%let num_vars=&sqlobs;

You also should tell it what order to generate the names. Were the new names generated in expectation that the list would be in the order the variables exist in the dataset? If so use the VARNUM variable in the ORDER BY clause. If in alphabetical order then use NAME in the ORDER BY clause.

How are you passing in the new names?

Is it a space delimited list? If so your final step should look more like this:

proc datasets library = &LIB;
  modify &DSN;
  rename
%do i = 1 %to &num_vars.;
    &&vars&i = %scan(&newname,&i,%str( ))
%end;
  ;
run; quit;

If NEWNAME has the base name to use for a series of variable names with numeric suffixes then you would want this:

    &&vars&i = &newname&i

If instead you are passing into NEWNAME a base string to use to locate a series of macro variables with numeric suffixes then the syntax would be more like this.

    &&vars&i = &&&newname&i

So if NEWNAME=XXX and I=1 then on the first pass of the macro processor that line will transform into

    &vars1 = &XXX1

And on the second pass the values of VARS1 and XXX1 would be substituted.

SAS Rename variables using a list of variables in a macro

1 Answers