Desktop productivity for business analysts and programmers

Getting an error when using first obs

Posts: 0

Getting an error when using first obs

In a proc sql I am trying to write, I can process my first 65,000 records just fine, however, when I then change my sought after records to firstobs=65001 obs=130000, I get this message:

"FIRSTOBS option > number of observations"

The dataset I am trying to process has over 6 million records, so why am I getting this error message? Any ideas or suggestions will be gratefully accepted.

Thank you
Posts: 0

Re: Getting an error when using first obs

are you trying to access a sas data set, a data view, or a database table (sql table, etc)? sas can't even tell how many records are in data view or a database table, so i doubt it can access records 65,001 - 130,000. you'd need to access all from the beginning.
Posts: 0

Re: Getting an error when using first obs

?? So, if, as you say, SAS can't even tell how many records are in a database table, how does something like the following work to tell how many records are in the dataset? :
proc sql;
select nobs
from dictionary.tables
where libname eq 'SASHELP'
and memname eq 'ZIPCODE';

Secondly, if I have to access all from the beginning, that defeats the purpose of trying to select a given set of records, doesn't it?

Thanks for replying....I'll see what I can come up with.
Trusted Advisor
Posts: 2,114

Re: Getting an error when using first obs


Database tables are not the same as SAS tables. SAS tables (aka SAS datasets) on DASD keep the number of observations in the header. PROC SQL can't get that from an Oracle or SQL/server table.

Also, SQL is "stateless" in the sense that it does not necessarily read or keep the observations in sequential order (that's why you need to use the ORDER BY clause even if you want the output to be in the same order that you knew the input to be). SQL uses indices to address order.

What is your source file type. If you are somehow filtering this through some of the older file systems, you may be running against it's limits. Also check your system options (run PROC OPTIONS) to ensure your SAS install does not have a global limit set.

Doc Muhlbaier
Posts: 0

Re: Getting an error when using first obs

What is the underlying data source?

6 million records, in a SAS Dataset or in a database?
If a database, which database: Oracle, SQL Server, DB2, etc. ?

Are you using pass-through or a libname to access the database?

Why are you wanting to split your work up into 65,000 record (obs) chunks?
Why 65,000?

Why are you using proc sql instead of a Data step?

SAS has many tools, and depending on what is desired, it probably has the right tool for the job. I remember when I first learned about proc sql. I was ecstatic because I needed to do a real join and had been beating my head against the wall with merges, by-sets and loops. There are times when a data step is more appropriate, and times when proc sql is more appropriate. Similarly, there are times when Base SAS is best, when SPDE is best, when Oracle is best, and when DB2 is best. It all depends on what is trying to be accomplished.
Ask a Question
Discussion stats
  • 4 replies
  • 2 in conversation