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):
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?
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!
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);
no need for proc import
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.