BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
FrankPoppe
Quartz | Level 8

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?

1 ACCEPTED SOLUTION

Accepted Solutions
MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com

View solution in original post

4 REPLIES 4
Doc_Duke
Rhodochrosite | Level 12

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
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
FrankPoppe
Quartz | Level 8

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
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1400 views
  • 5 likes
  • 4 in conversation