Columns named the same - Any way to differentiate?

[Russell]

Hey everyone,
My name is Russell, and I am rather new to SAS programming. I just graduated and work as a media analyst at an advertising firm.

Our database has a weird way of exporting some data... it uses two rows to explain some columns. For example, it will have the Row 1 saying "Projected," spanning 2 columns, then "Estimated," spanning 2 more columns. Then each individual column is named RHOMES and IHOMES. Something like this (the vertical bars denote columns):

.......... Projected................. Estimated
| RHOMES | IHOMES | RHOMES | IHOMES |

There is a lot more information all which has unique column names. So I just delete Row 1 and rename each of these headers PRHOMES, PIHOMES, ERHOMES, etc...

However, in some spreadsheets I have upwards of 6-8 columns under each header, and it gets to be a pain in the butt to do this on a weekly basis. Is there any other way to instruct SAS to understand the difference in these columns?

Thanks a bunch!

[Cynthia_sas]

Hi:
Is that really a DATABASE (like Oracle or Sybase or DB2) or are you reading an Excel spreadsheet/worksheet into a SAS dataset, where the spreadsheet has multiple row headers???

That doesn't sound like many of the databases that I have dealt with, although it sounds like the kind of report file that I have created from database data.

cynthia

[Russell]

Cynthia: it is an Excel report output from Core Media Systems, sorry for the confusion.

Reeza... that is a good idea. This is a very standard report layout which is spit out from my buying software, so as long as nobody changes it (which they can't without my password), it will stay the same order. I never even thought just to change the names on the import step... doh!

Thanks!

[Ksharp]

Hi.
If you let ' proc import' not to getnames in the row, SAS will automatically assign variable such as F1 F2 to these variables and label these variables with you the same columns name.


Ksharp

[Peter_C]

since you have control of the workbook, name a range to cover all the columns and rows you need, in the sas-name style (starting with letter or underscore and the rest containing numbers, letters and/or underscores, and no longer than 32).
Then the workbook can work as a sas library. The rangename is a tablename allowing:
libname book 'your workbook.xls';
proc contents data= book.rangename;
* where rangename is the name you gave that range in excel);
run;
no need for proc import

[Reeza]

I've seen data like that before...

The "kludge" has been to not import the header labels at all and then rename the variables manually once in the code.

The risk in this method is if the report structure/layout changes but it has been pretty steady so no issues around that for me as yet and the database administrator knows this is what I'm doing so he adds new columns at the end. It isn't a good programming method but it works.

Another option is to record a macro to do the renaming, but that is still an excel step.

Ideally you'd connect to the file on the DB through SAS. If you can't connect directly to the report database though using ODBC or some other connection then I hope someone has a better solution.