Variable names with ":"

[Matt3]

Hello,

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.

[RW9]

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.

E.g.

proc sql;
  select NAME
  into   :VLIST separated by ' '
  from   SASHELP.VCOLUMN
  where  LIBNAME="YOURLIB"
     and MEMNAME="YOURDS";
quit;

* Use
data want;
  set yourlib.yourds (keep=&vlist.);
run;

[Ksharp]

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

options validvarname=v7 ;
proc import ............

[ChrisBrooks]

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;;
run;

[Reeza]

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.