Reading Excel (xlsx) file with column names on two or more rows? (SAS9.4M7 windows)

[kjohnsonm]

Hello all,

I am not sure if this has been done before or not so here goes.
Sorry this is not in a traditional SAS 'have', 'want' style* with some starter code.

I have many reports going back decades that I want to read back in from excel into SAS. All have a few things in common. Each has a report name in cell A1.

May skip one or more lines/rows and has column heading information broken over two lines not repeated but repeated implied (see image attached)  A random number of data/body lines with some type of time series indicator for the first column A, can have per separate tab or excel report a random number of columns (generally less than 40 columns).

then a Notes or footer data.

Note in this example how the first row of the column headings (Row 3) just has Academic, Room, Board, and 'Room & Board Rates'  in this case if you select each word/phrase they are in cells for example:
Academic (A3), Room (B3), Board (F3), and 'Room & Board Rates' (L3)  in each case from for example 'Room' would be repeated in the columns from B3-E3 or B3 to ((L3)-1 column), then Board would be repeated til column K etc..

I have attempted to read this in with a few passes since the data sizes are very small in general I can read in with a few let statements per report and short macros, -custom written per sub-report area, get the report name, the body, the footer, and all its body lines.  I can read these correctly except my method has to ignore the column names and just read for example columns as A-M in this case. 

What I am failing to do with this method is tie it back together with a single line for the column names.
At this time does anyone have a solution that would handle reading this data and for example have a column name for column 'C' 'Room_Double' or 'Double_Room'?

*I am forgoing that request style since I think my ideas to re-join the composite column headers to the columns have failed I just wanted to start fresh to see what the rest of SAS world might do to solve this read issue. My research on reading excel xlsx files is not very fruitful where the column header is over two lines.  TIA  -KJ

[SASKiwi]

The way I would approach this is to import the data using PROC IMPORT starting at row 5 and use the GETNAMES = NO option. That means columns in SAS will be called A, B, C and so on.

Then I would create a separate lookup table that specifies what I want the columns to be renamed as like A = Year, B = Room_Single and so on. Then I would use the lookup table to create a RENAME statement so I end up with the names I want.

[kjohnsonm]

Thank you for your reply,
I actually already have your suggestion accomplished at this point. I can read the body of the reports as you state as columns A, B, C, etc.  I can parse out the footer/notes/  I can get the report name. What I am stuck on is how do I press the column names back together with the column data (automating the column name [a, b, c, d...]to the descriptive column name)   A

proc sql;
select A as "(select a from mytable1)"
from mytable2
;quit;

 /* where mytable1 has a single row of data column descriptions for mytable2 and a=a, b=b, c=c column data to column names. --and I know this is not correct but was my line of thinking.  I just assumed there had to be a better way that someone was aware of and could share. Thus my query to see if anyone had a complete solution to this multi lined column name problem. */

for example?  I don't know how to do this step and every attempt I try brings me back to another problem. like I do it nicely for a single case when I know in advance how many columns there will be but that can be relatively random number between 5 columns to 40.

[kjohnsonm]

SASkiwi,
hmmm on second thought, ...maybe I do not know what you mean by "Then I would use the lookup table to create a RENAME statement so I end up with the names I want." When I try and do a rename all my attempts are failing. Can you give me an example? I use 'as' statements all the time. I don't recall using a soft coded one before.

[SASKiwi]

@kjohnsonm  - This is what I was thinking:

data VarNames;
 informat ColumnName $32.; 
 infile datalines EOF = Finish;
 input Column $ ColumnName $;
 length RenameStmt $ 500;
 retain RenameStmt;
 Rename = catx(' ', Column, '=', ColumnName);
 RenameStmt = catx(' ', RenameStmt, Rename);
 return;
 Finish: call symputx('RenameStmt', 'rename = (' !! strip(RenameStmt) !! ')'); 
datalines;
A Year
B Room_Single
C Room_Double
;
run;

%put &RenameStmt;

You can plug the macro variable RenameStmt into a SET or DATA statement.