DATA Step, Macro, Functions and more

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

Reply
Contributor
Posts: 21

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

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

 

Super User
Posts: 7,866

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,080

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

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.

Contributor
Posts: 21

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

Hello,

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

 

Xiumei

 

Super User
Posts: 11,343

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

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.

Ask a Question
Discussion stats
  • 4 replies
  • 110 views
  • 3 likes
  • 4 in conversation