proc import from excel with variable names with spaces

[learner_sas]

Hello Everybody,

I am importing huge file ( so many columns) and run below query.

PROC IMPORT OUT=WORK.test DATAFILE="~\data.XLS"

  DBMS=XLS REPLACE;

  SHEET="Sheet1";

  GETNAMES=YES;

RUN;

I found that my column names has spaces in final sas dataset also. For example 'var one' came as 'var one' but I was expecting it will come as var_one. I am wondering if there is a way to make sure that all variables are named as SAS convention as we create tables using file from Excel.

I know I can do it using file>>import data and clicking in rename variables as sas convention in EG but I am looking if it can be done using proc import procedure.

Thank you

[Reeza · Accepted solution]

1. Are you sure that's what's happening? SAS has variable labels and names, the labels may have spaces but the names won't.

2. Look at the validvarname option.

option validvarname=ANY - this allows spaces

OPTION VALIDVARNAME=V7 - will allow the standard SAS naming convention, i.e. using underscores.

Change the option in EG if you require and re-importing will allow the names to be imported as you expect.

SAS(R) 9.3 System Options: Reference, Second Edition

[learner_sas]

Thank you @reeza. It worked.