BookmarkSubscribeRSS Feed
Carabas
Calcite | Level 5

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?

 

3 REPLIES 3
Tom
Super User Tom
Super User

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;
Carabas
Calcite | Level 5

I definitely did all types of validvarname. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 375 views
  • 0 likes
  • 3 in conversation