Extra rows when importing Excel data

[Betsy]

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! 

[Kurt_Bremser · Accepted solution]

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.

[Betsy]

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).

[mkeintz]

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.

[Krissy217]

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