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?
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;
Make sure you have
options validvarname=v7;
set before you run your PROC IMPORT.
I definitely did all types of validvarname.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.
Find more tutorials on the SAS Users YouTube channel.