Identify row with first occurrence of a string (searching all variables)

[td1345]

Problem:

I have 1000s of xlsx sheets that I'm importing. However, they are poorly structured but contain a table that all start with a date-column with either 'date' or 'testdate' as column header (then the column contains dates). This keyword/header can be in various positions in excel (eg row 3 and column 4 or row 5 and column 3).

Solution:

Import xlsx file

create a variable with row-nr (_N_)

Identify row position of keyword ('date' or 'testdate')

import xlsx file at rownum = position given above to capture headers.

My problem lies in identifying the first occurrence of date or testdate searching all columns with unknown column names.

[SASKiwi]

It would help if you could post an example of a sheet you are trying to import. Dummy data is fine if the original data is confidential.

I'd suggest using the XLSX engine in a SAS LIBNAME statement pointing at one of the Excel workbooks, then reading the required worksheet in using a SAS Data Step.