Removing merged header from Excel files with SAS

[SAS_user_RG]

Hello 

Can anyone offer an option for dealing with Excel files that contain merged rows as the Headers? I am using base SAS 9.4.

The files I am working with are collected daily. I need to create a way to remove the merge and reformat the rows. 

Importing and deleting is not an option due to SAS reading the rows below the headers as text during the import.

I have a second issue, if anyone can offer an idea of how to deal with it. A different Excel file has the first row blank. 

Is there a way to delete the first row without using the import function?

Thank for any help you can offer. 

[ballardw]

Same solution for both:

If the purpose is to create SAS data sets you have two options but both pretty much involve data step code.

One is to save the files as CSV and write a data step to read the file. Your variable properties, like NAME, informat, length, format and label are set by you and the input reads the columns. The Infile statement pointing to the file will allow you to define which is the first row of data to read.

The other approach is to use a LIBNAME  excel to point to the source file, and use a data step to manipulate the data starting on a specific row. This has potential weakness because minor changes in the data content may require rewriting bits of the code.

If you are reading these every day the amount of time to do a "file save as" to CSV is well worth the time you might spend trying to fix other junk from "importing" Excel data.