topic Re: Renaming variables to be read in SAS in SAS Programming

Renaming variables to be read in SAS

BernieK — Tue, 16 Jun 2020 21:20:55 GMT

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

Re: Renaming variables to be read in SAS

BernieK — Tue, 16 Jun 2020 21:24:11 GMT

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

Re: Renaming variables to be read in SAS

novinosrin — Tue, 16 Jun 2020 21:26:31 GMT


options validvarname=any;

data One;
set Merlin.data;

rename 'Dx status'n=Dx_status;
run;

Re: Renaming variables to be read in SAS

ballardw — Tue, 16 Jun 2020 21:29:25 GMT

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.

Re: Renaming variables to be read in SAS

agannon — Tue, 16 Jun 2020 23:34:33 GMT

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.

Re: Renaming variables to be read in SAS

Reeza — Wed, 17 Jun 2020 00:19:35 GMT

option validvarname=v7;

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

Re: Renaming variables to be read in SAS

Tom — Wed, 17 Jun 2020 00:56:43 GMT

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),' ','_')))