topic Re: SAS 9.2 and SAS 9.4 create different variable names when import excel files. in SAS Programming

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

yangx — Fri, 06 Oct 2017 17:50:47 GMT

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

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

Kurt_Bremser — Fri, 06 Oct 2017 18:00:35 GMT

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.

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

Tom — Fri, 06 Oct 2017 18:04:34 GMT

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.

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

ballardw — Fri, 06 Oct 2017 18:04:41 GMT

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.

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

yangx — Fri, 06 Oct 2017 18:40:16 GMT

Hello,

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

Xiumei