Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Accessing a spreadsheet by column instead of name after importing from Excel

Posted on 2013-11-05
4
Medium Priority
?
387 Views
Last Modified: 2013-11-11
Experts,

I was wondering if there is a way to access a column  in Excel by column (i.e. Column A) in lieu of the column heading in SAS.  This would allow flexibility of bypassing column headings which may be long or descriptive in Excel but not good for a SAS variable name.
0
Comment
Question by:morinia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 5

Expert Comment

by:ckluka
ID: 39624381
You are looking for the "indirect" function.

http://office.microsoft.com/en-gb/excel-help/indirect-HP005209139.aspx


You can address a cell (B3) for example two ways:

=indirect(B3,TRUE)
=indirect(R3C2,FALSE)

You can replace "B3" or "R3C2" with a string literal or cell reference so you can use it in a formula or function to iterate through a list or entire column.
0
 

Author Comment

by:morinia
ID: 39624415
ckluka,
I would like to reference the column in SAS.
0
 
LVL 14

Accepted Solution

by:
Aloysius Low earned 800 total points
ID: 39626215
you using proc import code? try naming the columns (i.e. row 1) as numbers and import again - you should see the Excel column as the column name i.e. A - Z, AA - ZZ etc etc

at least that's how it's appearing to me... otherwise, if you don't wish to rename the columns that you have already, set validvarname option to any (i.e. options validvarname = any;)

you can reference the column names by adding a quotation marks and a letter n (i.e. 'field name'n) however, this does not avoid the 32 characters length limitation set...
0
 
LVL 17

Assisted Solution

by:Gerald Connolly
Gerald Connolly earned 400 total points
ID: 39626939
Look at the column function
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question