BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
MillerEL
Obsidian | Level 7

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;
1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

12 REPLIES 12
novinosrin
Tourmaline | Level 20

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;

 

 

MillerEL
Obsidian | Level 7
Thanks. I tried this but my syntax was a little different. This worked great!
MillerEL
Obsidian | Level 7

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?

novinosrin
Tourmaline | Level 20

Are you dropping somewhere? Can you share your code plz

MillerEL
Obsidian | Level 7
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.
Reeza
Super User

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

MillerEL
Obsidian | Level 7
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!
Tom
Super User Tom
Super User

@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?

MillerEL
Obsidian | Level 7
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!
ChrisBrooks
Ammonite | Level 13

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.

MillerEL
Obsidian | Level 7
Thanks! I'll give that a try.
novinosrin
Tourmaline | Level 20

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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