PROC IMPORT with PC Files Server can't read variable names properly with spaces

[Carabas]

Hi all,

I am using 9.4 Base. Since I have x86 Office and x64 SAS, I use DMBS = EXCELCS in PROC IMPORT to read excel files.

Some of the variables are like "   X Y   ", with 3 leading and 3 trailing spaces and one in between. I need to read these variables to "X_Y".

This is achievable effortlessly by my coworker's setup of x86 SAS, which does not have to enable PC Files Server, with simply DBMS = EXCEL. 

I am not sure why this difference exists. And I wonder if there are fixes to remediate this while not deviating from the existing version?

[Tom]

Can you get the creator of the file to not add those spaces into the header row.

Note that the non-Microsoft XLSX engine will also include the leading spaces when defining the variable name.

Sounds like you will need to post-process the file. 

proc contents data=HAVE noprint out=contents;run;
proc sql noprint;
select catx('=',nliteral(name),nliteral(left(name)))
  into :renames separated by ' ' 
  from contents
  where name ne left(name)
;
quit;
%if &sqlobs %then %do;
proc datasets lib=WORK nolist;
  modify have;
  rename &renames;
  run;
quit;
%end;

[Kurt_Bremser]

Make sure you have

options validvarname=v7;

set before you run your PROC IMPORT.

[Carabas]

I definitely did all types of validvarname.