BookmarkSubscribeRSS Feed
Russell
Calcite | Level 5
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!
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
Calcite | Level 5
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
Super User
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
Rhodochrosite | Level 12
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
Super User
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 687 views
  • 0 likes
  • 5 in conversation