DATA Step, Macro, Functions and more

proc sql, renaming all but a few variables

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

proc sql, renaming all but a few variables

Hello,

 

I have a dataset with ~200 variables. I need to rename the entire dataset with the exception of 10 variables. Previous analysts typed out each of the 190 variables, but I'm trying to streamline the code. The following is what I came up with, and works perfectly....except it renames all the variables. Can someone help me figure out how to remove just the 10 that shouldn't be renamed?

 

Thank you.

 

DATA dnld15;  
set dnld.2015;
run;

proc sql noprint;
	select cats(name,'=',name,'Z')
	into :suffixlist
	separated by ' '
	from dictionary.columns
	where libname = 'WORK' and memname = 'DNLD15' ;
	quit;

		proc datasets lib=WORK nolist nodetails;
		modify dnld15;
		rename 
		&suffixlist;
		run;

Accepted Solutions
Solution
‎09-13-2017 02:27 PM
PROC Star
Posts: 283

Re: proc sql, renaming all but a few variables

[ Edited ]

You could add another filter to your code:

where libname = 'WORK' and memname = 'DNLD15' and name not in (list) ;

 

DATA dnld15;  
set dnld.2015;
run;

proc sql noprint;
	select cats(name,'=',name,'Z')
	into :suffixlist
	separated by ' '
	from dictionary.columns
	where libname = 'WORK' and memname = 'DNLD15' and name not in (list) ;
	quit;

		proc datasets lib=WORK nolist nodetails;
		modify dnld15;
		rename 
		&suffixlist;
		run;

 

 

View solution in original post


All Replies
Solution
‎09-13-2017 02:27 PM
PROC Star
Posts: 283

Re: proc sql, renaming all but a few variables

[ Edited ]

You could add another filter to your code:

where libname = 'WORK' and memname = 'DNLD15' and name not in (list) ;

 

DATA dnld15;  
set dnld.2015;
run;

proc sql noprint;
	select cats(name,'=',name,'Z')
	into :suffixlist
	separated by ' '
	from dictionary.columns
	where libname = 'WORK' and memname = 'DNLD15' and name not in (list) ;
	quit;

		proc datasets lib=WORK nolist nodetails;
		modify dnld15;
		rename 
		&suffixlist;
		run;

 

 

Occasional Contributor
Posts: 14

Re: proc sql, renaming all but a few variables

Posted in reply to novinosrin
Thanks. I tried this but my syntax was a little different. This worked great!
Occasional Contributor
Posts: 14

Re: proc sql, renaming all but a few variables

Posted in reply to novinosrin

Well, darn. It did rename all but 10 of the variables...but it left those un-renamed variables out of the dataset. How do I keep them in?

PROC Star
Posts: 283

Re: proc sql, renaming all but a few variables

Are you dropping somewhere? Can you share your code plz

Occasional Contributor
Posts: 14

Re: proc sql, renaming all but a few variables

Posted in reply to novinosrin
The code I posted above is it. When I run a proc contents on dnld15, it is only showing the renamed variables. This file feeds into a second program, but the second program is about 2000 lines long...and there isn't a single drop statement in either program.
Super User
Posts: 19,815

Re: proc sql, renaming all but a few variables

check the case, ie upper, lower, mixed for variable names or data set name.

Occasional Contributor
Posts: 14

Re: proc sql, renaming all but a few variables

Syntax error - forgot to check my log. The file didn't finish correctly and the proc contents was on an old file. Sorry! and Thank you!
Super User
Super User
Posts: 7,050

Re: proc sql, renaming all but a few variables


MillerEL wrote:

Well, darn. It did rename all but 10 of the variables...but it left those un-renamed variables out of the dataset. How do I keep them in?


There is nothing in the proposed solutions that would drop any variables.

Show what you did that dropped those variables?

Occasional Contributor
Posts: 14

Re: proc sql, renaming all but a few variables

I found it. Syntax error - forgot to check my log. The file didn't finish correctly and the proc contents was on an old file. Sorry! and Thank you!
Super Contributor
Posts: 440

Re: proc sql, renaming all but a few variables

You can get the column name from dictionary.columns too - I can't check the exact field name at the moment but add something like "and colname not in (''ABC", 'DEF')" to your where clause to exclude the listed columns and asuming colname is the field name in dictionary.columns which holds that info.

Occasional Contributor
Posts: 14

Re: proc sql, renaming all but a few variables

Posted in reply to ChrisBrooks
Thanks! I'll give that a try.
PROC Star
Posts: 283

Re: proc sql, renaming all but a few variables

Yes sorry, i should have mentioned what @ChrisBrooks in the change i made to your code but i was lazy. If you noticed my addition to your where condition, the name column is the column that has a list of variables names in the dictionary.columns metadata. So you can basically filter with IN operator. 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 179 views
  • 0 likes
  • 5 in conversation