topic Excel libname firstobs option in SAS Programming

Excel libname firstobs option

LEINAARE — Mon, 25 Jan 2021 15:24:06 GMT

Hello,

I have copied multiple years of data tables into an Excel xlsx file with sheets named for each year (ex. 2005, 2006, ... 2018). I used a libname statement specifying the xlsx engine to create a SAS library that accesses the sheets from the Excel workbook. I have run into two problems with this. First, although I am using the firstobs= and obs= options to specify rows for data input, the data step is importing data from one row below each specified value. For example, I wrote the program below to import rows 3-54 from the Excel sheet. However, it brought in rows 4-55. Does anyone know why this is happening?

My second question involves naming variables. I tried using an input statement, as I typically use when importing raw data within data steps, but I received an error message "No datalines or infile statement". Could anyone tell me how to specify variable names and informats when using an Excel file as a library? I could not find an example in SAS documentation where they specified variable names.

libname SHEETS xlsx "S:\Data Files\OPA FPAR\States.xlsx";
data fpar_2005;
	set SHEETS.'2005'n(firstobs = 3 obs = 54);
run;

Also, I do not see in SAS documentation how to use the equivalent of an input state

Thanks,

Ted

Re: Excel libname firstobs option

LinusH — Mon, 25 Jan 2021 15:59:17 GMT

SAS is probably cinsidering row 1 in Excel as header (variable) information, wheras the firstobs= optins refeers to datalines processed by the data step. Set firstobs=2 if you want to ommit the header row and an additionl row.

If you own the Excel spreadhsheet the simplest is to use headers that comply with SAS variable naming rules.

You can't/shouldn't use input with the Excel libname engine - the engine itself defines/decides table and column/variable names.

Re: Excel libname firstobs option

LEINAARE — Mon, 25 Jan 2021 16:02:06 GMT

Thank you @LinusH. I think I will just rename the variable names SAS assigns.