When does the OBS= data set option operate?

[FrankPoppe]

Hi,

I have always thought that data set options operate outside the context of the Proc that uses the data set as input.

That means that the Proc only sees the observations that options like OBS= let through.

I was greatly surprised when I saw the results of the following piece of code (simplified for this purpose).

data test ;
do x = 1 to 10 ;
output ;
end ;
run ;

proc sql ;
create table sel1 as
select * from test ( obs=1)
where x > 1 ;

I expected that Proc SQL would see only the first observation (with x=1), then decide that it didn't pass the where clause, and thus create a table with 0 observation.

But the result is a single observation with x=2!

Apparently the obs= and where conditions are combined. But it does not feel right that when I specify that only the first observation should be processed, I get the second observation as output.

Any other views?

[Doc_Duke]

Frank,

Time to RTFM:  "Note that with WHERE processing, SAS first subsets the data and then SAS applies OBS= to the subset."

Actually, it wasn't that obvious in SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

The "note" was in the example.

Doc

[MichelleHomes · Accepted solution]

Hi Frank,

This is one of the concepts that I teach in the SAS Programming 2 course. It's also described in the Language Reference:Concepts book, http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#n1iok7aawea9zzn1aso4...

Cheers,

Michelle

[FrankPoppe]

Thanks Michelle and Doc@Duke (both answers are 'correct' but it seems I can only mark one as such).

Btw, I did search for information in the Language Reference, under Data set options, OBS=, and there the remark is still hidden in the examples.

But it still surprises me that the OBS= data set option is executed after the where clause in Proc SQL. The WHERE statement in a Data Step is a declarative one, and there I see that it has the same 'position' as a data step option.

If I make a join between two tables with an OBS= option on one of the table the result is what I expected.

data test ;
do x = 1 to 10 ;
 output ;
end ;
run ;
data test2 ;
do y = 8 , 9 ;
 output ;
end ;
proc sql ;
create table sel1 as
select * from test ( obs=1)  , test2 
where x = y ;

There is an observation in test that will satisfy the where clause, but this query return 0 observation because only the first obs from test is tried.

I haven't found (yet) some paragraph in the documentation explaining when in SQL the OBS= options is executed before the where clause and when after.

[Tom]

SAS will attempt to push the WHERE operation down as soon as it can.  So your original example with PROC SQL is the same thing that would happen in a data step with a WHERE statement.

In your new example SAS CANNOT push the WHERE clause into the reference to TEST1 because it does not have the variable Y that is used in the WHERE clause.