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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.