libname xlsx header=no option yields an error

[pspung]

Hi Folks, I'm trying to understand how to use libname xlsx to read a .xlsx file that does not have a header row. Ie, the first observation is on the first row. Since there's no header, I expect that the variable names will be the Excel column letters, which I can rename later. In a prior question in this community someone suggested trying header=no after the filename, such as:

LIBNAME Labs XLSX "&CourseRoot/Data/Laboratory.xlsx" header=no;

For me, that produces an error in the log: "ERROR: Libref LABS not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name HEADER."

My goal is to read rows from a couple of tabs in the Excel workbook tabs into a dataset, using a data step and set statement. I've successfully done this before using libname xlsx, but the .xlsx tabs/sheets used previously had a header row.

Thanks for any suggestions, Peter

[Quentin]

Agree, it looks like the XLSX engine does not support header=no.  

You could try using the older EXCEL engine, which does support that option:

LIBNAME Labs excel "&CourseRoot/Data/Laboratory.xlsx" header=no;

[SASKiwi · Accepted solution]

What happens without the HEADER option? The EXCEL engine isn't available in Unix SAS which is what you appear to be using. You could always revert to PROC IMPORT and the GETNAMES = NO option.

[pspung]

Hi SASKiwi, leaving the HEADER option off does not yield an error. However, values on the first row become variable names. Since some values repeat, eg 'R1', odd variable names are created such as R1_1, R1_2, etc.

Proc import getnames=no does indeed work as expected. I'll switch to that approach.

Thanks!  Peter

[Quentin]

@pspung , please mark @SASKiwi 's answer as accepted / correct, to close out this question.