Hello,
I am trying to import Excel spreadsheets across platforms. I am using the following code but I get an error that Datarow is not valid. My data starts in row 7 and the column headings are in row6.
proc import FILE="\\...My file.xlsx"
out=Myfileout
dbms=excelcs /*ExcelCS is required else I get "Class not registered" */
replace;
sheet="Dashboard";
datarow=7;
run;
As the error says, datarows is not supported for Excel. You could try specifying a range to import such as Range="sheetname$a6:z200" but you're not going to get variable names from row 6 and would have to use Proc datasets or similar to assign names you want.
Or save the file as CSV and remove the first 5 rows before importing.
Proc import expects "clean" data, column headings if any in the first row and then data. If the data is clean, a common issue with Excel, then you have to do more work. Or avoid using Excel files for data interchange if possible.
As the error says, datarows is not supported for Excel. You could try specifying a range to import such as Range="sheetname$a6:z200" but you're not going to get variable names from row 6 and would have to use Proc datasets or similar to assign names you want.
Or save the file as CSV and remove the first 5 rows before importing.
Proc import expects "clean" data, column headings if any in the first row and then data. If the data is clean, a common issue with Excel, then you have to do more work. Or avoid using Excel files for data interchange if possible.
Depending on your SAS version, DBMS=XLSX may also work (SAS 9.3 TS2M3+).
As specified you can use a RANGE.
I thought it would still include the names when you specified the RANGE but you can probably test that.
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.