BookmarkSubscribeRSS Feed
kjohnsonm
Lapis Lazuli | Level 10

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.

my_housing.JPG

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

4 REPLIES 4
SASKiwi
Opal | Level 21

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10
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
Opal | Level 21

@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.

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1652 views
  • 0 likes
  • 2 in conversation