DATA Step, Macro, Functions and more

Variable names with ":"

Posts: 67

Variable names with ":"



I would be greatefull if anyone could help me with ':' in variable name.  When I import from excel files I ve got var names like 'Data var: id'n while I can manage(drop, keep, rename) with names without ':' exemple  'Data var name'n, I can`t do anything with first one.


Thank you.



Super User
Super User
Posts: 9,840

Re: Variable names with ":"

Unfortunately your a bit stuck.  : means any variable with a prefix of.  However this is not generally the case with Excel.  Let me explain, first Excel is a really poor data medium.  As you are finding, there is no real rules on what goes into a spreadsheet, so you "varaible" names could be anything, even pictures.  Now for SAS to try to work with this mess, they give you the option of referring to variables (we will call it that, but in Excel terms it is the first cells contents) using named literals, or, look for this text in the box.  Behind the scenes SAS creates a SAS compliant name for it by stripping spaces, special characters and fixing length.

Simple solution, don't use Excel - it really does cause so many problems.

More difficult solution, either know the names and put them in by hand, or use a bit of code to get the variable names, and then work with that - be prepared for your code to fail at each run as Excel "names" change though.


proc sql;
  select NAME
  into   :VLIST separated by ' '
     and MEMNAME="YOURDS";

* Use
data want;
set yourlib.yourds (keep=&vlist.);
Super User
Posts: 10,850

Re: Variable names with ":"

use the following option to get legal variable name of SAS.


options validvarname=v7 ;
proc import ............
Valued Guide
Posts: 596

Re: Variable names with ":"

Options validvarname=any should work here - but I agree, change it to a "normal" SAS name ASAP e.g.


options validvarname=any;

data test(rename=("Data Var:id"n=DataVarid));
	set sashelp.class;
	"Data Var:id"n=1;;
Super User
Posts: 24,010

Re: Variable names with ":"

If you set the OPTION VALIDVARNAME=V7 before you import the data, SAS will automatically convert the variable name to a valid SAS name and keep the name as a label. 


You can then rename it if you want and I suspect that's really the easiest fix.

Ask a Question
Discussion stats
  • 4 replies
  • 5 in conversation