topic Re: Extra rows when importing Excel data in SAS Data Management

Extra rows when importing Excel data

Betsy — Mon, 14 Sep 2020 14:00:39 GMT

I have a 74 row excel sheet, 1 row variable names, 73 rows of data. When I import, over a million rows are imported - 73 data plus over a million of missing data ('.'). All the variables in these rows have this "missing" data.

I tried options obs = 73 with no change.

options obs = 73;

proc import datafile = 'data.xlsx'
out = data1
dbms = xlsx
replace;
datarow = 2;
run;

Any recommendations on how to just get the 73 rows of data would be greatly appreciated!

Re: Extra rows when importing Excel data

Kurt_Bremser — Mon, 14 Sep 2020 14:08:26 GMT

This happens when somebody did anything in the very last available row in the spreadsheet (e.g. type a blank). Since the maximum number of rows in Excel is 1M, your spreadsheet will then expand down to there, and SAS will import that.

Fix it in Excel (mark rows 75 to 1M, delete rows), or run a data step after the import with dataset option OBS=73 in the SET statement.

Re: Extra rows when importing Excel data

mkeintz — Mon, 14 Sep 2020 14:43:20 GMT

The global statement

OPTIONS OBS=73;

only applies to sas datasets being read. As you have discovered, it does not control the number of observations that are written to a sas dataset. You can prove this to yourself with the following demo program:

options obs=19;
data t;
  set sashelp.class;
  output;
  output;
run;

which generates the log notes:

NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set WORK.T has 38 observations and 5 variables.

Re: Extra rows when importing Excel data

Betsy — Mon, 14 Sep 2020 15:08:32 GMT

Thank you, that worked perfectly. I ended up using VBA in excel to delete the rows since there were so many rows to highlight, and I wasn't able to do a find and delete (other rows had actual missing data marked with '.' as well).

Re: Extra rows when importing Excel data

Krissy217 — Mon, 14 Dec 2020 03:25:10 GMT

The obs=73 needs to be in the set statement.