BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
changxuosu
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

7 REPLIES 7
Tommy1
Quartz | Level 8

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
Quartz | Level 8
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
Super User Tom
Super User

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
Quartz | Level 8
learned a ton! thanks!!! Really appreciate it.
bodkhesudam
Calcite | Level 5

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
Super User Tom
Super User

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
Quartz | Level 8
worked like charm! thanks!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 25298 views
  • 3 likes
  • 4 in conversation