06-13-2017 05:42 AM - edited 06-13-2017 05:46 AM
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
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,
06-13-2017 05:53 AM
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):
06-13-2017 06:00 AM
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.
06-13-2017 06:33 AM
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:
If you goto the dataset created by proc import, in the SAS explorer, and right click on the dataset, what does it show you?
06-13-2017 07:30 AM
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.
06-13-2017 07:35 AM
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 ;