Desktop productivity for business analysts and programmers

xls column name with blank space

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

xls column name with blank space

Hi, I am very new to SAS and trying to read an xls file and using the following code.

libname orionxls 'sales.xls';

data work.subset2;

set orionxls.'Australia$'n;

where Job_Title contains 'Rep';

keep First_Name Last_Name Salary Job_Title Hire_Date;

label Job_Title='Sales Title' Hire_Date='Date Hired';

format Salary comma10. Hire_Date weekdate.; run; 

Code is not functioning because in excel file column name is Job Title (with blank space).

My question is how to write the column name with blank space in such type of code?

Thanks in advance

Best Regards

Dani


Accepted Solutions
Solution
‎01-10-2014 05:17 AM
Super User
Posts: 1,228

Re: xls column name with blank space

I assume you are trying to run the program in SAS Enterprise Guide? If so, you will need to change the columns that have a space in it to be written as named literal variables. So instead of Job_Title, it will be "Job Title"n which treats the string as a variable. Don't forget the n as this tells the compiler to treat it as the name literal rather than a string.

The other columns: First_Name, Last_Name, Hire_Date in all the other statements will need to be updated too.

Kind Regards,

Michelle

View solution in original post


All Replies
Solution
‎01-10-2014 05:17 AM
Super User
Posts: 1,228

Re: xls column name with blank space

I assume you are trying to run the program in SAS Enterprise Guide? If so, you will need to change the columns that have a space in it to be written as named literal variables. So instead of Job_Title, it will be "Job Title"n which treats the string as a variable. Don't forget the n as this tells the compiler to treat it as the name literal rather than a string.

The other columns: First_Name, Last_Name, Hire_Date in all the other statements will need to be updated too.

Kind Regards,

Michelle

Contributor
Posts: 24

Re: xls column name with blank space

Yes, it is working now.

Thanks a lot.


Super User
Posts: 1,228

Re: xls column name with blank space

No problem. I teach the SAS Programming 1 course for SAS Education in Australia and am familiar with this piece of code and the issue of running it in Enterprise Guide. :-)

FYI, it's due to a SAS System option, validvarname having different values in Foundation SAS and SAS Enterprise Guide.

Glad to hear your code is running now.

Kind Regards,

Michelle

Contributor
Posts: 24

Re: xls column name with blank space

Many thanks for more details and quick help.

Right, this code is from Prog. 1 examples.

I am Newbie-SAS-Programmer and in high learning mode Smiley Happy.

Best Regards

Trusted Advisor
Posts: 1,054

Re: xls column name with blank space

Well, you're in the right place...Michelle has forgotten more about Enterprise Guide than most of us know...

Keep asking questions!

  Tom

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 359 views
  • 1 like
  • 3 in conversation