topic Re: Variable names with ":" in SAS Programming

Variable names with ":"

Matt3 — Tue, 03 Oct 2017 13:01:27 GMT

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.

Re: Variable names with ":"

RW9 — Tue, 03 Oct 2017 13:08:04 GMT

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;

Re: Variable names with ":"

Ksharp — Tue, 03 Oct 2017 13:53:56 GMT

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

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

Re: Variable names with ":"

ChrisBrooks — Tue, 03 Oct 2017 15:16:22 GMT

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;

Re: Variable names with ":"

Reeza — Tue, 03 Oct 2017 16:59:30 GMT

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.