Rename column headings from &list of rename variable where name exists otherwise ignore

[TJNovice1]

I have a macro to import multiple excel spreadsheets and then to rename some column names to be consistent across the datasets. However only some of the datasets have the 'oldname' column name so rename is failing when it can't find the column name. Is there a way to use rename=(&renamelist.) to rename column names that exist and just skip over those that are not found in the datasets rather than stop the process?

proc sql print;
select catx('=', OldName, NewName)
into :renamelist separated by " "
from work.DataName;
quit;

data _null_;

set sashelp.vcolumn (where=(libname="WORK" and memname= "&&data."));
if _n_ = 1 then call execute('data &&data.; set &&data. (rename=(&renamelist.)); run;'); Run;

[ballardw]

---

@TJNovice1 wrote:

I have a macro to import multiple excel spreadsheets and then to rename some column names to be consistent across the datasets. However only some of the datasets have the 'oldname' column name so rename is failing when it can't find the column name. Is there a way to use rename=(&renamelist.) to rename column names that exist and just skip over those that are not found in the datasets rather than stop the process?

proc sql print;
select catx('=', OldName, NewName)
into :renamelist separated by " "
from work.DataName;
quit;

data _null_;

set sashelp.vcolumn (where=(libname="WORK" and memname= "&&data."));
if _n_ = 1 then call execute('data &&data.; set &&data. (rename=(&renamelist.)); run;'); Run;

---

I have to assume by "I have a macro to import multiple excel spreadsheets" that you actually mean Proc Import. If that is the case then search this forum for all the Proc Import/Excel related issues.

Strongly suggest, if these Excel files have data in the same columns but the names get changed that saving the Excel files to CSV and using a data step to read the CSV file is a much more robust and reliable approach. Then you set all of the properties besides name such as length and variable type with the data step and you don't have to "fix" anything.

If the issue is that the columns change as well then you may want to get management involved in just how much time(=$$$$) is wasted because you, and likely others, have to "fix" stuff that should be standard.

You can also search the forum for "Proc Datasets rename" and find examples of using Call Execute with data sets containing rename pairs which is much easier to manage. Also when you use a data step to rename the entire data set gets read and processed which can be time consuming with larger data sets. Proc Datasets does this in place without any of the possibilities of destroying data that arises with use of the

Data somename;

   set somename;

type of coding.

Hint for new SAS users. That structure while syntactically legal is a time bomb waiting to happen. A minor logic error and you can destroy data, lose records not intended to remove and many other bad things.