topic Re: Underscore in variable names in SAS Programming

Underscore in variable names

SASPhile — Tue, 16 Feb 2010 15:29:37 GMT

Hi,
Sometimes the variables given in excel come with spaces in front of them.SAS reads them with an underscore followed by the variable name.How to avoid this?

for instance, NDC is the field name.when we recieve the data in excel,there will be space in front of NDC so SAS reads it as _NDC.

Re: Underscore in variable names

Paige — Tue, 16 Feb 2010 16:42:27 GMT

I can think of two ways to avoid this.

1) Don't put spaces in front of variable names in Excel
2) Write a macro to remove underscores if they are the first character of a variable name.

Re: Underscore in variable names

SASPhile — Tue, 16 Feb 2010 21:23:34 GMT

I'm sharing the code with you all:
data abc(keep=name column2 column3);
set sashelp.vcolumn;
where libname='WORK' and memname='FAIRVIEW_FIN';
if substr(name,1,1) = '_' then column2 = substr(name, 2, length(name));
else column2=name;
column3=left(trim(name))||'='||column2;
run;

proc sql;
select distinct column3 into: new_column seperated by ' '
from abc;
quit;
run;

data Fairview_fin (rename = (&new_column));
set Fairview_fin;
run;

Re: Underscore in variable names

Patrick — Wed, 17 Feb 2010 10:40:57 GMT

You can use the SAS option "validvarname=any".
If "any" is set then the Excel variable names won't be converted to comply with SAS naming conventions during import.

You can do it - but I wouldn't as it will cause you quite a bit of pain. Variables with non-conforming names must be addresses as literals meaning that whenever you use the variable you have to quote the name in the form: ''n

For SAS EG 4.2
In step 2 of the import wizard there is a checkbox "rename columns to comply with SAS naming conventions". Make sure that this checkbox is not ticked if you want to avoid the conversion.
Again: I recommend to choose conversion. You still keep the excel source variable names as SAS variable labels.

HTH
Patrick