topic Re: proc import from excel data name has spaces in SAS Programming

proc import from excel data name has spaces

changxuosu — Mon, 01 Jul 2019 15:24:42 GMT

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!!!

Re: proc import from excel data name has spaces

Tommy1 — Mon, 01 Jul 2019 15:41:17 GMT

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.

Re: proc import from excel data name has spaces

Tom — Mon, 01 Jul 2019 15:51:29 GMT

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

Re: proc import from excel data name has spaces

changxuosu — Tue, 02 Jul 2019 13:47:48 GMT

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?

Re: proc import from excel data name has spaces

changxuosu — Tue, 02 Jul 2019 13:49:33 GMT

worked like charm! thanks!

Re: proc import from excel data name has spaces

Tom — Tue, 02 Jul 2019 13:51:31 GMT

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.

Re: proc import from excel data name has spaces

changxuosu — Tue, 02 Jul 2019 13:53:12 GMT

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

Re: proc import from excel data name has spaces

bodkhesudam — Sat, 06 Jun 2020 07:47:00 GMT

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.