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!
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.
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.
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).
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.
The obs=73 needs to be in the set statement.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.