SAS 9.2 and SAS 9.4 create different variable names when import excel files.

[yangx]

Hello,

When I used proc import to import an excel file, I got different variable names for SAS version 9.2 and 9.4.

Below is the program:

proc import datafile="copy.xls" out=othlab1 dbms=xls replace;
getnames=yes;
datarow = 2;
run;

In the excel file one column has "HbA1c (fraction)" as label, when I ran the program using SAS9.2, I got "NOTE: variable name change: HbA1c (fraction) -> VAR19"; if I ran the program using sas9.4, I got  "NOTE:    Variable Name Change.  HbA1c (fraction) -> HbA1c__fraction_. I know bracket is not valid SAS variable name, it should change to VAR followed by a number, but whey does SAS9.4 interpret this as a different way? Thanks.

xiumei

[Kurt_Bremser]

It seems that SAS 9.4 tries to preserve more of the original variable name when converting to a valid SAS name.

If you want to have consistent control over data import, do not use the Excel format; instead save your data as a csv file and import that with a data step where you set variable names.

[Tom]

SAS made major upgrades in the support of Excel format files. I suspect that this includes changes how it renames the illegal column names.

What happens if you try the same with the system option VALIDVARNAME set to ANY.

[yangx]

Hello,

Thanks for your quick reply. I tried to use system option VALIDVARNAME=ANY, both versions produced the same variable names.

Xiumei

[ballardw]

I'm feeling a little snarky today so I'll say this is yet another reason not to use Excel for data interchange and not to rely on proc import for repetitive tasks.