topic Re: How to rename all the column names of a dataset in SAS Programming

How to rename all the column names of a dataset

UdayGuntupalli — Tue, 18 Sep 2018 15:50:58 GMT

All,
I would like to request your help in guiding me in my attempt to rename all the columns in a SAS dataset. I have gone through different approaches that have been explained by different people, I am not sure on how to apply these solutions to my case.

My attempt is based on the SUGI submission (http://www2.sas.com/proceedings/sugi28/118-28.pdf)

%Macro renameallcols(lib,dsn);
	
	Options Pageno= 1 NoDate; 
	Proc Contents Data = &Lib..&dsn; 
	Title "Before Renaming All Variables"; 
	Run; 
	
	Proc Sql NoPrint;
		Select nvar into :num_vars
			From dictionary.tables
			Where libname="&LIB" 
			and memname="&DSN";
		Select Distinct(name) into :var1-
			:var%TRIM(%LEFT(&num_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;
			&&var&i=Col_&i.
		%End;
		;
	Quit;
	Run;
	
	Options Pageno= 1 NoDate; 
		Proc Contents Data = &Lib..&dsn; 
		Title "Before Renaming All Variables"; 
	Run; 
%Mend renameallcols;

%renameallcols(SASHelp,Air);

I have made a minor modification to adjust to my needs i.e. the name that is being assigned is a little different that what was being discussed in the paper. In most of the relevant posts that I see in the community, a lot of people are trying to define a "rename" list where they define "oldname = newname" and save it and run it in a do-while loop. I think the approach adopted by the author of the paper is very similar. Can someone kindly explain why the above is failing ?

Re: How to rename all the column names of a dataset

Reeza — Tue, 18 Sep 2018 15:54:35 GMT

Are you trying to rename all the variables to COL1-COLN?

Re: How to rename all the column names of a dataset

Reeza — Tue, 18 Sep 2018 16:00:14 GMT

Your issue is with the first step. The sashelp or dictionary tables are uppercase, so make sure to upper case them in the query.

And you really don't want to change the SASHELP datasets so try a different test data set.

Here's a simplified version:

%Macro renameallcols(lib,dsn);
		Proc Contents Data = &Lib..&dsn; 
		Title "Before Renaming All Variables"; 
	    Run; 
	
	Proc Sql NoPrint;
		Select Distinct(name) into :var1-
			From dictionary.columns
			Where libname=%upcase("&LIB")
			and memname=%upcase("&DSN");
	Quit;
    %let num_vars = &sqlobs.;
	
	Proc Datasets library=&LIB;
		Modify &DSN;
		Rename
		%Do i=1 %to &num_vars;
			&&var&i=Col_&i.
		%End;
		;
	Quit;

	 
		Proc Contents Data = &Lib..&dsn; 
		Title "After Renaming All Variables"; 
	    Run; 
%Mend renameallcols;



data work.air;
set sashelp.air;
run;
options mprint symbolgen;
%renameallcols(work,Air);

Re: How to rename all the column names of a dataset

ballardw — Tue, 18 Sep 2018 16:00:50 GMT

Failing is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Or at least clearly explain the unexpected output.

Note that messing with the data sets in the SASHELP library may not be a good idea. And potentially in some environments they may even be read only so you can't rename the variables in place.

If I make a copy of SASHELP.Class in my work library then this macro did rename the variables in work.class.

SASHELP.AIR does not exist in my install.

Re: How to rename all the column names of a dataset

VDD — Tue, 18 Sep 2018 16:16:26 GMT

It sounds like your IT people are doing their job, ensuring that you don't monk up stuff.

Re: How to rename all the column names of a dataset

novinosrin — Tue, 18 Sep 2018 18:13:17 GMT

If you are familiar with hashes. There is away using Hashes as it accepts literal expressions. Looping num and char arrays and feeding into hash obj at run time hash.definekey(' ') in a loop. Hash guru PD aka Paul Dorfman who is very busy now and will be here in a couple of weeks or less has demonstrated alike in many of his papers. You could output the hash renamed contents to a dataset.

I am not recommending, however it's nice to bring some awareness while I got lucky to receive privileges from PD on a personal level