proc import from excel data name has spaces

[changxuosu]

Dear Sas community,

I have a question. When I use proc import to load an excel sheet, the column name in my excel sheet has spaces (for example, one variable name is "Real GDP").

I can successfully import the excel sheet into my sas. The problem is that in sas datset, the variable name also have spaces.

It prevents me from doing any analysis/coding.

Do you know any way to rename (for example, I want to change "Real GDP" to "Real_GDP" in sas)

I don't want to do it manually in excel because in excel there are hundreds of names to rename.

Thanks!!!

[Tommy1]

Using proc import should automatically import data and put underscores in the variable names where there are spaces. The spaces that you see are probably a Format. Double click a variable and look at the name of the variable in the popup. You can look at all the variables with Proc Contents. Let me know if those still have spaces.

[changxuosu]

thank you for your reply, Tom. Appreciate it. Yes, strangely, the variable name still has spaces... I'm also surprised it doesn't automatically add underscore... maybe my setting is different from default?

[Tom]

Check what you have.  For variable names check VALIDVARNAME.

%put %sysfunc(getoption(validvarname));

If it is ANY then PROC IMPORT will  allow spaces in names.

If it is V7 then PROC IMPORT should convert the names.

For dataset names check the VALIDMEMNAME option instead.

If it is COMPAT then it should convert the dataset names.

If it is EXTEND then it should allow spaces in the dataset names.

[changxuosu]

learned a ton! thanks!!! Really appreciate it.

[bodkhesudam]

I have been facing the same issue. After importing dataset using proc import, sas does not auto converting space into underscore when column name has space in it. Is there any options that i need to specify first.

[Tom · Accepted solution]

Change the setting of the VALIDVARNAME option and SAS should automatically convert those types of column headers into valid names.

If you leave VALIDVARNAME=ANY then you can use name literals in your code.

rename 'real gdp'n=Real_GDP

[changxuosu]

worked like charm! thanks!