Managing carriage return in variable names

[Nathan_Gurnet]

Hi there,

I faced big issues when I discovered that my variable names were containing carriage return in it. The problem is that when your variables names contains carriage return, it is possible to call the variables with a KEEP statement but when you want to call a specific variable, SAS will not read correctly the request. . For example if your variable name is 

WO
K 

the following code will indeed read correctly the request: 

data want; 
     set have; 
     keep w:;
run;

and that code will then not work: 

data want; 
      set have;
wok="WO
K"N;
run;

It is then not possible to rename the variables since SAS is not capable of reading it. I worked a lot to find a solution that finally was not that complicated. I wanted then to share it with you if ever, you had to manage variables names with carriage return in it. So here is the macro that will manage it: 

 

%macro carriage /*Macro that drop carriage returns from variable names*/
(lib /* Specify library*/,
data /*Specify dataset*/,
out /*Specify output dataset (mandatory in order to not erase proper data*/,
vars /*Specify variables to rename*/);


data &lib..&out; 
	set &lib..&data;
	keep &vars;
run;

data &lib..&data;
	set &lib..&data;
	drop &vars; 

proc transpose 
	data=&lib..&out 
	out=&lib..&out;
run;

data &lib..&out;
	set &lib..&out; 
	_NAME_=compress(_NAME_,'0D0A'x);
run;
proc transpose 
	data=&lib..&out 
	out=&lib..&out(drop=_name_);
run;

data &lib..&data;
	merge &lib..&data &lib..&out;
run;
%mend carriage; 

Have a good day, 

Nathan 

[RW9]

Sorry, this doesn't make sense.  Variable Names cannot contain special characters such as carriage returns.

What is the data, where is it coming form, how are you importing it etc?  Are you perhaps using named literals and the "name" from Excel has carriage returns in it?

From the docs (note its V8 but still should be true):

https://v8doc.sas.com/sashtml/lgref/z1031056.htm

[Nathan_Gurnet]

Hi there, 

I gathered my data with the limesurvey online software. For an unknown reason limesurvey version at my office cannot manage the capital R. So when I exported the data in .xls format, limesurvey replaced the R by a carriage return. I then did the importation with a PROC IMPORT procedure. Managing my variables names directly from limesurvey or Excel was not a solution because I have many files and many variables (+1000). I had then to find a quick solution with SAS and here it is. This is maybe not the most elegant answer but it does work. I post it because I found no solution on internet about that problem. This is maybe due of what you say: sas would normally not permit such character in a variable name. 

[RW9]

I am sorry, a variables name cannot contain a carriage return.  In your given example you are using named literals, this isn't however the actual name of the variable:

data want; 
      set have;
wok="WO              <- this bit here
K"N;
run;

If you open the properties of have dataset and look at the list of variables you will see that the variable in the have dataset looks something like:

WO_K

If you goto the dataset created by proc import, in the SAS explorer, and right click on the dataset, what does it show you?

[Nathan_Gurnet]

Hi, 

I know that SAS does not allow character such as carriage return in variable names. However when importing data through a PROC IMPORT  with an excel file where the variable names contains carriage return, it does appear in the SAS Datafile (at least, it was my case, see attached file). 

The code you mentionned was just a demonstration to show that it is not possible to call literaly those variables. I am sorry for the misunderstanding, I guess I lacked clarity in my previous post, I just wanted to help people who would face the same issue I had. 

[BrunoMueller]

Before using Proc IMPORT you should set the SAS System Option VALIDVARNAME=V7 so that non standard characters are translated to an underscore. This way you will have valid names, but be aware that the names might be different to what you have when using VALIDVARNAME=ANY.

SAS Enterprise Guide will set VALIDVARNAME=ANY by default. Check your settings under Tools -> Options -> Data General

But I recommend to add the setting to your code like this:

options
  validvarname=v7
;

[Eric_R]

Very helpful thread, thank you!

Including this code in my program solved my identical problem with the 'carriage return' being made part of the variable name created when I brought in data from Excel via a Proc Import process.

options
  validvarname=v7
;

  This issue stumped me all day long  and now... solved!