topic Re: proc sql, renaming all but a few variables in SAS Programming

proc sql, renaming all but a few variables

MillerEL — Wed, 13 Sep 2017 17:58:35 GMT

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;

Re: proc sql, renaming all but a few variables

novinosrin — Wed, 13 Sep 2017 18:02:41 GMT

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;

Re: proc sql, renaming all but a few variables

ChrisBrooks — Wed, 13 Sep 2017 18:07:48 GMT

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.

Re: proc sql, renaming all but a few variables

MillerEL — Wed, 13 Sep 2017 18:20:57 GMT

Thanks! I'll give that a try.

Re: proc sql, renaming all but a few variables

novinosrin — Wed, 13 Sep 2017 18:24:08 GMT

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.

Re: proc sql, renaming all but a few variables

MillerEL — Wed, 13 Sep 2017 18:27:50 GMT

Thanks. I tried this but my syntax was a little different. This worked great!

Re: proc sql, renaming all but a few variables

MillerEL — Wed, 13 Sep 2017 18:31:31 GMT

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?

Re: proc sql, renaming all but a few variables

novinosrin — Wed, 13 Sep 2017 18:33:52 GMT

Are you dropping somewhere? Can you share your code plz

Re: proc sql, renaming all but a few variables

Tom — Wed, 13 Sep 2017 18:35:14 GMT

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

Re: proc sql, renaming all but a few variables

MillerEL — Wed, 13 Sep 2017 18:37:39 GMT

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.

Re: proc sql, renaming all but a few variables

MillerEL — Wed, 13 Sep 2017 18:45:07 GMT

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!

Re: proc sql, renaming all but a few variables

Reeza — Wed, 13 Sep 2017 18:45:16 GMT

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

Re: proc sql, renaming all but a few variables

MillerEL — Wed, 13 Sep 2017 18:51:48 GMT

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!