topic Re: xls column name with blank space in SAS Enterprise Guide

xls column name with blank space

danimian — Fri, 10 Jan 2014 09:35:38 GMT

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

Re: xls column name with blank space

MichelleHomes — Fri, 10 Jan 2014 10:17:49 GMT

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

Re: xls column name with blank space

danimian — Fri, 10 Jan 2014 10:21:20 GMT

Yes, it is working now.

Thanks a lot.

Re: xls column name with blank space

MichelleHomes — Fri, 10 Jan 2014 10:35:54 GMT

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

Re: xls column name with blank space

danimian — Fri, 10 Jan 2014 13:37:26 GMT

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 .

Best Regards

Re: xls column name with blank space

TomKari — Sat, 11 Jan 2014 00:03:51 GMT

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

Keep asking questions!

Tom