topic Re: PROC IMPORT with PC Files Server can't read variable names properly with spaces in New SAS User

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

Carabas — Mon, 09 Oct 2023 21:18:26 GMT

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?

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

Tom — Mon, 09 Oct 2023 22:30:02 GMT

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;

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

Kurt_Bremser — Tue, 10 Oct 2023 04:08:10 GMT

Make sure you have

options validvarname=v7;

set before you run your PROC IMPORT.

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

Carabas — Tue, 10 Oct 2023 06:22:20 GMT

I definitely did all types of validvarname.