DATA Step, Macro, Functions and more

Variable names with ":"

Reply
Contributor
Posts: 67

Variable names with ":"

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.

 

 

Super User
Super User
Posts: 9,227

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.

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;
Super User
Posts: 10,626

Re: Variable names with ":"

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

 

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

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;;
run;
Super User
Posts: 22,874

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
  • 152 views
  • 3 likes
  • 5 in conversation