Renaming variables to be read in SAS

[BernieK]

I am running SAS Studio 9.4 and am trying to rename variables that have spaces in them so that they can be read in SAS.

For example I have a variable Dx status but since it has a space in it SAS cannot read it. This is part of the original dataset.

Is there a way to rename it to Dx_status?

Thank you!

Bernie

[BernieK]

data One;
set Merlin.data;
options validvarname=any;
rename Dx status='Dx_status';
run;

[novinosrin]

options validvarname=any;

data One;
set Merlin.data;

rename 'Dx status'n=Dx_status;
run;

[ballardw]

Close. Try:

options validvarname=any;
data One;
   set Merlin.data;
   rename 'Dx status'n=Dx_status;
run;

A name that needs the Validvarname option to be usable requires the quotes and n to indicate a "name literal". The quotes are needed to tell SAS where the unfriendly name starts and ends and the n that is a name. The new variable name should not be in quotes.

[agannon]

You can dynamically change the variable names by utilizing SAS Dictionary Tables. The code below pulls all the variables from the table that have spaces and creates a rename statement that can convert them in a downstream data step operation.

/* Data with spaces in variable names */
data work.in_data;
	'Dx Status'n = "a";
	'Another Status'n = 23;
	good_status = "yes";
run;

/* Create rename statement */
proc sql noprint;
	select cats("'",strip(name),"'n=",tranwrd(strip(name)," ","_")) into :rename_stmt separated by " "
	from dictionary.columns
		where find(strip(name),' ','i')
			and upcase(libname) eq 'WORK'
			and upcase(memname) eq 'IN_DATA'
	;
quit;

/* Apply rename statement */
data work.want;
	set work.in_data (rename=(&rename_stmt));
run;

The &rename_stmt variable will resolve to:

Which, when applied, will generate the desired variable names.

[Tom]

Note that the NLITERAL() function will generate a valid variable name from a string.  If the value is already a valid name then it does nothing. Otherwise it adds quotes and the N suffix.  It will pick the best quote character to use.

Note that changing just the spaces to underscore does not guarantee a valid name.  Also removing leading spaces by using STRIP() instead of just removing trailing spaces with TRIM() might lead to duplicate names. 

catx('=',nliteral(name),nliteral(tranwrd(trim(name),' ','_')))

[Reeza]

option validvarname=v7;

Now import your data and SAS will automatically fix your variable names to be valid SAS names without spaces.